January 2011

I wish I could say I planned to break the all-time record for post-conference blogging delay and was waiting until Debra Lilley got round to hers, but the truth is this is yet another post that slipped through the cracks in the constant work/play/travel continuum.

One very unfortunate side-effect of the growth of Twitter that I've noticed over the past year or so is the plummeting number of mid-conference blog posts from both myself and others. I think everyone finds it difficult to blog in the frantic conference atmosphere and Twitter offers a nice easy way out - pumping out short, virtually meaningless messages but, hey, as long as we all feel we're connecting, eh? So I find myself in the sorry position of having to look through old tweets to remind myself what on earth I got up to in Birmingham at the end of November, although some parts will live long in my memory!

This (last?) year was always going to be a slightly different conference experience for me because I wasn't presenting and planned a shorter two-and-a-bit day trip, mainly to catch up with friends and attend a few presentations. It became a lot shorter when my planned Sunday socialising was wiped out by the 14 hour trip, but I suppose I was one of the lucky ones who made it and in time to have a decent sleep before the first day of presentations.

Monday

My first presentation was Tom Kyte's - What's new in Oracle Database Application Development. Of the many interesting bits and pieces I picked up from this was the reminder of how successfully the modern optimiser can eliminate chunks of workload such as unneccesary outer joins. Most of that type of optimisation is dependant on safe assumptions the optimiser can make when the correct referential integrity constraints are defined. Another good reason to keep up good physical modelling principles.

One of the audience questions at the end interested me, asking if Oracle had any plans to be able to synchronise two partition exchanges so that they become part of the same DDL operaton. I was interested because I was faced with that specific challenge at the time. Of course, with further thought it became apparent that we didn't really need to perform two partition exchanges at the same time, we just needed to fix our data model!

However, my best laugh came when he was asked what I thought had become Graham Wood's personal question at the end of the presentation. The great Diagnostics Pack Licensing Question! There are variations on the theme, but it's essentially ... 'Do we really have to pay extra for x, y or z' ... usually the Diagnostics Pack.

Next up was - Sane SAN 2010 - James Morle - although not before I'd drawn far too much attention to myself by walking into a brightly lit room with my ridiculous moustache and side-burns only to see a friendly, inquisitive face to remind me what I had growing on my own! (As the day progressed, I tried to sneak in a little late once the lights were down )

The best thing about James' presentation apart from the sensible high-level messages about how awful storage performance has been for years, some of the reasons why and what an utter game-changer Flash is, the best part for the lazy blogger is that he has a complete paper on the subject to read here. Read it, weep a little, then rejoice that things are improving.

Oracle 'Hidden' Features: Features You Probably Didn't Know About and are FREE - Graham Wood. (Mmmm, well, mostly free, apart from some that require that damned Diagnostics Pack license! In fairness, many people have paid already and aren't making full use of what they've paid for.)

I particularly enjoyed this and I think it's because it felt like Graham, who does a lot of Oracle keynote presentations these days, was getting back to what he does best - showing off all sorts of little useful technical details that many people would be unaware of, and perhaps only some of us care in addition to the big ticket items like ASH and ADDM. He spent a fair bit of time walking through how 11gR2 trace files look, including the proper capture of CLOSE statements, and emphasising that we should use DBMS_MONITOR for tracing (I always do these days) which has parameters like PLAN_STAT that control how often row source execution statistics are dumped to the trace file.

On a related note, for more great information on tracing, see Cary Millsap's latest paper.

My presentation of the conference was Oracle Optimizer - Top Tips to get Optimal SQL Execution all the Time by Maria Colgan in which she talked about the most common types of problems you see in four areas

1. Cardinality
2. Access path
3. Join type
4. Join order

There was so much useful information, I'd suggest you get hold of a copy of the slides if you can. One thing I noted personally was that SQL Monitoring is probably the best method for getting the A-Rows (actual rows) value for parallel queries because 'ALLSTATS LAST' doesn't work well with PQ.

It made me chuckle away to myself to hear her constantly refer to the optimiser as 'he'. Yes, I thought. A male optimiser. Unreliable, temperamental and sometimes difficult to work with

Maria was undoubtedly the best speaker I heard and I met many others around the conference who agreed. I'd already seen here present at OOW so I knew she was good (although I've also skipped a couple of presentations too which she doesn't let me forget ) I was lucky enough to have dinner with her, Peter Robson and Dan Fink later and, despite the fact I was three sheets to the wind (after taking too much dutch courage to get myself up on stage for the Pantomime) she was excellent company too. At least I think she was although there might have been times she struggled to get a word in edge-ways

There are plenty of opportunities to hear Maria present at upcoming conferences including Openworld and the Hotsos Symposium. Give them a try and I don't think you'll be disappointed. (What's the betting we end up scheduled to speak at the same time at Hotsos and she wipes out my audience? LOL)

It would be a great shame if the UKOUG don't get Maria and more of the great Oracle Corp speakers from Redwood Shores over. I can do without marketing presentations and I like to see a User Group focus on community presentations, but I know from Openworld that there are great presenters inside Oracle who really know their stuff and it's a nice balance to the user stories.

OK, I think I've sucked up enough now to excuse missing her earlier presentations and being drunk at dinner

I was able to catch a little bit of Daniel Fink's Customising ASH and AWR: Beyond ashrpt.sql and awrrpt.sql. My main reason for attending was that I know Dan and enjoy listening to him present but I didn't expect to learn too much, given that I teach a course on ASH and AWR. It was nice to see him draw attention to the AWR differences report, though. The more people who know about that the better.

Which just left me enough time for some pre shave-off dutch courage and moral support from friends before the Keynote Presentation by David Callaghan. I turned up late, am not the greatest fan of Oracle keynotes and was petrified by what was to come, so can't remember a thing!

Next I made a fool of myself but there are already enough Panto blogs, photos and videos floating around!

Tuesday

Tuesday was a bit of an anti-climax for me, largely because I had to miss even more presentations because my skin condition was playing up so I had to hunt down some medical supplies and take care of myself. (On a brighter note, the long delay between the conference and this post means that my condition's improved sufficiently that I needn't keep whining about it.)

As one of the slides of Wolfgang Breitling's Time Series Analysis Techniques for Statspack or AWR stated - "The main purpose of time series analysis is identifying patterns in past events which can be used to forecast future values and events." It's an interesting concept and if I had a fraction of the maths that might have helped me understand it, I think I would have enjoyed the presentation much more But Wolfgang is one of those presenters who always have something to say that interests me and it's probably a topic worth investigating further, perhaps using some of the references to R that he included. Summary - I remember enjoying it but some of it sailed over my head!

Ironically, given the number of times he's recommended taking detailed notes as one of the cornerstones of his personal approach to conferences, I can't remember the first thing about Jonathan Lewis' - Co-operating with the database. It doesn't help that he hasn't uploaded his slides either, but I remember him being utterly compelling to listen to although I doubt that helps much if none of it stuck in my head! I'll put it down to my utterly distracted state that day.

My final presentation of the week was How to Build a System that Doesn't Scale - Graham Wood - a presentation all about the things you *shouldn't* do! It turned out that I was actually quite familiar with a lot of the information from various Real World Performance Group presentations I've attended at Openworld, but Graham walked through a very interesting spreadsheet showing different benchmark results obtained when misusing various configuration options which showed, for example, just how unscalable systems are that don't manage connections carefully or use bind variables.

Slides for most of the presentations are available from here, although you will need the username and password so it only really works if you attended the conference or maybe someone from your organisation did. If you are particularly interested in any of the presentations, I suppose you could always email the presenter directly.

I finished off my conference with several enjoyable beers at the Tuesday evening networking which was sponsored by OTN and then it was back to work the following morning.

Phew, I am *so* glad I've finally got this post done and can move on to other things. It's been bugging me!

Not many days pass that I don’t think a time or two about James R. Harkey. Mr. Harkey was my high school mathematics teacher. He taught me algebra, geometry, analytic geometry, trigonometry, and calculus. What I learned from Mr. Harkey influences—to this day—how I write, how I teach, how I plead a case, how I troubleshoot, .... These are the skills I’ve used to earn everything I own.

A recurring question during Grid Infrastructure and RAC courses I teach is “How do you manage Oracle password files in a clustered environment?”. The answer isn’t as straight forward as you might think because there are significant differences between ASM and RAC (==clustered database) environments. Additionally, in recent releases changes were made concerning password file […]

Some time ago I was on a client site which was busy setting up a RAC cluster using Oracle 10g. Although I wasn’t involved with that particular system there were a couple of coffee-breaks where I ended up chatting with the consultant that the client had hired to install the cluster. In one of our breaks he surprised me by making the casual comment: “Of course, you can’t run Statspack on RAC because it locks up the system.”

Now there’s no reason why Statspack should lock up a RAC cluster – in principle. But this was an 8-node cluster and if you set up the automatic job to take snapshots by running the default spauto.sql script all eight nodes could start running every hour on the hour – so they would all be fighting constantly for every block of every table and index in the Statspack schema.  (I’m exaggerating for effect, of course, but not by much). You might not notice the global cache contention in a 2-node cluster but eight nodes could, indeed, “lock up the system” at this point.

Ideally, of course, you would like to run all eight snapshot simultaneously and concurrently so that you get information from the same moment across all nodes. In practice you have to stagger the snapshots.

The code for taking snapshots for the AWR (automatic workload repository) gets around this problem by using the ‘WF’ lock for cross-instance synchronisation. One instance gets the lock exclusively, and that’s the instance that sets the snapshot time and coordinates the flushing to disc for all the other instances. (The other instances collect their stats at the same moment – but are cued to flush them to disc one after the other)

For Statspack you have to do something manually – and the simplest approach that people sometimes take is to run a slightly different version of sp_auto.sql on each node so that each node is scheduled to start its snapshot a couple of minutes after the previous one. But there is an alternative that is a little smarter and eliminates the risk of two instances overlapping due to time slippage or a slow snapshot.

Taking the idea from AWR we can make use of dbms_lock to ensure that each snapshot starts as soon as possible after the previous one. We simply create a wrapper procedure for statspack that tries to take a specified user-defined lock in exclusive mode before calling the snapshot procedure and releasing the lock after the snapshot is complete. We can schedule this procedure to run on every node at the start of each hour – which means every node on the system will try to take lock simultaneously – but only one node will get it and the rest will queue. After a node completes its snapshot and releases the lock the next node in the queue immediately acquires the lock and starts its snapshot.

Here’s some sample code for the wrapper. I’ve included a couple of debug messages, and a piece of code that makes the procedure time out without taking a snapshot if it has to wait for more than 10 minutes. Note that you have to grant execute on dbms_lock to perfstat for this to work.

create or replace procedure rac_statspack (i_snap_level in number) as
        m_status        number(38);
        m_handle        varchar2(60);
begin

        sys.dbms_lock.allocate_unique(
                lockname        => 'Synchronize Statspack',
                lockhandle      => m_handle
        );

        m_status := sys.dbms_lock.request(
                lockhandle              => m_handle,
                lockmode                => dbms_lock.x_mode,
                timeout                 => 600,         -- default is dbms_lock.maxwait
                release_on_commit       => false        -- which is the default
        );

        if (m_status = 0 ) then
                dbms_output.put_line(
                        to_char(sysdate,'dd hh24:mi:ss') ||
                        ': Acquired lock, running statspack'
                );

                statspack.snap(i_snap_level);

                dbms_output.put_line(
                        to_char(sysdate,'dd hh24:mi:ss') ||
                        ': Snapshot completed'
                );

                m_status := sys.dbms_lock.release(
                        lockhandle      => m_handle
                );
        else
                dbms_output.put_line(
                        to_char(sysdate,'dd hh24:mi:ss') ||
                        case m_status
                                when 1 then ': Lock wait timed out'
                                when 2 then ': deadlock detected'
                                when 3 then ': parameter error'
                                when 4 then ': already holding lock'
                                when 5 then ': illegal lock handle'
                                       else ': unknown error'
                        end
                );
        end if;

end;
/

And here’s a little bit of pl/sql you can run on each node in turn to install the procedure under dbms_job.

declare
	m_job	number;
	m_inst	number;
	m_date	date;
	m_jqs	number;

begin
	select	instance_number
	into	m_inst
	from	v$instance;

	dbms_job.submit(
		job		=> m_job,
		what		=> 'rac_statspack(7);',
		next_date	=> trunc(sysdate + 1 / 24,'HH'),
		interval	=> 'trunc(SYSDATE + 1 / 24,''HH'')',
		no_parse	=> TRUE,
		instance	=> m_inst,
		force		=> true
	);
	commit;

	select
		next_date
	into	m_date
	from	dba_jobs
	where	job = m_job
	;

	select
		value
	into	m_jqs
	from	v$parameter
	where	name = 'job_queue_processes'
	;

	dbms_output.put_line('Job number: ' || m_job);
	dbms_output.put_line('Next run time: ' || to_char(m_date,'dd-Mon-yyyy hh24:mi:ss'));
	dbms_output.put_line('Current Job Queues: ' || m_jqs || ' (must be greater than zero)');

end;
/

Warning: Judging by the date stamps on my files it’s at least 18 months since I last took this approach with a system – so (a) you might want to test it carefully before you use it and (b) you might want to modify the code to use dbms_scheduler to run the job rather than dbms_job.

[Further Reading on Statspack]