Fixing Bad Index Hints in SQL Profiles (automatically)

I’ve written before on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to “lock” execution plans. That is to allow the optimizer as little flexibility as possible. With 10g and 11g, it appears the goal has swung away from the “locking” concept and towards allowing the optimizer more flexibility. I’ll show you an example of what I mean in a minute, but I must say that I find this decision to be irritating at best. It seems to me that the whole reason for implementing one of these objects is to try to keep the optimizer from changing its mind. After all, it was originally called “Plan Stability” by the Oracle marketing guys.

One of the main offenders in this regard is the use of a new format available for index hints as of 10g. Prior to 10g, the index looked basically like this:

INDEX(TABLE_NAME INDEX_NAME)

Translation: If possible, use this index on this object.

As of 10gR2, there is a new possible format which appears to be used (at least most of the time) when Outlines/Profiles/Baselines are created. The new format looks like this:

INDEX(TABLE_NAME (TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …))

Translation: If possible, use any available index on any of these columns.

As you can see, this format leaves a lot more to the optimizer’s discretion than the older format. (Note: the older format is still valid)

Update 01/03/11: Thanks to Tony Hasler for making me get the syntax right (see comments below).

In my previous post I published a script for changing an individual SQL Profile hint, but recently I had a situation where there was a SQL Profile that had 20+ index hints where the statement was suffering from plan instability, despite the fact that it was using a SQL Profile. Rather than manually look up the correct index names and change the hints one by one (a very error prone proposition), I decided to write a script that would automatically change all the INDEX hints from the “non-specific column oriented” format to the “specific index name” format.

But before I give you the script – a little history. Here are Outline hints for the same statement from 9.2.0.8, 10.1.0.?, 10.2.0.4, and 11.2.0.1:

 
> sqlplus "/ as sysdba"
 
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Nov 16 10:40:51 2009
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
 
SYS@LAB920> @find_sql_stats9
Enter value for sql_text: %skew%
Enter value for address: 
Enter value for hash_value: 
 
HASH_VALUE  CHILD  PLAN_HASH      EXECS     AVG_ETIME       AVG_CPU       AVG_PIO       AVG_LIO   AVG_FETCHES   AVG_ROWS SQL_TEXT
---------- ------ ---------- ---------- ------------- ------------- ------------- ------------- ------------- ---------- -----------------------------------------
 216499009      0 3062520228          1          1.90          1.60     11,692.00     11,942.00          1.00          0 select /*+ index(a SKEW_COL2_COL1) */ avg
 
SYS@LAB920> select sql_text from v$sqlarea where hash_value = '216499009';
 
SQL_TEXT
------------------------------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from
kso.skew a where col1 > 0
 
SYS@LAB920> create outline "OUTLINE_9.2.0.8" on select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0;
 
Outline created.
 
SYS@LAB920> @outlines
Enter value for category: 
Enter value for name: 
 
CATEGORY        NAME                           USED         ENABLED       HINTS SQL_TEXT
--------------- ------------------------------ ------------ ------------ ------ ----------------------------------------------------------------------
DEFAULT         OUTLINE_9.2.0.8                USED         ENABLED           6 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher
DEFAULT         SKEW_HINTED                    UNUSED       ENABLED           6 select /*+ index (a skew_col4) */ avg(col1) from kso.skew a where col4
DEFAULT         SKEW_NOT_HINTED                USED         ENABLED           6 select avg(col1) from kso.skew where col4 <> 'Y'
 
SYS@LAB101> @outline_hints
Enter value for name: OUTLINE_9.2.0.8
Enter value for hint: 
 
NAME                      HINT
------------------------- ------------------------------------------------------------------------------------------------------------------------
OUTLINE_9.2.0.8           NO_EXPAND
OUTLINE_9.2.0.8           ORDERED
OUTLINE_9.2.0.8           NO_FACT(A)
OUTLINE_9.2.0.8           <p><span style="background-color:yellow">INDEX(A SKEW_COL2_COL1)</span></p>
OUTLINE_9.2.0.8           NOREWRITE
OUTLINE_9.2.0.8           NOREWRITE
 
6 rows selected.
 
...
 
SYS@LAB101> @version
 
VERSION           MAJOR_RELEASE     MINOR_RELEASE   MINOR3 MINOR4 MINOR5
----------------- ----------------- --------------- ------ ------ ------
10.1.0.3.0        10                1                      3      0
 
SYS@LAB101> @outline_hints
Enter value for name: OUTLINE_10.1.0.3
Enter value for hint: 
 
NAME                      HINT
------------------------- ------------------------------------------------------------------------------------------------------------------------
OUTLINE_10.1.0.3          NO_EXPAND(@"SEL$1" )
OUTLINE_10.1.0.3          LEADING(@"SEL$1"  "A"@"SEL$1")
OUTLINE_10.1.0.3          NO_STAR_TRANSFORMATION(@"SEL$1" )
OUTLINE_10.1.0.3          NO_FACT(@"SEL$1" "A"@"SEL$1")
OUTLINE_10.1.0.3          NO_INDEX(@"SEL$1" "A"@"SEL$1" ("SKEW"."PK_COL"))
OUTLINE_10.1.0.3          NO_INDEX(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL3"))
OUTLINE_10.1.0.3          NO_INDEX(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL1"))
OUTLINE_10.1.0.3          NO_INDEX(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL4"))
OUTLINE_10.1.0.3          INDEX(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
OUTLINE_10.1.0.3          NO_REWRITE(@"SEL$1" )
 
10 rows selected.
 
...
 
 
SYS@LAB1024> @version
 
VERSION           MAJOR_RELEASE     MINOR_RELEASE   MINOR3 MINOR4 MINOR5
----------------- ----------------- --------------- ------ ------ ------
10.2.0.4.0        10                2               0      4      0
 
SYS@LAB1024> @outline_hints
Enter value for name: OUTLINE_10.2.0.4
Enter value for hint: 
 
NAME                      HINT
------------------------- ------------------------------------------------------------------------------------------------------------------------
OUTLINE_10.2.0.4          INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
OUTLINE_10.2.0.4          OUTLINE_LEAF(@"SEL$1")
OUTLINE_10.2.0.4          ALL_ROWS
OUTLINE_10.2.0.4          OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OUTLINE_10.2.0.4          IGNORE_OPTIM_EMBEDDED_HINTS
 
...
 
SYS@LAB111> @outline_hints
Enter value for name: OUTLINE_11.1.0.7
Enter value for hint: 
 
NAME                      HINT
------------------------- ------------------------------------------------------------------------------------------------------------------------
OUTLINE_11.1.0.7          INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
OUTLINE_11.1.0.7          OUTLINE_LEAF(@"SEL$1")
OUTLINE_11.1.0.7          ALL_ROWS
OUTLINE_11.1.0.7          DB_VERSION('11.1.0.7')
OUTLINE_11.1.0.7          OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
OUTLINE_11.1.0.7          IGNORE_OPTIM_EMBEDDED_HINTS
 
6 rows selected.
 
...
 
SYS@LAB112> @outline_hints
Enter value for name: OUTLINE_11.2.0.1
Enter value for hint: 
 
NAME                      HINT
------------------------- ------------------------------------------------------------------------------------------------------------------------
OUTLINE_11.2.0.1          INDEX(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
OUTLINE_11.2.0.1          OUTLINE_LEAF(@"SEL$1")
OUTLINE_11.2.0.1          ALL_ROWS
OUTLINE_11.2.0.1          DB_VERSION('11.2.0.1')
OUTLINE_11.2.0.1          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OUTLINE_11.2.0.1          IGNORE_OPTIM_EMBEDDED_HINTS
 
6 rows selected.

So you can see the change to INDEX hints that occurred between 9i and 10g (also note that there was quite a lot of change between 10gR1 and 10gR2). The new INDEX hint format can cause some problems when it comes to trying to force a particular plan. In fact, there are cases where creating a SQL Profile on a statement will actually change its plan. I believe this is a bug, but it is related to the new index format. Here’s an example of that behavior:

> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 17 09:28:08 2009
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SYS@LAB1024> @flush_pool
System altered.
 
SYS@LAB1024> @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
 
 
SYS@LAB1024> -- that statement runs for a long time
SYS@LAB1024> --   so I just let it go for a few seconds and ctl-C out
SYS@LAB1024>
SYS@LAB1024> @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          4.00       13,230 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
1 row selected.
 
SYS@LAB1024> @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           |    32M|   335M|    28M  (1)| 96:39:29 |
|*  3 |    INDEX FULL SCAN           | SKEW_COL2_COL1 |    32M|       |   121K  (2)| 00:24:14 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1">0)
       filter("COL1">0)
 
 
21 rows selected.
 
SYS@LAB1024> @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.
 
SYS@LAB1024> @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
 
 
 
SYS@LAB1024> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 8js5bhfc668rp
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp      0  568322376          1          2.55       41,715 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
1 row selected.
 
SYS@LAB1024> -- so creating the profile changed the plan (this is a bug)
SYS@LAB1024> @sql_profile_hints
Enter value for profile_name: PROFILE_8js5bhfc668rp
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
 
5 rows selected.
 
SYS@LAB1024> @sql_hints
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
 
OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
 
5 rows selected.
 
SYS@LAB1024> -- hmmm 
SYS@LAB1024> -- let's try fixing the hint
SYS@LAB1024> 
SYS@LAB1024> @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_RS_ASC(@"SEL$1" "A"@"SEL$1" "SKEW_COL2_COL1")             
version: 10
 
PL/SQL procedure successfully completed.
 
SYS@LAB1024> @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
 
 
 
SYS@LAB1024> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 8js5bhfc668rp                                                   
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp      0  568322376          1          2.01       41,762 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
1 row selected.
 
SYS@LAB1024> -- so that didn't work, still doing the full table scan 
SYS@LAB1024> --   ahh, probably can't do a Index Range Scan Ascending on that index
SYS@LAB1024> --    the original statement was doing Index Full Scan 
SYS@LAB1024> --    let's try the simpler INDEX hint
SYS@LAB1024> 
SYS@LAB1024> @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_COL1")
Enter value for good_hint: INDEX(@"SEL$1" "A"@"SEL$1" "SKEW_COL2_COL1")
version: 10
 
PL/SQL procedure successfully completed.
 
SYS@LAB1024> @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
 
 
 
SYS@LAB1024> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 8js5bhfc668rp                         
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp      0 3062520228          1          1.70       13,270 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
                                                                      kew a where col1 > 0
 
 
1 row selected.
 
SYS@LAB1024> @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           |    32M|   335M|    28M  (1)| 96:39:29 |
|*  3 |    INDEX FULL SCAN           | SKEW_COL2_COL1 |    32M|       |   121K  (2)| 00:24:14 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1">0)
       filter("COL1">0)
 
Note
-----
   - SQL profile "PROFILE_8js5bhfc668rp" used for this statement
 
 
25 rows selected.
 
SYS@LAB1024> -- so that makes sense, the first attempt didn't work because it couldn't do 
SYS@LAB1024> -- an Index Range Scan on that index (it had to do an Index Full Scan)
SYS@LAB1024> -- thus is just silently ignored the hint

No big deal. The hint can easily be changed using something like my fix_sql_profile_hint.sql script (once again – please refer to Why Isn’t Oracle Using My Outline / Profile / Baseline for more details). However, this is a very error prone approach, especially if you are dealing with a statement that uses many indexes. So I decided to write a little script that attempts to automatically replace all of the new format INDEX hints, with the old format INDEX hints. This turned out to be a little trickier than I had anticipated, but it’s not too bad. I called it pif.sql (pif is short for Profile Index Fixer, by the way). There were a couple of approaches I could have taken to get this to work.

1. I could have prompted for a sql_id and plan_hash_value and looked for that plan in the cursor cache or the awr tables.

2. I could pull the list of columns from the INDEX hint and find the name of the index with exactly those columns on the base table (which may need to be translated from a synonym).

I chose option 2 (maybe I’ll go back and add option 1 if I run into a case that I can’t resolve with option 2 – or you can do it for yourself and send me what you come up with!). Anyway, the whole reason for this little exercise was a system that had several SQL Profiles which were not behaving, each with many tables and 10 – 15 indexes. It seems to have worked pretty well in that environment. Anyway, here’s an example of it in use (just a test system, not the real one that needed help):

> !sql
sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 17 19:29:31 2009
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SYS@LAB1024> @junk1
SYS@LAB1024> select sql_id, child_number child_no, cnt from (
  2    select sql_id, child_number, count(1) cnt from v$sql_plan
  3    where operation = 'INDEX'
  4    group by sql_id, child_number
  5    having count(1) > 2
  6    order by 3 desc
  7  ) where rownum < 20
  8  /
 
SQL_ID          CHILD_NO        CNT
------------- ---------- ----------
5ps3p5ma94bkh          0         41
78m9ryygp65v5          0         35
bnvfb5khhat7t          0         26
d89c1mh5pvbkz          0         26
fyrbzparfb4gb          0         21
3hgxzypxz2xpg          0         13
2c5p3qrtz23rp          0         13
7ayfw9g455pn3          0         13
20f6a85kwud5c          0         13
8usapcs6k1b7f          0         12
31a13pnjps7j3          3         11
31a13pnjps7j3          0         11
31a13pnjps7j3          2         11
31a13pnjps7j3          1         11
fyjb2abrfhy4u          0         11
bccv75rtxm77j          0         10
1uu12vzu39ts7          0          9
0xcprwqg912w6          0          9
2umtws6d10kzt          0          9
 
19 rows selected.
 
SYS@LAB1024> set echo off
SYS@LAB1024> @find_sql_long
Enter value for sql_id: fyrbzparfb4gb
Enter value for sql_text: 
 
SQL_TEXT
----------------------------------------------------------------------
SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLES
PACE_NAME, TABLESPACE_ID FROM SYS_DBA_SEGS WHERE SEGMENT_OBJD =
:B1
 
3 rows selected.
 
SYS@LAB1024> @dplan
Enter value for sql_id: fyrbzparfb4gb
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fyrbzparfb4gb, child number 0
-------------------------------------
SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, TABLESPACE_ID FROM
SYS_DBA_SEGS WHERE SEGMENT_OBJD = :B1
 
Plan hash value: 3220063432
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |       |       |   264 (100)|          |
|   1 |  VIEW                               | SYS_DBA_SEGS        |     3 |   351 |   264   (2)| 00:00:04 |
|   2 |   UNION-ALL                         |                     |       |       |            |          |
|   3 |    NESTED LOOPS                     |                     |     1 |   167 |   200   (2)| 00:00:03 |
|   4 |     NESTED LOOPS                    |                     |     1 |   157 |   199   (2)| 00:00:03 |
|   5 |      NESTED LOOPS                   |                     |     1 |   151 |   199   (2)| 00:00:03 |
|   6 |       NESTED LOOPS                  |                     |     1 |   137 |   198   (2)| 00:00:03 |
|   7 |        NESTED LOOPS OUTER           |                     |     1 |    59 |   187   (2)| 00:00:03 |
|*  8 |         TABLE ACCESS FULL           | OBJ$                |     1 |    44 |   186   (2)| 00:00:03 |
|   9 |         TABLE ACCESS CLUSTER        | USER$               |     1 |    15 |     1   (0)| 00:00:01 |
|* 10 |          INDEX UNIQUE SCAN          | I_USER#             |     1 |       |     0   (0)|          |
|* 11 |        VIEW                         | SYS_OBJECTS         |     1 |    78 |    11   (0)| 00:00:01 |
|  12 |         UNION ALL PUSHED PREDICATE  |                     |       |       |            |          |
|* 13 |          TABLE ACCESS CLUSTER       | TAB$                |     1 |    23 |     2   (0)| 00:00:01 |
|* 14 |           INDEX UNIQUE SCAN         | I_OBJ#              |     1 |       |     1   (0)| 00:00:01 |
|  15 |          TABLE ACCESS BY INDEX ROWID| TABPART$            |     1 |    16 |     1   (0)| 00:00:01 |
|* 16 |           INDEX UNIQUE SCAN         | I_TABPART_OBJ$      |     1 |       |     0   (0)|          |
|  17 |          TABLE ACCESS CLUSTER       | CLU$                |     1 |    13 |     2   (0)| 00:00:01 |
|* 18 |           INDEX UNIQUE SCAN         | I_OBJ#              |     1 |       |     1   (0)| 00:00:01 |
|* 19 |          TABLE ACCESS BY INDEX ROWID| IND$                |     1 |    19 |     2   (0)| 00:00:01 |
|* 20 |           INDEX UNIQUE SCAN         | I_IND1              |     1 |       |     1   (0)| 00:00:01 |
|  21 |          TABLE ACCESS BY INDEX ROWID| INDPART$            |     1 |    16 |     1   (0)| 00:00:01 |
|* 22 |           INDEX UNIQUE SCAN         | I_INDPART_OBJ$      |     1 |       |     0   (0)|          |
|* 23 |          TABLE ACCESS BY INDEX ROWID| LOB$                |     1 |    21 |     2   (0)| 00:00:01 |
|* 24 |           INDEX UNIQUE SCAN         | I_LOB2              |     1 |       |     1   (0)| 00:00:01 |
|  25 |          TABLE ACCESS BY INDEX ROWID| TABSUBPART$         |     1 |    52 |     0   (0)|          |
|* 26 |           INDEX UNIQUE SCAN         | I_TABSUBPART$_OBJ$  |     1 |       |     0   (0)|          |
|  27 |          TABLE ACCESS BY INDEX ROWID| INDSUBPART$         |     1 |    52 |     0   (0)|          |
|* 28 |           INDEX UNIQUE SCAN         | I_INDSUBPART_OBJ$   |     1 |       |     0   (0)|          |
|  29 |          TABLE ACCESS BY INDEX ROWID| LOBFRAG$            |     1 |    16 |     1   (0)| 00:00:01 |
|* 30 |           INDEX UNIQUE SCAN         | I_LOBFRAG$_FRAGOBJ$ |     1 |       |     0   (0)|          |
|* 31 |       TABLE ACCESS CLUSTER          | SEG$                |     1 |    14 |     1   (0)| 00:00:01 |
|* 32 |        INDEX UNIQUE SCAN            | I_FILE#_BLOCK#      |     1 |       |     0   (0)|          |
|* 33 |      INDEX UNIQUE SCAN              | I_FILE2             |     1 |     6 |     0   (0)|          |
|  34 |     TABLE ACCESS CLUSTER            | TS$                 |     1 |    10 |     1   (0)| 00:00:01 |
|* 35 |      INDEX UNIQUE SCAN              | I_TS#               |     1 |       |     0   (0)|          |
|  36 |    NESTED LOOPS                     |                     |     1 |    76 |     4   (0)| 00:00:01 |
|  37 |     NESTED LOOPS OUTER              |                     |     1 |    66 |     3   (0)| 00:00:01 |
|  38 |      NESTED LOOPS                   |                     |     1 |    51 |     2   (0)| 00:00:01 |
|  39 |       NESTED LOOPS                  |                     |     1 |    34 |     1   (0)| 00:00:01 |
|* 40 |        TABLE ACCESS BY INDEX ROWID  | UNDO$               |     1 |    28 |     1   (0)| 00:00:01 |
|* 41 |         INDEX UNIQUE SCAN           | I_UNDO1             |     1 |       |     0   (0)|          |
|* 42 |        INDEX UNIQUE SCAN            | I_FILE2             |     5 |    30 |     0   (0)|          |
|* 43 |       TABLE ACCESS CLUSTER          | SEG$                |     1 |    17 |     1   (0)| 00:00:01 |
|* 44 |        INDEX UNIQUE SCAN            | I_FILE#_BLOCK#      |     1 |       |     0   (0)|          |
|  45 |      TABLE ACCESS CLUSTER           | USER$               |     1 |    15 |     1   (0)| 00:00:01 |
|* 46 |       INDEX UNIQUE SCAN             | I_USER#             |     1 |       |     0   (0)|          |
|  47 |     TABLE ACCESS CLUSTER            | TS$                 |     1 |    10 |     1   (0)| 00:00:01 |
|* 48 |      INDEX UNIQUE SCAN              | I_TS#               |     1 |       |     0   (0)|          |
|  49 |    NESTED LOOPS                     |                     |     1 |    56 |    60   (2)| 00:00:01 |
|  50 |     NESTED LOOPS                    |                     |     1 |    46 |    59   (2)| 00:00:01 |
|  51 |      NESTED LOOPS OUTER             |                     |     1 |    37 |    58   (2)| 00:00:01 |
|* 52 |       TABLE ACCESS FULL             | SEG$                |     1 |    22 |    57   (2)| 00:00:01 |
|  53 |       TABLE ACCESS CLUSTER          | USER$               |     1 |    15 |     1   (0)| 00:00:01 |
|* 54 |        INDEX UNIQUE SCAN            | I_USER#             |     1 |       |     0   (0)|          |
|  55 |      TABLE ACCESS BY INDEX ROWID    | FILE$               |     1 |     9 |     1   (0)| 00:00:01 |
|* 56 |       INDEX UNIQUE SCAN             | I_FILE2             |     1 |       |     0   (0)|          |
|  57 |     TABLE ACCESS CLUSTER            | TS$                 |     1 |    10 |     1   (0)| 00:00:01 |
|* 58 |      INDEX UNIQUE SCAN              | I_TS#               |     1 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - filter("O"."DATAOBJ#"=:B1)
  10 - access("O"."OWNER#"="U"."USER#")
  11 - filter("O"."TYPE#"="SO"."OBJECT_TYPE_ID")
  13 - filter(BITAND("T"."PROPERTY",1024)=0)
  14 - access("T"."OBJ#"="O"."OBJ#")
  16 - access("TP"."OBJ#"="O"."OBJ#")
  18 - access("C"."OBJ#"="O"."OBJ#")
  19 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6
              OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9))
  20 - access("I"."OBJ#"="O"."OBJ#")
  22 - access("IP"."OBJ#"="O"."OBJ#")
  23 - filter((BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128))
  24 - access("L"."LOBJ#"="O"."OBJ#")
  26 - access("TSP"."OBJ#"="O"."OBJ#")
  28 - access("ISP"."OBJ#"="O"."OBJ#")
  30 - access("LF"."FRAGOBJ#"="O"."OBJ#")
  31 - filter("S"."TYPE#"="SO"."SEGMENT_TYPE_ID")
  32 - access("S"."TS#"="SO"."TS_NUMBER" AND "S"."FILE#"="SO"."HEADER_FILE" AND
              "S"."BLOCK#"="SO"."HEADER_BLOCK")
  33 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
  35 - access("S"."TS#"="TS"."TS#")
  40 - filter("UN"."STATUS$"<>1)
  41 - access("UN"."US#"=:B1)
  42 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#")
  43 - filter(("S"."TYPE#"=1 OR "S"."TYPE#"=10))
  44 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#")
  46 - access("S"."USER#"="U"."USER#")
  48 - access("S"."TS#"="TS"."TS#")
  52 - filter(("S"."HWMINCR"=:B1 AND "S"."TYPE#"<>6 AND "S"."TYPE#"<>5 AND "S"."TYPE#"<>8 AND
              "S"."TYPE#"<>10 AND "S"."TYPE#"<>1))
  54 - access("S"."USER#"="U"."USER#")
  56 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
  58 - access("S"."TS#"="TS"."TS#")
 
 
108 rows selected.
 
SYS@LAB1024> @create_sql_profile
Enter value for sql_id: fyrbzparfb4gb
Enter value for child_no: 0
Enter value for category: 
Enter value for force_matching: 
 
PL/SQL procedure successfully completed.
 
SYS@LAB1024> @sql_profile_hints
Enter value for profile_name: PROFILE_fyrbzparfb4gb
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SEL$42078B5C")
OUTLINE_LEAF(@"SEL$88DBC977")
OUTLINE_LEAF(@"SEL$FE7E982E")
OUTLINE_LEAF(@"SEL$5ED616EE")
OUTLINE_LEAF(@"SEL$AE4DCD4E")
OUTLINE_LEAF(@"SEL$267CE17A")
OUTLINE_LEAF(@"SET$BE4AEC69")
PUSH_PRED(@"SEL$2" "SO"@"SEL$2" 6)
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$12")
OUTLINE_LEAF(@"SEL$13")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SET$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$13")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$1" "SYS_DBA_SEGS"@"SEL$1")
FULL(@"SEL$13" "S"@"SEL$13")
INDEX(@"SEL$13" "U"@"SEL$13" "I_USER#")
INDEX_RS_ASC(@"SEL$13" "F"@"SEL$13" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$13" "TS"@"SEL$13" "I_TS#")
LEADING(@"SEL$13" "S"@"SEL$13" "U"@"SEL$13" "F"@"SEL$13" "TS"@"SEL$13")
USE_NL(@"SEL$13" "U"@"SEL$13")
USE_NL(@"SEL$13" "F"@"SEL$13")
USE_NL(@"SEL$13" "TS"@"SEL$13")
INDEX_RS_ASC(@"SEL$12" "UN"@"SEL$12" ("UNDO$"."US#"))
INDEX(@"SEL$12" "F"@"SEL$12" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$12" "S"@"SEL$12" "I_FILE#_BLOCK#")
INDEX(@"SEL$12" "U"@"SEL$12" "I_USER#")
INDEX(@"SEL$12" "TS"@"SEL$12" "I_TS#")
LEADING(@"SEL$12" "UN"@"SEL$12" "F"@"SEL$12" "S"@"SEL$12" "U"@"SEL$12" "TS"@"SEL$12")
USE_NL(@"SEL$12" "F"@"SEL$12")
USE_NL(@"SEL$12" "S"@"SEL$12")
USE_NL(@"SEL$12" "U"@"SEL$12")
USE_NL(@"SEL$12" "TS"@"SEL$12")
FULL(@"SEL$2" "O"@"SEL$2")
INDEX(@"SEL$2" "U"@"SEL$2" "I_USER#")
NO_ACCESS(@"SEL$2" "SO"@"SEL$2")
INDEX(@"SEL$2" "S"@"SEL$2" "I_FILE#_BLOCK#")
INDEX(@"SEL$2" "F"@"SEL$2" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$2" "TS"@"SEL$2" "I_TS#")
LEADING(@"SEL$2" "O"@"SEL$2" "U"@"SEL$2" "SO"@"SEL$2" "S"@"SEL$2" "F"@"SEL$2" "TS"@"SEL$2")
USE_NL(@"SEL$2" "U"@"SEL$2")
USE_NL(@"SEL$2" "SO"@"SEL$2")
USE_NL(@"SEL$2" "S"@"SEL$2")
USE_NL(@"SEL$2" "F"@"SEL$2")
USE_NL(@"SEL$2" "TS"@"SEL$2")
INDEX_RS_ASC(@"SEL$267CE17A" "LF"@"SEL$11" ("LOBFRAG$"."FRAGOBJ#"))
INDEX_RS_ASC(@"SEL$AE4DCD4E" "ISP"@"SEL$10" ("INDSUBPART$"."OBJ#"))
INDEX_RS_ASC(@"SEL$5ED616EE" "TSP"@"SEL$9" ("TABSUBPART$"."OBJ#"))
INDEX_RS_ASC(@"SEL$FE7E982E" "L"@"SEL$8" ("LOB$"."LOBJ#"))
INDEX_RS_ASC(@"SEL$88DBC977" "IP"@"SEL$7" ("INDPART$"."OBJ#"))
INDEX_RS_ASC(@"SEL$42078B5C" "I"@"SEL$6" ("IND$"."OBJ#"))
INDEX(@"SEL$9384AC1D" "C"@"SEL$5" "I_OBJ#")
INDEX_RS_ASC(@"SEL$8E13D68A" "TP"@"SEL$4" ("TABPART$"."OBJ#"))
INDEX(@"SEL$B01C6807" "T"@"SEL$3" "I_OBJ#")
 
74 rows selected.
 
SYS@LAB1024> @pif
Enter value for profile_name: PROFILE_fyrbzparfb4gb
Enter value for simplify_hint_flag: 
Enter value for make_modifications: 
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
Old: INDEX_RS_ASC(@"SEL$13" "F"@"SEL$13" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX_RS_ASC(@"SEL$13" "F"@"SEL$13" "I_FILE2")
 
Old: INDEX_RS_ASC(@"SEL$12" "UN"@"SEL$12" ("UNDO$"."US#"))
New: INDEX_RS_ASC(@"SEL$12" "UN"@"SEL$12" "I_UNDO1")
 
Old: INDEX(@"SEL$12" "F"@"SEL$12" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$12" "F"@"SEL$12" "I_FILE2")
 
Old: INDEX(@"SEL$2" "F"@"SEL$2" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$2" "F"@"SEL$2" "I_FILE2")
 
Old: INDEX_RS_ASC(@"SEL$267CE17A" "LF"@"SEL$11" ("LOBFRAG$"."FRAGOBJ#"))
New: INDEX_RS_ASC(@"SEL$267CE17A" "LF"@"SEL$11" "I_LOBFRAG$_FRAGOBJ$")
 
Old: INDEX_RS_ASC(@"SEL$AE4DCD4E" "ISP"@"SEL$10" ("INDSUBPART$"."OBJ#"))
New: INDEX_RS_ASC(@"SEL$AE4DCD4E" "ISP"@"SEL$10" "I_INDSUBPART_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$5ED616EE" "TSP"@"SEL$9" ("TABSUBPART$"."OBJ#"))
New: INDEX_RS_ASC(@"SEL$5ED616EE" "TSP"@"SEL$9" "I_TABSUBPART$_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$FE7E982E" "L"@"SEL$8" ("LOB$"."LOBJ#"))
New: INDEX_RS_ASC(@"SEL$FE7E982E" "L"@"SEL$8" "I_LOB2")
 
Old: INDEX_RS_ASC(@"SEL$88DBC977" "IP"@"SEL$7" ("INDPART$"."OBJ#"))
New: INDEX_RS_ASC(@"SEL$88DBC977" "IP"@"SEL$7" "I_INDPART_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$42078B5C" "I"@"SEL$6" ("IND$"."OBJ#"))
New: INDEX_RS_ASC(@"SEL$42078B5C" "I"@"SEL$6" "I_IND1")
 
Old: INDEX_RS_ASC(@"SEL$8E13D68A" "TP"@"SEL$4" ("TABPART$"."OBJ#"))
New: INDEX_RS_ASC(@"SEL$8E13D68A" "TP"@"SEL$4" "I_TABPART_OBJ$")
 
 
11 INDEX hint(s) found.
0 INDEX hint(s) modified.
 
SYS@LAB1024> @pif
Enter value for profile_name: PROFILE_fyrbzparfb4gb
Enter value for simplify_hint_flag: Y
Enter value for make_modifications: 
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
Old: INDEX_RS_ASC(@"SEL$13" "F"@"SEL$13" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$13" "F"@"SEL$13" "I_FILE2")
 
Old: INDEX_RS_ASC(@"SEL$12" "UN"@"SEL$12" ("UNDO$"."US#"))
New: INDEX(@"SEL$12" "UN"@"SEL$12" "I_UNDO1")
 
Old: INDEX(@"SEL$12" "F"@"SEL$12" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$12" "F"@"SEL$12" "I_FILE2")
 
Old: INDEX(@"SEL$2" "F"@"SEL$2" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$2" "F"@"SEL$2" "I_FILE2")
 
Old: INDEX_RS_ASC(@"SEL$267CE17A" "LF"@"SEL$11" ("LOBFRAG$"."FRAGOBJ#"))
New: INDEX(@"SEL$267CE17A" "LF"@"SEL$11" "I_LOBFRAG$_FRAGOBJ$")
 
Old: INDEX_RS_ASC(@"SEL$AE4DCD4E" "ISP"@"SEL$10" ("INDSUBPART$"."OBJ#"))
New: INDEX(@"SEL$AE4DCD4E" "ISP"@"SEL$10" "I_INDSUBPART_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$5ED616EE" "TSP"@"SEL$9" ("TABSUBPART$"."OBJ#"))
New: INDEX(@"SEL$5ED616EE" "TSP"@"SEL$9" "I_TABSUBPART$_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$FE7E982E" "L"@"SEL$8" ("LOB$"."LOBJ#"))
New: INDEX(@"SEL$FE7E982E" "L"@"SEL$8" "I_LOB2")
 
Old: INDEX_RS_ASC(@"SEL$88DBC977" "IP"@"SEL$7" ("INDPART$"."OBJ#"))
New: INDEX(@"SEL$88DBC977" "IP"@"SEL$7" "I_INDPART_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$42078B5C" "I"@"SEL$6" ("IND$"."OBJ#"))
New: INDEX(@"SEL$42078B5C" "I"@"SEL$6" "I_IND1")
 
Old: INDEX_RS_ASC(@"SEL$8E13D68A" "TP"@"SEL$4" ("TABPART$"."OBJ#"))
New: INDEX(@"SEL$8E13D68A" "TP"@"SEL$4" "I_TABPART_OBJ$")
 
 
11 INDEX hint(s) found.
0 INDEX hint(s) modified.
 
SYS@LAB1024> @pif
Enter value for profile_name: PROFILE_fyrbzparfb4gb
Enter value for simplify_hint_flag: Y
Enter value for make_modifications: Y
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
Old: INDEX_RS_ASC(@"SEL$13" "F"@"SEL$13" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$13" "F"@"SEL$13" "I_FILE2")
 
Old: INDEX_RS_ASC(@"SEL$12" "UN"@"SEL$12" ("UNDO$"."US#"))
New: INDEX(@"SEL$12" "UN"@"SEL$12" "I_UNDO1")
 
Old: INDEX(@"SEL$12" "F"@"SEL$12" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$12" "F"@"SEL$12" "I_FILE2")
 
Old: INDEX(@"SEL$2" "F"@"SEL$2" ("FILE$"."TS#" "FILE$"."RELFILE#"))
New: INDEX(@"SEL$2" "F"@"SEL$2" "I_FILE2")
 
Old: INDEX_RS_ASC(@"SEL$267CE17A" "LF"@"SEL$11" ("LOBFRAG$"."FRAGOBJ#"))
New: INDEX(@"SEL$267CE17A" "LF"@"SEL$11" "I_LOBFRAG$_FRAGOBJ$")
 
Old: INDEX_RS_ASC(@"SEL$AE4DCD4E" "ISP"@"SEL$10" ("INDSUBPART$"."OBJ#"))
New: INDEX(@"SEL$AE4DCD4E" "ISP"@"SEL$10" "I_INDSUBPART_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$5ED616EE" "TSP"@"SEL$9" ("TABSUBPART$"."OBJ#"))
New: INDEX(@"SEL$5ED616EE" "TSP"@"SEL$9" "I_TABSUBPART$_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$FE7E982E" "L"@"SEL$8" ("LOB$"."LOBJ#"))
New: INDEX(@"SEL$FE7E982E" "L"@"SEL$8" "I_LOB2")
 
Old: INDEX_RS_ASC(@"SEL$88DBC977" "IP"@"SEL$7" ("INDPART$"."OBJ#"))
New: INDEX(@"SEL$88DBC977" "IP"@"SEL$7" "I_INDPART_OBJ$")
 
Old: INDEX_RS_ASC(@"SEL$42078B5C" "I"@"SEL$6" ("IND$"."OBJ#"))
New: INDEX(@"SEL$42078B5C" "I"@"SEL$6" "I_IND1")
 
Old: INDEX_RS_ASC(@"SEL$8E13D68A" "TP"@"SEL$4" ("TABPART$"."OBJ#"))
New: INDEX(@"SEL$8E13D68A" "TP"@"SEL$4" "I_TABPART_OBJ$")
 
 
11 INDEX hint(s) found.
11 INDEX hint(s) modified.
 
SYS@LAB1024> @pif
Enter value for profile_name: PROFILE_fyrbzparfb4gb
Enter value for simplify_hint_flag: 
Enter value for make_modifications: 
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
0 INDEX hint(s) found.
0 INDEX hint(s) modified.
 
SYS@LAB1024> @sql_profile_hints
Enter value for profile_name: PROFILE_fyrbzparfb4gb
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SEL$42078B5C")
OUTLINE_LEAF(@"SEL$88DBC977")
OUTLINE_LEAF(@"SEL$FE7E982E")
OUTLINE_LEAF(@"SEL$5ED616EE")
OUTLINE_LEAF(@"SEL$AE4DCD4E")
OUTLINE_LEAF(@"SEL$267CE17A")
OUTLINE_LEAF(@"SET$BE4AEC69")
PUSH_PRED(@"SEL$2" "SO"@"SEL$2" 6)
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$12")
OUTLINE_LEAF(@"SEL$13")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SET$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$13")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$1" "SYS_DBA_SEGS"@"SEL$1")
FULL(@"SEL$13" "S"@"SEL$13")
INDEX(@"SEL$13" "U"@"SEL$13" "I_USER#")
INDEX(@"SEL$13" "F"@"SEL$13" "I_FILE2")
INDEX(@"SEL$13" "TS"@"SEL$13" "I_TS#")
LEADING(@"SEL$13" "S"@"SEL$13" "U"@"SEL$13" "F"@"SEL$13" "TS"@"SEL$13")
USE_NL(@"SEL$13" "U"@"SEL$13")
USE_NL(@"SEL$13" "F"@"SEL$13")
USE_NL(@"SEL$13" "TS"@"SEL$13")
INDEX(@"SEL$12" "UN"@"SEL$12" "I_UNDO1")
INDEX(@"SEL$12" "F"@"SEL$12" "I_FILE2")
INDEX(@"SEL$12" "S"@"SEL$12" "I_FILE#_BLOCK#")
INDEX(@"SEL$12" "U"@"SEL$12" "I_USER#")
INDEX(@"SEL$12" "TS"@"SEL$12" "I_TS#")
LEADING(@"SEL$12" "UN"@"SEL$12" "F"@"SEL$12" "S"@"SEL$12" "U"@"SEL$12" "TS"@"SEL$12")
USE_NL(@"SEL$12" "F"@"SEL$12")
USE_NL(@"SEL$12" "S"@"SEL$12")
USE_NL(@"SEL$12" "U"@"SEL$12")
USE_NL(@"SEL$12" "TS"@"SEL$12")
FULL(@"SEL$2" "O"@"SEL$2")
INDEX(@"SEL$2" "U"@"SEL$2" "I_USER#")
NO_ACCESS(@"SEL$2" "SO"@"SEL$2")
INDEX(@"SEL$2" "S"@"SEL$2" "I_FILE#_BLOCK#")
INDEX(@"SEL$2" "F"@"SEL$2" "I_FILE2")
INDEX(@"SEL$2" "TS"@"SEL$2" "I_TS#")
LEADING(@"SEL$2" "O"@"SEL$2" "U"@"SEL$2" "SO"@"SEL$2" "S"@"SEL$2" "F"@"SEL$2" "TS"@"SEL$2")
USE_NL(@"SEL$2" "U"@"SEL$2")
USE_NL(@"SEL$2" "SO"@"SEL$2")
USE_NL(@"SEL$2" "S"@"SEL$2")
USE_NL(@"SEL$2" "F"@"SEL$2")
USE_NL(@"SEL$2" "TS"@"SEL$2")
INDEX(@"SEL$267CE17A" "LF"@"SEL$11" "I_LOBFRAG$_FRAGOBJ$")
INDEX(@"SEL$AE4DCD4E" "ISP"@"SEL$10" "I_INDSUBPART_OBJ$")
INDEX(@"SEL$5ED616EE" "TSP"@"SEL$9" "I_TABSUBPART$_OBJ$")
INDEX(@"SEL$FE7E982E" "L"@"SEL$8" "I_LOB2")
INDEX(@"SEL$88DBC977" "IP"@"SEL$7" "I_INDPART_OBJ$")
INDEX(@"SEL$42078B5C" "I"@"SEL$6" "I_IND1")
INDEX(@"SEL$9384AC1D" "C"@"SEL$5" "I_OBJ#")
INDEX(@"SEL$8E13D68A" "TP"@"SEL$4" "I_TABPART_OBJ$")
INDEX(@"SEL$B01C6807" "T"@"SEL$3" "I_OBJ#")

A few closing comments on this way too long post.

1. As you can see, not all INDEX hints use the new format – so pif.sql only looks for the new format.
2. The simplify_hints flag changes hints like INDEX_RS_ASC into the more simple INDEX (default is “N”).
3. The make_modifications flag tells pif to implement the changes if set to “Y” (default is “N”).

Let me know what you think.

11 Comments

  1. [...] Osborne gives a lesson on fixing bad index hints in SQL Profiles (automatically). He says, “With 10g and 11g, it appears the goal [or Outlines] has swung away from the [...]

  2. Kerry:

    Regarding this comment:

    INDEX(TABLE_NAME (TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …))
    Translation: If possible, use any available index on any of these columns.

    I think the translation should be more like:

    “Use the index on this set of columns in this order but if the required index doesn’t exist then use the cheapest index that starts with this set of columns in this order.”

    Personally I think it’s a better way of hinting an index than the old “index(alias index_name)” method given that (a) people sometimes rename indexes to conform to naming conventions and (b) people sometimes drop one index because it starts the same way as another. In both cases, the new mechanism is likely to reduce the risk of damage.

    The “index_rs(_asc/_desc)” hints were introduced, by the way, because an index() hint could allow the optimizer to choose between a range scan and a full scan – and that meant that a hinted query could still change its execution path dramatically unless all the other hints had been carefully set up to avoid the error – so I wouldn’t be too keen to change a index range scan hint to a simple index hint.

    Regards
    Jonathan Lewis

  3. osborne says:

    Jonathan,

    Thanks for the comments.

    I totally agree that your paraphrase of what the index hint is saying is more accurate than mine. (too bad they don’t document hints better, and that they don’t have the option to raise a warning when they are ignored).

    I also agree that the INDEX_RS(_ASC/DSC) hints are more specific and therefore a better choice when trying to force a specific plan. Unfortunately, there are some problems (i.e. bug) that cause Oracle to create an invalid hint. In the particular case I showed (2nd batch of code), the other_xml field contains the hint

    INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1″ (“SKEW”.”COL2″ “SKEW”.”COL1″))

    – even though the actual plan does an

    INDEX FULL SCAN

    Therefore, creating an Outline on this statement changes the plan. In this case, changing the hint to the more generic INDEX(xxx), allows the optimizer to use an INDEX FULL SCAN. By the way, 11.2 works around this bug by doing exactly that, changing the INDEX_RS_ASC to INDEX. So I am agreeing with you on this point as well, more specific is certainly better (unless of course it doesn’t work, like in this corner case).

    I’m not much of a fan of the new index format though. I understand your point about changing indexes and in general it makes sense. With respect to Outlines/Profiles/Baselines though it seems too flexible at times. By that I mean that occasionally an Outline or Profile will not keep a plan from changing – and sometimes it’s due to this non-specific Index Hint format. I think one of the reasons is that the Outline Hint created by Oracle (again pulled from the other_xml field) specifies every column contained in the index. That is to say that if a plan uses an index that contains 10 columns, all ten will be listed in the hint, regardless of the fact that only a couple of them may actually be used in the statement. You can see this in the example in the post as well. The SKEW_COL2_COL1 index has COL2 and COL1. So the hints specifies both columns, even though the SQL only references COL1. I guess it just seems more straight forward and less error prone to actually specify the index name (although I acknowledge that it may go bad at some point in the future if someone changes the indexes).

    Thanks again for your comments.

    Note: I’ll have to do a little verification (and refreshing my memory) before I respond to your comments on
    Why Isn’t Oracle Using My Outline / Profile / Baseline.

    Kerry

  4. [...] 14-How to change index hints with new index hint format automatically in sql profiles? Kerry Osborne-Fixing Bad Index Hints in SQL Profiles (automatically) [...]

  5. Michael Fontana says:

    I like the suggestion of having Oracle issue a warning when a hint is syntactically incorrect and will be ignored. A more powerful (and probably quite difficult) behavioral change would be to have Oracle indicate when it uses a different path than what was specified (or implied????) by the hint. This has always been such a vague and esoteric area. I wonder where an enhancement request with Oracle support would end up?

  6. [...] Jonathan Lewis @ 7:19 pm UTC Jan 12,2010 If you have looked at SQL Profiles (see for example Kerry Osborne’s blog) then you may have come across the force_match option for enabling or importing a SQL profile. I [...]

  7. Tony Hasler says:

    Kerry,

    I know this is an old blog but it is still of interest. There is a typo in your description of the new index format that Jonathan has repeated in his reply. Please feel free to correct it (I suggest both places..I doubt JL will object) and delete this comment.

    The new format is not:

    INDEX(TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …)

    but

    INDEX(TABLE_NAME (TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …))

    Note the TABLE_NAME at the start and the extra brackets around the columns specifications.

    • osborne says:

      Hi Tony,

      Thanks for the comment. Hint syntax is a bit confusing (and not really documented anywhere that I’m aware of – at least with a formal syntax diagram of list of arguments). Jonathan and I were using the less formal hint syntax which leaves out the 1st and 2nd arguments (Query_Block and Alias). This is commonly done when adding hints directly to SQL statements. So the common version of the new INDEX hint syntax would look like this:

      /*+ INDEX(TABLE_NAME.COLUMN_NAME TABLE_NAME.COLUMN_NAME) */

      If in fact you use this format it works fine but it will be applied within the scope of the current QB. Here’s an example:

      
      SYS@dbm1> select /*+ index(skew.col1 skew.col2) */ count(col1) from kso.skew t  where col1 = 1 and col2 = 'asddsadasd';
      
       COUNT(COL1)
      ------------
           3199977
      
      1 row selected.
      
      Elapsed: 00:00:07.08
      SYS@dbm1> @x
      
      PLAN_TABLE_OUTPUT
      -----------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID  3v5nxcb4nnwga, child number 0
      -------------------------------------
      select /*+ index(skew.col1 skew.col2) */ count(col1) from kso.skew t
      where col1 = 1 and col2 = 'asddsadasd'
      
      Plan hash value: 4272108972
      
      -----------------------------------------------------
      | Id  | Operation         | Name           | E-Rows |
      -----------------------------------------------------
      |   0 | SELECT STATEMENT  |                |        |
      |   1 |  SORT AGGREGATE   |                |      1 |
      |*  2 |   INDEX RANGE SCAN| SKEW_COL1_COL2 |      1 |
      -----------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("COL1"=1 AND "COL2"='asddsadasd')
      
      Note
      -----
         - Warning: basic plan statistics not available. These are only collected when:
             * hint 'gather_plan_statistics' is used for the statement or
             * parameter 'statistics_level' is set to 'ALL', at session or system level
      
      
      26 rows selected.
      
      Elapsed: 00:00:00.02
      SYS@dbm1> @sql_hints
      Enter value for sql_id: 3v5nxcb4nnwga
      Enter value for child_no: 0
      
      OUTLINE_HINTS
      -----------------------------------------------------------------------------------------------------------------------------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("SKEW"."COL1" "SKEW"."COL2"))
      
      7 rows selected.
      
      Elapsed: 00:00:01.05
      
      

      So as you can see, the fully specified hint ends up looking like INDEX(QB_NAME ALIAS (TABLE_NAME.COLUMN_NAME TABLE_NAME.COLUMN_NAME)) Your syntax also works as long as the hint is defined in the Query Block where you want it applied and there is not an alias specified for the table. The full syntax is needed for many hints to work when using SQL Profiles by the way. Thanks again for pointing out this out as it is confusing and needed to be spelled out a little more clearly.

      Kerry

  8. [...] seems that we are not the first to experiment with this technique. See for example this blog by Kerry Osborne published a while ago but I think these posts have slightly different [...]

  9. Tony Hasler says:

    Kerry,

    Hint syntax is descrribed in the SQL reference manual. For example, the syntax for specifying an index hint is here:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#BABEFDFC

    As you can see, the query block specification is optional but the table specification is not. I have been unable to get your syntax to work on 10g or 11g. Are you sure that the use of the index in your example isn’t purely coincidental?

  10. osborne says:

    Damn it, I guess I’m going to have start reading the documentation. And as you predicted the index did not get picked up because of the hint. I have corrected the syntax in the original post.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/img/index_hint.gif

    Kerry

Leave a Reply