Home > Announcements, DBA, NoCOUG, Oracle, SQL > The golden rule of NOT tuning SQL

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.

Kindest regards,

Iggy Fernandez
NoCOUG Journal editor

Categories: Announcements, DBA, NoCOUG, Oracle, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: