Revealing the Hints Behind the Automatic SQL Tuning Advisor

Posted in: Technical Track

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, 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;
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------
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



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Marc is a passionate and creative problem solver, drawing on deep understanding of the full enterprise application stack to identify the root cause of problems and to deploy sustainable solutions. Marc has a strong background in performance tuning and high availability, developing many of the tools and processes used to monitor and manage critical production databases at Pythian. He is proud to be the very first DataStax Platinum Certified Administrator for Apache Cassandra.

3 Comments. Leave new

Log Buffer #299, A Carnival of the Vanities for DBAs | The Pythian Blog
December 14, 2012 1:49 am

[…] very own Marc Fielding is revealing the hints behind the Automatic SQL Tuning […]


Will have to try this out. Up until now I believe I used DBA_SQLTUNE_PLANS and slogged through the OTHER_XML…

I could probably put some effort into formatting the XML better but didn’t know about DBA_ADVISOR_RATIONALE. looks like some other juicy stuff is in that view too.

Marc Fielding
March 8, 2013 1:13 am

Hi David,

OTHER_XML is indeed a bit tough to read. You can use a xmltable-type query to make them readable though; from a quick Google search I found that should be reasonably easy to adapt to DBA_SQLTUNE_PLANS.




Leave a Reply

Your email address will not be published. Required fields are marked *