SQL Profiles (10g)

[Forward to part 2]

When the Tuning Advisor suggests that you accept a SQL Profile, what is it offering you? If you want to find out, the following SQL seems to be the appropriate query to run before you accept the profile:

rem
rem     Script:         dbms_sqltune_02.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2004
rem
rem     Updated (Sep 2021):        
rem             attr1 is for 10g, attr5 is for 11g+
rem             report only the SQL Profile recommendation
rem

set linesize 180
set pagesize  60
set trimspool on

column attr      format a80
column rat_type  format a20
column rec_type  format a20

break on task_id on rec_id skip 1
select
        rat.task_id,
        rat.rec_id,
--      rat.type        rat_type,
        rec.type        rec_type,
        rat.obj_id,
        nvl(attr1,attr5)        attr
from
        sys.wri$_adv_tasks            tsk,
        sys.wri$_adv_rationale        rat,
        sys.wri$_adv_recommendations  rec
where
        tsk.name    = {sql tuning task name}
and     rat.task_id = tsk.id
and     rec.task_id = rat.task_id
and     rec.id      = rat.rec_id
and     rat.type    = 'RECOMMENDATION'
and     rec.type    in ( 
                        'SQL PROFILE',
                        'PARALLEL EXECUTION',
                        'EXADATA AWARE SQL PROFILE'
        )
order by
        rat.task_id,
        rat.rec_id,
        rat.id
/

The {sql tuning task name} will be the task name generated and printed at the top of the Enterprisie Manager screen where you can see the recommendations. The output might list a few lines with values like the following(which I have edited for cosmetic effect) for the attr:

 
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "REQUESTS"@"SEL$1", REQ_UK, SCALE_ROWS=5.786002118) 
OPT_ESTIMATE(@"SEL$1", TABLE,      "REQUESTS"@"SEL$1",         SCALE_ROWS=0.0004355485551) 

These are actually hints – they would be valid if you included them in the usual fashion in your SQL (although that wouldn’t be a good idea at present as they are undocumented, unsupported, and liable to change in their implementation).

Like Stored Outlines, the SQL Profile consists of a stored SQL statement, and a set of hints that will be brought into play when that SQL has to be optimised. Unlike Stored Outlines, the hints for SQL Profiles do not attempt to dictate execution plans explicitly. Instead they supply arithmetical correction factors to the optimizer as it does its arithemetic because it is still possible, even with a 100% sample size, for the optimizer to come up with some bad estimates from your statistics and produce an unsuitable execution path.

In principle, if the data distributions do not change, then a stored profile will ensure that the optimizer “understands” your data and does the right thing – even when the data volume changes.

Update Sep 2021

After a question on Oracle-L asked why a plan “suddenly changed” a few months after an upgrade from 11g to 19c I revisited this note and re-ran a simple test, and found that the query now produces far more data than it used to. After a brief investigation I have updated the script to join to wri$_adv_recommendations to report only the recommendation that is a SQL Profile (and there are three possible types of SQL Profile, hence the IN-list).

I’ll also mention that since SQL Profiles are things that “correct” the gathered object statistics and the estimates the optimizer makes from those statistics, then doing an upgrade that changes the methods that Oracle uses to gather statistics could make the SQL Profile irrelevant, or (worst case) just unsuitable enough that it appears to be helping some of the time.

When you upgrade it’s always worth doing some tests where you disable all the SQL Profiles, Baselines, and Patches in case the optimizer no longer needs them.

[Forward to part 2]