Oracle DBA – A lifelong learning experience

Oracle Performance Management with Gaja Vaidyanatha

Posted by John Hallas on October 21, 2009

I attended this 2 day workshop ,held in London, last week.

I already owned both of Gaja’s book, Tales of the Oak Table and 101 Performance Tuning so I was reasonably familiar with  his background.

The course started of badly as Gaja was late arriving due to travel delays and so we did not kick off until 11:30 which was either 1.5 or 2 hours depending on whom you asked. He hit the ground running with a quick introduction and then a round the room ‘what do you want to get from this’ session.

The sessions covered everything set out in the advertising blurb, namely architecture, tracing, use of the OWI interface and a planned methodology for dealing with problems. One key point , apart from the availability of RAT in 11g was that the newer release did not add much to what Oracle 10g already provided with regard to performance tuning. 

The key methodology thoughts were to clearly define a goal, run a simple diagnostic routine and then repeat. With regard to SQL optimisation the 2 things to remeber were

  1. Reducing logical I/O is primary in any performance tuning engagement
  2. Hand in hand reducing elapsed time is equally important

The repeatable process is to find ‘interesting’ PIDS from glance or top, map them to a SPID (SID and SERIAL#) to find a session then run a 10046 trace and find the most expensive step (often the  most indented call).

Fix the code (one change at a time) and repeat.  If a 10046 trace does not help then get down to the OS level and look at how resources are being used.

That all sounds pretty simple and indeed it is.  I could write more about the methodology but I am concious that it is someone’s livelyhood and not to say too much.

Without sounding too knowledgeable or clever I was aware of most of the content that was discussed in the 2 days. Where I gained real benefits was in the storage aspects that Gaja covered very well and offered real insights into CPU, hyper threading, HBA usage and loading and suchlike. Various RAID levels were well covered and we had  a very good discussion on the value of placing redo logs on RAID 1+0 and suchlike.

Most of the second day was taken up with running through some real world examples, some where the problem was obvious, others where I had no clue at all. Several people had brought in 10046 traces and AWR snapshots and we all gained real insight as Gaja looked at them for the first time and showed us the key points worthy of investigation.

All in all a very worthwhile 2 days. The target audience needs to have a reasonable Oracle knowledge beforehand as a couple of students were saying that they had found it very difficult to follow. Oh and be prepared for long days as on day one Gaja was still talking strongly upto 18:00 despite being deadbeat from travelling and the second day went on up to 17:00.

Executive summary. Highly recommended

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: