Revealing the Hints Behind the Automatic SQL Tuning Advisor

A client recently supplied a list of 50+ SQL IDs that should receive SQL profiles, and I’ve been working with Gwen Shapira to review the list. Further discussion showed that this list had come from the Automatic SQL Tuning feature, installed by default in Oracle 11g. The report includes a list of recommended SQL profiles ordered by “Maximum Benefit”, and in our case it included several hundred statements. As far as I can gather, the expected workflow is to see the recommendations, look at the before- and after- execution plans, and accept the recommendations.

Before blindly accepting recommendations, though, I like to see what exact changes are being proposed. They aren’t listed anywhere in the report, and require some extra work to uncover. Kerry Osborne has blogged about this issue before, but his post details profiles after they’re accepted; I want to see what they might be before.

The first step is to get the automatic SQL tuning advisor report. (Note that the SQL tuning advisor requires a license for the Oracle Tuning Pack.) You likely have this already, but in my case, all I had was a SQL ID and no idea which execution generated the advice. dbms_sqltune.report_auto_tuning_task has default NULL parameters that report on the previous run, but for me, the previous run had no recommendations at all. (As of Oracle 11.2.0.2, dbms_sqltune.report_auto_tuning_task is deprecated in favor of dbms_auto_sqltune.report_auto_tuning task. As far as I can tell, the only difference is a cleaner security model, requiring DBA privileges for automatic SQL tuning, but allowing non-DBA users to still run the tuning advisor on a specific statement.)

I created a query that dumps all automatic SQL Tuning Advisor findings ever made. Some disclaimers before you run it: On a long-running system, it generates a lot of output. And it lists old recommendations that may no longer apply to your situation. This particular system was very recently deployed and had virtually no tuning done, so this wasn’t a problem for me (though the full report was still over 16m in size).

select dbms_sqltune.report_auto_tuning_task(
  (select min(execution_name) from dba_advisor_findings
    where task_name like 'SYS_AUTO_SQL%'),
  (select max(execution_name) from dba_advisor_findings
    where task_name like 'SYS_AUTO_SQL%')
) from dual;

And the SQL profile benefits section came up, which was the source of the report we had already been given. However, it includes the crucial execution name and object ID to track down the actual recommendation:

-------------------------------------------------------------------------------
      SQLs with SQL Profile Findings Ordered by Maximum Benefit, Object ID
-------------------------------------------------------------------------------
execution name                 object ID  SQL ID        benefit
------------------------------ ---------- ------------- --------
EXEC_22037                          18170 b784vj0mpkr02   99.99%
EXEC_22327                          18198 3s547zh8pvswd   99.99%
EXEC_22704                          18819 bscx3000k0t4m   99.99%
EXEC_22704                          18832 5md4pjkw4c1s3   99.99%
EXEC_22704                          18913 3hp0x9s6kfptw   99.99%
EXEC_22704                          18914 9vxzkgbrvs73k   99.99%
EXEC_22704                          18915 d981sh8vzxhqw   99.99%
EXEC_22704                          18916 2mcu1ampwt6z5   99.99%
EXEC_23205                          19075 0fdcusd9jxfyf   99.99%
...

The report includes a full report for each SQL ID that had good information, notably the plans with and without the SQL profile, and execution statistics. But what I’m after is the real meat of the report: the hints that make up the SQL profile itself.

Using the execution name and object ID, we can drill down. I was surprised to find that the actual hints are available in DBA_ADVISOR_RATIONALE, so there’s no need to query internal SYS.WRI$ tables directly.

Using the top SQL on the list:

select rec_id, to_char(attr5)
from dba_advisor_rationale
where execution_name = 'EXEC_22037'
and object_id = 18170
and rec_id > 0
order by rec_id;
REC_ID TO_CHAR(ATTR5)
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------
3778   OPT_ESTIMATE(@"SEL$A065B7E5", NLJ_INDEX_SCAN, "AC"@"SEL$27", ("L"@"SEL$27", "C"@"SEL$27", "U"@"SEL$28", "O"@"SEL$28"), "I_ATTRCOL1", SCALE_ROWS=0.001476321563)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", NLJ_INDEX_SCAN, "AC"@"SEL$27", ("L"@"SEL$27", "C"@"SEL$27", "S"@"SEL$27"), "I_ATTRCOL1", SCALE_ROWS=0.0001109991632)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", NLJ_INDEX_SCAN, "AC"@"SEL$27", ("L"@"SEL$27", "C"@"SEL$27", "O"@"SEL$28"), "I_ATTRCOL1", SCALE_ROWS=0.001476321563)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", NLJ_INDEX_SCAN, "AC"@"SEL$27", ("C"@"SEL$27", "O"@"SEL$28"), "I_ATTRCOL1", SCALE_ROWS=0.0001112632572)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", NLJ_INDEX_SCAN, "AC"@"SEL$27", ("L"@"SEL$27", "C"@"SEL$27"), "I_ATTRCOL1", SCALE_ROWS=0.0576363657)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", NLJ_INDEX_SCAN, "AC"@"SEL$27", ("C"@"SEL$27"), "I_ATTRCOL1", SCALE_ROWS=12.16600796)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", JOIN, ("C"@"SEL$27", "O"@"SEL$28"), SCALE_ROWS=109344.0637)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", JOIN, ("C"@"SEL$27", "U"@"SEL$28", "O"@"SEL$28"), SCALE_ROWS=3180606.747)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", JOIN, ("U"@"SEL$28", "O"@"SEL$28"), SCALE_ROWS=215.1396307)
3778   OPT_ESTIMATE(@"SEL$A065B7E5", JOIN, ("L"@"SEL$27", "C"@"SEL$27"), SCALE_ROWS=210.5028193)

And that’s the proposed SQL profile itself. We can see that the optimizer’s estimates are way off; the join cardinality between C, U, and O, for example, is over 3 million times the estimate, at least with the combination of bind variables the tuning advisor used. (Christo Kutrovsky has covered the OPT_ESTIMATE hint on this blog before.)

By taking these hints, we can manually run the SQL statement, and more importantly, test it with various combinations of bind variables and optimizer environments the application actually uses to confirm that the proposed SQL profile will give consistently good results.

A few other references I discovered later:

Oracle-base article
Nokia OpenWorld presentation