An Approach to Tuning a SQL Statement

While application knowledge and experience is helpful, We've got a methodical approach to use when asked to tune a SQL statement.

Here's the basic approach/process that can be used to diagnose and analyze a SQL statement..

Analyze the Statement

1. We can start by reviewing the WHERE clause, dividing it into clauses that restrict rows and those that join tables.

2. We can locate candidate Driving Tables by looking at the "restrict rows" clauses as determining which table has the most restrictive clauses ( i.e. those that will result in the fewest rows). If not sure between a couple of candidates, we can pick one.

3. Next we have to list all the tables in the FROM clause, starting with the driving table, using the JOIN criteria to link from one table to the next. This generates an ordered list of how the database should be accessing the tables, starting with the candidate driving table. This exercise generates all possible ways to join the tables together as well as making sure there are Join criteria for all the tables in the FROM clause.

4. Look for indexes on the driving table and determine which should be used or if a full table scan would be best. If we find a better index than the one the optimizer picks then we add an Index hint to get the desired index to be used. If a full table scan is in order, we consider adding a parallel hint.

5. We can try to join two tables that have restrictive where clauses before joining two tables that don't have any such clauses.

6. We now have one or more candidate explain plan(s) that need to be tested.
Compare Plans

1. Next we compare the actual explain plan of the statement in question with the candidate plans generated in the above exercise

2. Looking at the actual data (and running some queries to collect metrics) we consider trying different driving tables and join orders.

3. We review and compare each, and subjectively arrive at a best guess plan.

Test it out

1. The most effective hint we've found is the ORDERED hint. This works especially well when you have a lot of tables in the FROM clause.

2. We add /*+ ordered */ after the Select verb and the list the tables in the from clause from first to last the way we listed them in Analysis Step 3. Adding other hints like /*+ Full (driving table alias) */ is also a good way to get the optimizer to use the proper driving table.

3. Run an explain plan using the above HINTs.

4. If the plan looks good, execute it for real. Set Timing On and set Autotrace On (hopefully you have that enabled in your database)

5. Run the old and new statements several times to see how the timings work out. The Autotrace summary of blocks touched is a. good indicator of performance: the fewer blocks touched, the better.

Tuning Hints and Tips

1. There are many other hints and techniques that can influence an explain plan. Keep in mind that a Hash Join is nearly always better than a Merge Join

2. Nested Loop is better only if a few rows are being retrieved.

3. Tuning sub-selects can be tricky. We will often rewrite a statement to replace " IN (Select...) or a NOT IN" with a join to an in-line view and achieve significant performance increase by avoiding executing thousands of Nested Loop index reads with a hash join. Listing more statement rewrite options is beyond the scope of this article.

Those are the basic steps We follow. Give them a try and over time we are confident your SQL tuning skills will expand.