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.

Leave a Reply