The golden rule of NOT tuning SQL
Dear NoCOUG members and friends,
The golden rule of not tuning SQL is “operate with as little information as possible.” Not only will this increase your chances of failure but it will make it difficult for others to help you.
Every exercise in problem solving – no matter how little – has exactly six stages: Problem, Observation, Implication, Solution, Execution, and Documentation. I use the acronym POISED to remember the six stages. If you strive to operate with as little information as possible, you increase the chances that you will not progress beyond the Observation stage.
I recently saw a request for SQL tuning help (http://www.freelists.org/post/oracle-l/Query-tuning-help). The poster had provided the EXPLAIN PLAN output. You probably know that EXPLAIN PLAN output is not of much use in SQL tuning. He was asked to add the GATHER_PLAN_STATISTICS hint and provide the output of DBMS_XPLAN.DISPLAY_CURSOR (http://www.freelists.org/post/oracle-l/Query-tuning-help,7). The rowsource execution statistics in the DISPLAY_CURSOR output showed that hundreds of thousands of rows were being retrieved and then discarded. There was enough information to deduce the right joining order (http://www.freelists.org/post/oracle-l/Query-tuning-help,9) but not enough to explain why the query optimizer was not finding the right order.
The best tool for tuning SQL is SQLTXPLAIN because it collects all the information that is required for tuning SQL. The author of this tool is Carlos Sierra. He will be conducting a two-day SQL tuning master class at the CarrAmerica conference center in Pleasanton on February 19 and 20. He will also speak at our winter conference at the Oracle conference center in Redwood Shores on February 21. I hope that you can attend at least one event.
NoCOUG Journal editor