Best method for tuning sub-optimal execution plans
How do you determine if the Oracle SQL optimizer has created a sub-optimal execution plan? re-run statistics and see what happens? wait for Oracle to find other execution plans? What if neither method is helping? Do you read the execution plan? What do you look at? Differences in actual vs estimated? How successful is that? Look for full table scans? Do you look at the 10053 trace? How much time and effort does that take? What do you look at in the 10053 trace. Do you have a systematic methodology that works in almost all cases?
Well there is a method that is reliable and systematic. It’s laid out in Dan Tow’s book SQL Tuning.
The method is tedious as it requires a lot of manual work to draw join trees, identify constraints and relationships, manual decomposition and execution of every 2 table join in the statement. It can add up to a lot of work but it is systematic and dependable.
Cool thing is it can all be done automatically with a tool called DB Optimizer that now (as I look today) only cost about $400.
If you cost your company $50/hour then in 8 hours of saved work it’s paid for its self. In my experience as a DBA I have serveral SQL a year that take me over a day to optimize manually but that I can get done in a few minutes with DB Optimizer. Thus with just one hard SQL the tool has paid for itself. The DB Optimizer analysis might run for a couple hours, but afterwords with the data it collects and presents, I can find better tuning path in minutes if it exists.
Here is previous blog post that gives some an overview
Here is Dan Tow’s book SQL Tuning that originally laid out the method.
Pick up a copy. I think it’s super cool and interested in feedback on the your experiences.