Why Isn’t Oracle Using My Outline / Profile / Baseline?

I seem to have spent a lot of time during the last week having conversations about why Oracle is not locking plans, even when an Outline or SQL Profile has been created. I mean, their whole purpose in life is to keep the optimizer from changing plans, right? Here’s a bit of an email I sent with my thoughts on the issue during a conversation on the Oracle-L list.


First, I think you need to convince yourself as to whether the profile is being used or not. There is a column in v$sql (sql_profile) which will have the name of the profile if one is being used. Also xplan will show that a profile is being used at the bottom of the output.

If the profile is being used, but the plan is not what you expected, there are a couple of likely culprits.

First, if it’s a profile generated by SQL Tuning Advisor it’s quite possible that it will have one or more OPT_ESTIMATE hints – which apply scaling factors to various operations. These types of profiles can and do switch plans fairly easily, because they are not even attempting to lock anything. As the stats change, the plans can change.

The second common possibility is that Oracle decided to use an index hint that doesn’t specify the index name, but rather the columns that it would like to use an index on. For example:

INDEX_RS_ASC(@”SEL$1″ “TEST_TABLE”@”SEL$1″ (“TEST_TABLE”.”COL2″ “TEST_TABLE”.”TEST_TABLE_ID”))

This hint does not specify an index, but rather columns to use an index on. I’m not sure when this form became a preferred approach but it certainly seems to show up pretty often. So that leaves the optimizer with the flexibility to pick an index based on stats. In systems with lots of indexes, this makes it more likely that a statement will switch plans even though it’s using an Outline or Profile. In fact, you may have a statement with multiple plans in the shared pool, pick one to create an Outline or Profile with, enable it, and have the new child use a plan that’s different from the plan of the child you used to create it with. Very frustrating.

So as I said in the email, the most likely cause for SQL Profiles not working is the non-specific form of index hints that are being used. However, as I was doing some research for this post, I found a couple situations where SQL Profiles just flat don’t work as advertised. I’ll show you an example, but first here are a few scripts that I will use (I sometimes don’t set “echo on” because it makes the examples so messy – so just hit the links to view the scripts):

Updated: 6/14/13
Note that I have rewritten the create_sql_profile.sql script so that it is not dependent on the rg_sqlprof1 script and to add additional functionality. Please use the newer one. Also I have rewritten the sql_profile_hints.sql script so that it works with 10g and 11g. I have disabled the links to the old versions in the list below.

sql_hints.sql – shows the hints stored in the v$sql_plan.other_xml column
sql_profile_hints.sql – shows the hints associated with a SQL Profile
sql_profile_hints11.sql – shows the hints associated with a SQL Profile (for 11g)
create_sql_profile.sql – creates a profile for a statement in the shared pool (wrapper for rg_sqlprof1.sql)
rg_sqlprof1.sql – does the real work to create a profile for a statement in the shared pool
fix_sql_profile_hint.sql – replaces a hint in a profile with whatever you want

Here’s the set up: a simple single table query that wants to do a full table scan and a second version that uses a hint to force an index. A profile created on the hinted statement should keep it from changing, but instead, it changes the plan back to a full table scan. Here’s the example:

> sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jul 30 15:44:02 2009
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> @flush_pool
 
System altered.
 
SQL> set echo on
SQL> @avgskew
SQL> select avg(pk_col) from kso.skew a where col1 > 0
  2  /
 
AVG(PK_COL)
-----------
 16093749.3
 
SQL> @avgskew_hint2
SQL> select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
  2  /
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
                                                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
SQL> -- runs too long, so I ctl-C out
SQL>
SQL> @find_sql
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs,
  2  (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
  3  buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
  4  sql_text
  5  from v$sql s
  6  where upper(sql_text) like upper(nvl('&sql_text',sql_text))
  7  and sql_text not like '%from v$sql where sql_text like nvl(%'
  8  and sql_id like nvl('&sql_id',sql_id)
  9  order by 1, 2, 3
 10  /
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  568322376          1         11.02      162,436 select avg(pk_col) from kso.skew a where col1 > 0
8js5bhfc668rp      0 3062520228          1          6.84    1,252,591 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
  2  /
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |  1234 | 13574 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1">0)
 
 
19 rows selected.
 
SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
  2  /
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
 
Plan hash value: 3062520228
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    28M(100)|          |
|   1 |  SORT AGGREGATE              |                |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW           |  1234 | 13574 |    28M  (1)| 96:38:01 |
|*  3 |    INDEX FULL SCAN           | SKEW_COL2_COL1 |    32M|       |   120K  (1)| 00:24:11 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1">0)
       filter("COL1">0)
 
 
22 rows selected.
 
SQL> -- so the unhinted one does a FULL TABLE SCAN
SQL> -- the hinted version uses an index 
SQL> --     note that it's doing an INDEX FULL SCAN on SKEW_COL2_COL1
SQL> 
SQL> -- let's create a profile on the hinted version - 8js5bhfc668rp       
SQL> 
SQL> set echo off
SQL> @create_sql_profile
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
Enter value for category: 
Enter value for force_matching: 
 
PL/SQL procedure successfully completed.
 
SQL> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_8js5bhfc668rp          DEFAULT         ENABLED  select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
 
SQL> -- now let's execute the statement again                   
SQL> 
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
                                                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
 
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  568322376          1         11.02      162,436 select avg(pk_col) from kso.skew a where col1 > 0
8js5bhfc668rp      0  568322376          1          6.25      101,709 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |  1234 | 13574 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1">0)
 
Note
-----
   - SQL profile PROFILE_8js5bhfc668rp used for this statement
 
 
24 rows selected.
 
SQL> -- interesting - the statement is now using the profile but the plan changed to a full table scan      
SQL> --   this is not what's supposed to happen!
SQL>
SQL> -- let's see what hints got generated for the profile
SQL> 
SQL> set echo on  
SQL> @sql_profile_hints11
SQL> /* 11g */
SQL> 
SQL> set lines 155
SQL> col hint for a150
SQL> select hint from (
  2  select p.name, p.signature, p.category,
  3         row_number()
  4           over (partition by sd.signature, sd.category order by sd.signature) row_num,
  5         extractValue(value(t), '/hint') hint
  6  from sqlobj$data sd, dba_sql_profiles p,
  7       table(xmlsequence(extract(xmltype(sd.comp_data),
  8                                 '/outline_data/hint'))) t
  9  where sd.obj_type = 1
 10  and p.signature = sd.signature
 11  and p.name like nvl('&name',name)
 12  )
 13  order by row_num
 14  /
Enter value for name: PROFILE_8js5bhfc668rp
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
 
6 rows selected.
 
SQL> 
SQL> -- there are numerous problems with the index hint
SQL> --   1. the non-specific version of the hint allows the optimizer a lot of flexibility
SQL> --   2. The hint is attempting INDEX RANGE SCAN, but the plan was INDEX FULL SCAN
SQL> --   3. All columns in the index are specified, even though the SQL only uses one of the columns
SQL> 
SQL> -- let's check it one more time
SQL> 
SQL> @drop_sql_profile
SQL> BEGIN
  2    DBMS_SQLTUNE.drop_sql_profile (
  3      name   => '&profile_name',
  4      ignore => TRUE);
  5  END;
  6  /
Enter value for profile_name: PROFILE_8js5bhfc668rp
 
PL/SQL procedure successfully completed.
 
SQL> set echo off
SQL> @flush_pool
 
System altered.
 
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
                                                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
 
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp      0 3062520228          1          3.67       50,061 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
 
Plan hash value: 3062520228
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    28M(100)|          |
|   1 |  SORT AGGREGATE              |                |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW           |  1234 | 13574 |    28M  (1)| 96:38:01 |
|*  3 |    INDEX FULL SCAN           | SKEW_COL2_COL1 |    32M|       |   120K  (1)| 00:24:11 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1">0)
       filter("COL1">0)
 
 
22 rows selected.
 
SQL> -- back to INDEX FULL SCAN on SKEW_COL2_COL1
SQL> 
SQL> -- let's see what hints are attached to the statement in v$sql_plan (so called Outline Hints)
SQL> 
SQL> @sql_hints        
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
 
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
 
6 rows selected.
 
SQL> -- so the problem is the outline hints that get created when the statement is parsed
SQL> -- profiles just use what's there
SQL> 
SQL> -- let's create the profile again
SQL> 
SQL> @create_sql_profile
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
Enter value for category: 
Enter value for force_matching: 
 
PL/SQL procedure successfully completed.
 
SQL> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_8js5bhfc668rp          DEFAULT         ENABLED  select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
 
SQL> -- and let's check the hints for the profile again
SQL>
SQL> @sql_profile_hints11
Enter value for name: PROFILE_8js5bhfc668rp
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
 
6 rows selected.
 
SQL> -- yep exactly the same as what's in other_xml field of v$sql_plan 
SQL> 
SQL> -- let's try executing the statement again - should use the profile 
SQL> 
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
                                                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
 
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp      0  568322376          1          4.93       77,126 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |  1234 | 13574 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1">0)
 
Note
-----
   - SQL profile PROFILE_8js5bhfc668rp used for this statement
 
 
24 rows selected.
 
SQL> -- yep, it is using the profile but not using the plan that the statement was using before we created the profile
SQL> 
SQL> -- BAD OPTIMZER! creating a profile on a statement in the shared pool should not change it's plan!

So let’s see if we can work around the problem. To do that, all we need to do is replace the bad index hint (or hints) with one that will work. In this case we’ll probably need to avoid the INDEX_RS_ASC hint because it probably won’t work since we’re not specifying the leading part of the index. Here’s an example:

 
SQL> -- let's see if we can fix it
SQL> 
SQL> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_8js5bhfc668rp          DEFAULT         ENABLED  select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
 
SQL> @sql_profile_hints11
Enter value for name: PROFILE_8js5bhfc668rp
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
 
6 rows selected.
 
SQL> -- let's replace the bad hint using fix_sql_profile_hints.sql
SQL> 
SQL> -- but first, let's see what options are available for index hints
SQL> -- (v$sql_hint shows up in 11g - nice new view)
SQL>
SQL> @valid_hints
SQL> select name,version from v$sql_hint
  2  where upper(name) like '%'||upper(nvl('&hint',name))||'%'
  3  order by name;
Enter value for hint: INDEX
 
NAME                                                             VERSION
---------------------------------------------------------------- -------------------------
DOMAIN_INDEX_FILTER                                              11.1.0.6
DOMAIN_INDEX_NO_SORT                                             8.1.5
DOMAIN_INDEX_SORT                                                8.1.5
INDEX                                                            8.0.0
INDEX_ASC                                                        8.1.0
INDEX_COMBINE                                                    8.1.0
INDEX_DESC                                                       8.1.0
INDEX_FFS                                                        8.1.0
INDEX_JOIN                                                       8.1.5
INDEX_RRS                                                        9.0.0
INDEX_RS_ASC                                                     11.1.0.6
INDEX_RS_DESC                                                    11.1.0.6
INDEX_SS                                                         9.0.0
INDEX_SS_ASC                                                     9.0.0
INDEX_SS_DESC                                                    9.0.0
INDEX_STATS                                                      10.1.0.3
LOCAL_INDEXES                                                    9.0.0
NO_DOMAIN_INDEX_FILTER                                           11.1.0.6
NO_INDEX                                                         8.1.5
NO_INDEX_FFS                                                     10.1.0.3
NO_INDEX_SS                                                      10.1.0.3
NO_PARALLEL_INDEX                                                8.1.0
NO_USE_INVISIBLE_INDEXES                                         11.1.0.6
NO_XMLINDEX_REWRITE                                              11.1.0.6
NO_XMLINDEX_REWRITE_IN_SELECT                                    11.1.0.6
NUM_INDEX_KEYS                                                   10.2.0.3
PARALLEL_INDEX                                                   8.1.0
USE_INVISIBLE_INDEXES                                            11.1.0.6
USE_NL_WITH_INDEX                                                10.1.0.3
XMLINDEX_REWRITE                                                 11.1.0.6
XMLINDEX_REWRITE_IN_SELECT                                       11.1.0.6
 
31 rows selected.
 
SQL> -- so there doesn't appear to be a hint that forces an INDEX FULL SCAN (although INDEX_FFS is close)
SQL> -- let's just go with the old fashioned INDEX hint
SQL>
SQL> @fix_sql_profile_hint 
Enter value for profile_name: PROFILE_8js5bhfc668rp
Enter value for bad_hint: INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
Enter value for good_hint: INDEX(@"SEL$1" "A"@"SEL$1" "SKEW_COL2_COL1")
version: 11
 
PL/SQL procedure successfully completed.
 
SQL> @sql_profile_hints11
Enter value for name: PROFILE_8js5bhfc668rp                       
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" "SKEW_COL2_COL1")
 
6 rows selected.
 
SQL> -- looks better, let's test it
SQL> @flush_pool
 
System altered.
 
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
                                                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
 
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp      0 3062520228          1          3.32       37,091 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
 
Plan hash value: 3062520228
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    28M(100)|          |
|   1 |  SORT AGGREGATE              |                |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW           |  1234 | 13574 |    28M  (1)| 96:38:01 |
|*  3 |    INDEX FULL SCAN           | SKEW_COL2_COL1 |    32M|       |   120K  (1)| 00:24:11 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile PROFILE_8js5bhfc668rp used for this statement
 
 
26 rows selected.
 
SQL> -- yep that works!
SQL> 
SQL> -- note - the syntax is very demanding (recommend - INDEX( + cut and paste query block id part + "INDEX_NAME")

So a few closing comments:

  • This particular example behaved consistently from 10.2.0.3 through 11.1.0.7 (that is to say, it doesn’t work in any of those versions, but it behaves in a consistent manner).
  • This particular example behaved the same for Outlines as it did for SQL Profiles.
  • I believe that the best replacement for the INDEX_XXX (table_name.column_name) hint is the simple INDEX(alias index_name) hint.
  • The syntax of these hints is very particular, so the best approach in my opinion is to keep all the query block and alias bits and simply replace the (table_name.column_name) bit with the index_name.
  • Remember that hints don’t give any error messages, they are just silently ignored if something isn’t right (that’s always been a bit frustrating, seems like there ought to be a switch that that could be set which would allow an error to be thrown if a hint has invalid syntax).
  • Outlines, SQL Profiles, Baselines (and Hints themselves for that matter) are a bit flakey (partly because they are not well understood due to the small amount of documentation, and partly because of the lack of error messages when hints are poorly formed). So you should always verify what they are doing by looking at the hints that are being applied. 10053 traces can help – there is a section at the very bottom of the trace file where it indicates if any hints have generated errors. This included hints applied via Outlines or Profiles.
  • It’s clear that a syntax error on one of the hints in a Profile will not invalidate the whole Profile. The optimizer will attempt to honor any hints that are valid. (if you’re interested, you can play with the fix_sql_profile_hint.sql script to verify this for yourself)
  • All these options are extreme measures that allow manipulation of execution plans when you can’t touch the actual SQL statement. While they can provide very dramatic results in a very short time frame, generally speaking, I do not consider them to be the best long term solution.

As always, your comments are welcome.

Leave a Reply