Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: OCP - Hotsos SQL Optimization

Re: OCP - Hotsos SQL Optimization

From: Michael Thomas <>
Date: Fri, 23 Apr 2004 01:04:56 -0700 (PDT)
Message-ID: <>


Hotsos has a bunch of stuff to attend, Symposium and Performance Diagnostics 101 and SQL Optimization 101, and hopefully more soon (I'm hoping for CP101). I'm not sure which we've been talking about, but the Symposium was one of the best events I've ever attended. The knowledge, people, and the energy were definately there and I'll be going back.

I agree with you about OP101 (SQL Opt) and the test harness. For me, the test harness would have taken me over a month to work out myself and I would have not even come close to what it is capable of doing. Therefore, I believe the harness alone was worth the cost of the course.... blah, blah, blah (sorry if my complements sound offensive). The teaching adds a tremendous amount to the course (and understanding the test harness) and alone is also worth the cost of the course. So I figure I got twice or 3x the value, and I was skeptical about even attending a course on "SQL Tuning". Duh.

The main thing I learned from the harness was from its organization. The main program (@do scripts) had three sections reporting 'timings', another 'work', a third 'waits and latches', plus misc like plans, etc. This is the fundamental (minimum) information for tuning SQL that every other method/tool I've used lacks in some detail. The harness also includes a bunch of other utilities (@hds data selectivity, @diff, scenario and workspace tools, etc) to support SQL tuning.

Good luck if you don't know what I'm talking about, you're missing something (sorry SQL tuning experts).

The 'open source' fashion of the harness has allowed me to build on the OP101 concepts. I've noticed many people on this list turn chicken when faced with volumes of 10053 trace file information (with noted exceptions). I believe there is much valuable information inside 10053 for various purposes.

The first time I considered 10053 or additional (to 10046) trace methods was after reading the last bits of Jonathan's book. However, since Cery and Jaff's book (couldn't resist fashionable mispellings) people now seem comfortable with 10046. Its sort of obvious that there is probably more work to be done in the SQL optimiZation (just to be fair with misspelling for our UK friends) area.

I agree with what you said too, "Looking forward to SQL test harness improvements". But, I'm not waiting because its really easy to build on OP101 test harness to do my own stuff.

Its unfortunate some employeers might not appreciate the DBA work to setup the harness in both a development and production environment. But, that's a common disease to be cured, isn't it? :-)



Mike Thomas  

 A "disadvatage" of the Hotsos SQL test harness is  the additional DBA
 work involved in setting up unit testing databases  so developers can
 experiment on their own (including bouncing their  database to see the
 optimizer mode differences for a SQL statement).  

 Note: I can share a wish with my employer for this  course to be brought
 on-site for developers and DBA's.  

 Have Fun :)  

>A hotsos course, just because I have heard of and
 respected the papers of the man known as Cary  Millsap since I first heard of Oracle.
>If your employer were willing to pay for some good
 training gig this year, what would you ask for?

Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Apr 23 2004 - 03:01:58 CDT

Original text of this message