Add new comment

When working on a SQL Tuning issue I often kick off a SQL Tuning Advisor job on the SQL while I gather info about statistics and other things I like to check when tuning. After I get an idea of where performance might be suffering I use the SQL Tuning Advisor results for a second opion.

Unfortunately more often than not the SQL Tuning Advisor reports it can dramatically improve the performance of a SQL Statement by using a SQL Profile to force a different plan.

Well that's all well and good, but what is Oracle doing behind the scenes? Sure it shows me the original and new plan... but what magic happened to get the new plan? Can the information used in the new plan shed some light on what the root issue might be?

Here is a SQL that will show you the completed tuning jobs and theirresults

SELECT TASK_NAME, CREATED, Attribute, PLAN_HASH_VALUE, OTHER_XML
  FROM DBA_ADVISOR_TASKS DAT, DBA_SQLTUNE_PLANS DSP 
 WHERE DAT.advisor_name = 'SQL Tuning Advisor' 
   AND DAT.status <> 'CANCELLED'
   AND DSP.OTHER_XML IS NOT NULL;

The OTHER_XML field has information that Oracle will use to create the SQL Profile. Basically it is a collection of hints. Decoding the OTHER_XML text will be left to the reader for now. I usually do some creative text editor search and replace on the markup to make it more readable or save to an .XML file and open with an XML browser.

There are clues in them there OTHER_XML files! Look at the hints, what does Oracle think will give you better performance?

The biggest thing I have found is sometimes Oracle goes wild with the Cardinality related hints. These hints may show up to compensate for stale or missing object statistics.

-Dave