How to Attach a SQL Profile to a Different Statement – Take 2

I posted on this topic a while back (How to Attach a SQL Profile to a Different Statement – Take 1), but wasn’t really happy with my solution. So here’s another shot at it. The basic idea is to be able to create a profile on one statement using hints or whatever other tricks you can come up with, and then attach the profile to a production statement on which you cannot modify the code. The basic steps of this technique are as follows:

The main reason I became dissatisfied with my previous approach was that it’s often necessary to fix one or more of the hints (see this post for the most common reason: Why Isn’t Oracle Using My Outline / Profile / Baseline?). The preceding steps allow the Profile to be tweaked before attaching it to the target statement. My previous approach created the profile and moved it all in one step. So there was no chance to modify the Profile before it was attached to the production statement. Anyways, here’s an example (note the examples are on a 10.2.0.4 database, but all the SQL should work on 11g as well):

 
> !sql
sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 12 15:31:54 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
 
SQL> -- first let's set up a situation where we get a bad plan by modifying the column stats
SQL>
SQL> @col_stats
Enter value for owner: KSO
Enter value for table_name: SKEW
Enter value for column_name: 
 
COLUMN_NAME               DATA_TYPE  AVG_COL_LEN     DENSITY          NDV HISTOGRAM       BUCKETS SAMPLE_SIZE LAST_ANAL LOW_VALUE       HIGH_VALUE
------------------------- ---------- ----------- ----------- ------------ --------------- ------- ----------- --------- --------------- ---------------
PK_COL                    NUMBER               6  .000000031   32,023,029 NONE                  1             28-JUL-09 4507            31994059
COL1                      NUMBER               5  .000001647      896,772 HEIGHT BALANCED     254             28-JUL-09 1               1000000
COL2                      VARCHAR2            11 ###########            1 NONE                  1             28-JUL-09 asddsadasd      asddsadasd
COL3                      DATE                 8  .000001001      998,523 NONE                  1             28-JUL-09 21-oct-2005     26-jul-2006
COL4                      VARCHAR2             2  .000000016            3 FREQUENCY             3    32000003 22-SEP-09 F               Y
 
SQL> @drop_histogram
Enter value for owner: KSO
Enter value for table_name: SKEW
Enter value for column_name: COL1
 
PL/SQL procedure successfully completed.
 
SQL> @col_stats
Enter value for owner: KSO
Enter value for table_name: SKEW
Enter value for column_name: 
 
COLUMN_NAME               DATA_TYPE  AVG_COL_LEN     DENSITY          NDV HISTOGRAM       BUCKETS SAMPLE_SIZE LAST_ANAL LOW_VALUE       HIGH_VALUE
------------------------- ---------- ----------- ----------- ------------ --------------- ------- ----------- --------- --------------- ---------------
PK_COL                    NUMBER               6  .000000031   32,023,029 NONE                  1             28-JUL-09 4507            31994059
COL1                      NUMBER               5  .000001115      896,772 NONE                  1             12-OCT-09 1               1000000
COL2                      VARCHAR2            11 ###########            1 NONE                  1             28-JUL-09 asddsadasd      asddsadasd
COL3                      DATE                 8  .000001001      998,523 NONE                  1             28-JUL-09 21-oct-2005     26-jul-2006
COL4                      VARCHAR2             2  .000000016            3 FREQUENCY             3    32000003 22-SEP-09 F               Y
 
SQL> @set_col_stats
Enter value for owner: KSO
Enter value for table_name: SKEW
Enter value for col_name: COL1
Enter value for ndv: 2
Enter value for density: .5
Enter value for nullcnt: 0
 
PL/SQL procedure successfully completed.
 
SQL> @col_stats
Enter value for owner: KSO
Enter value for table_name: SKEW
Enter value for column_name: 
 
COLUMN_NAME               DATA_TYPE  AVG_COL_LEN     DENSITY          NDV HISTOGRAM       BUCKETS SAMPLE_SIZE LAST_ANAL LOW_VALUE       HIGH_VALUE
------------------------- ---------- ----------- ----------- ------------ --------------- ------- ----------- --------- --------------- ---------------
PK_COL                    NUMBER               6  .000000031   32,023,029 NONE                  1             28-JUL-09 4507            31994059
COL1                      NUMBER               5  .500000000            2 NONE                  1             12-OCT-09 1               1000000
COL2                      VARCHAR2            11 ###########            1 NONE                  1             28-JUL-09 asddsadasd      asddsadasd
COL3                      DATE                 8  .000001001      998,523 NONE                  1             28-JUL-09 21-oct-2005     26-jul-2006
COL4                      VARCHAR2             2  .000000016            3 FREQUENCY             3    32000003 22-SEP-09 F               Y
 
SQL> -- so now we've got a table with a pretty selective column 
SQL> -- but the optimizer thinks has only 2 distinct values
SQL> -- let's try a query that should use an index (but won't because of the bad stats)
SQL>
SQL> @flush_pool
 
System altered.
 
SQL> set echo on
SQL> @avgskewi
SQL> select avg(pk_col) from kso.skew
  2  where col1 = 136133
  3  /
select avg(pk_col) from kso.skew
                            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
 
 
SQL> set echo off
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0  568322376          1          9.35      144,033 select avg(pk_col) from kso.skew where col1 = 136133
 
SQL> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 36802 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    16M|   167M| 36802   (4)| 00:07:22 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
 
19 rows selected.
 
SQL> -- so the optimizer is thoroughly confused and has picked a full table scan
SQL> -- let's create a new statement and make it use the index we want
SQL> 
SQL> @build_bind_vars
Enter SQL ID ==> 84q0zxfzn5u6s
Enter Child Number ==> 0
 
...
 
SQL> set echo on
SQL> @84q0zxfzn5u6s
SQL> select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ avg(pk_col) from kso.skew a
  2  where col1 = 136133;
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SQL> 
SQL> set echo off
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
4tzk4qvurczum      0 3723858078          1           .00           35 select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ a
                                                                      vg(pk_col) from kso.skew a where col1 = 136133
 
84q0zxfzn5u6s      0  568322376          1          9.35      144,033 select avg(pk_col) from kso.skew where col1 = 136133
 
2 records selected.
 
SQL> @dplan
Enter value for sql_id: 4tzk4qvurczum
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4tzk4qvurczum, child number 0
-------------------------------------
select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ avg(pk_col) from
kso.skew a where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    14M(100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    16M|   167M|    14M  (1)| 48:14:47 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    16M|       | 35797   (2)| 00:07:10 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
 
21 rows selected.
 
SQL> -- so of course with the hint it uses the index
SQL>  -- now let's create a profile on this new statement
SQL> 
SQL> @create_sql_profile
Enter value for sql_id: 4tzk4qvurczum
Enter value for child_no: 0
Enter value for category: 
Enter value for force_matching: 
 
PL/SQL procedure successfully completed.
 
SQL> @sql_profiles
Enter value for sql_text: %84q0zxfzn5u6s%
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_4tzk4qvurczum          DEFAULT         ENABLED  select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ avg(pk_col) NO
 
1 row selected.
 
SQL> -- let's see what the hints look like
SQL> 
SQL> @sql_profile_hints
Enter value for profile_name: %4tzk4qvurczum       
 
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"."COL1"))
 
5 rows selected.
 
SQL> -- I'd prefer to specify the actual index name, so let's change the index hint
SQL> 
SQL> @fix_sql_profile_hint
Enter value for profile_name: PROFILE_4tzk4qvurczum
Enter value for bad_hint: INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL1"))
Enter value for good_hint: INDEX (@"SEL$1" "A"@"SEL$1" "SKEW_COL1")
version: 10
 
PL/SQL procedure successfully completed.
 
SQL> -- let's just check to make sure it took
SQL> 
SQL> @sql_profile_hints
Enter value for profile_name: PROFILE_4tzk4qvurczum
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX (@"SEL$1" "A"@"SEL$1" "SKEW_COL1")
 
5 rows selected.
 
SQL> -- looks good, let's execute our statement and make sure it still does what we expect
SQL> 
SQL> set echo on
SQL> @84q0zxfzn5u6s
SQL> select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ avg(pk_col) from kso.skew a
  2  where col1 = 136133;
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SQL> set echo off
SQL> @find_sql
Enter value for sql_text: select /* test 84q0zxfzn5u6s%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
4tzk4qvurczum      0 3723858078          1           .00           35 select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ a
                                                                      vg(pk_col) from kso.skew a where col1 = 136133
 
4tzk4qvurczum      1 3723858078          1           .00           35 select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ a
                                                                      vg(pk_col) from kso.skew a where col1 = 136133
 
 
2 rows selected.
 
SQL> -- new child, but same plan
SQL> 
SQL> @dplan
Enter value for sql_id: 4tzk4qvurczum
Enter value for child_no: 1
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4tzk4qvurczum, child number 1
-------------------------------------
select /* test 84q0zxfzn5u6s */ /*+ index (a skew_col1) */ avg(pk_col) from
kso.skew a where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    14M(100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    16M|   167M|    14M  (1)| 48:14:47 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    16M|       | 35797   (2)| 00:07:10 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
Note
-----
   - SQL profile "PROFILE_4tzk4qvurczum" used for this statement
 
 
25 rows selected.
 
SQL> -- now using the profile - good
SQL> -- now the last step, let's move the profile to our original, unmodified statement
SQL> 
SQL> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0  568322376          1          9.35      144,033 select avg(pk_col) from kso.skew where col1 = 136133
 
1 row selected.
 
SQL> @move_sql_profile
Enter value for profile_name: PROFILE_4tzk4qvurczum
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
 
PL/SQL procedure successfully completed.
 
SQL> -- and test
SQL>
SQL> set echo on
SQL> @avgskewi
SQL> select avg(pk_col) from kso.skew
  2  where col1 = 136133;
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
SQL> @find_sql
Enter value for sql_text:
Enter value for sql_id: 84q0zxfzn5u6s
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0  568322376          1          9.35      144,033 select avg(pk_col) from kso.skew where col1 = 136133
84q0zxfzn5u6s      1  568322376          1         10.68      162,298 select avg(pk_col) from kso.skew where col1 = 136133
 
SQL> -- hmmmm, it's got a new child, but is still using the same plan (568322376 - which is the full table scan)
SQL>
SQL> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 1
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 36802 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    16M|   167M| 36802   (4)| 00:07:22 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1"=136133)
 
Note
-----
   - SQL profile "PROFILE_84q0zxfzn5u6s_moved" used for this statement
 
 
23 rows selected.
 
SQL> -- so it's using the Profile, but didn't change the plan
SQL> -- let's see what the hints are
SQL>
SQL> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_moved
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX (@"SEL$1" "A"@"SEL$1" "SKEW_COL1")
 
5 rows selected.

This is a good spot to take a break and have a little discussion about why things go wrong with Hints, (and therefore with Outlines, SQL Profiles, and SQL Baselines). The thing that is the most frustrating about Hints is that any Hint that isn’t valid is quietly ignored by the optimizer. No muss, no fuss, no error or warning message. It’s kind of like asking a kid to take out the garbage. A good kid will say yes and then take out the garbage. A not so good kid will throw a fit and refuse to take out the garbage (then you’ll have to take it out yourself). But by far the worst kid is the one that says yes and doesn’t complain, but then just doesn’t do it. I say this is worse because then you are left with a stinky mess in your garage for several more days until the next trash pickup occurs. If you had only known, you could have taken it out yourself. I contend that the optimizer is like that worst kid because it happily agrees to do what you say, but if for some reason it can’t, it just goes about its business without bothering to tell you, potentially leaving you with a stinky mess to clean up next week. But I digress!

This is exactly what happened in our example above. Have you spotted the problem? Anything that causes a Hint to be invalid will cause that Hint to be silently ignored. As you might expect, the problem is with the Hint we modified, but not because we modified it. See the “A”@”SEL$1″. That’s where the problem lies. It’s telling the optimizer to apply the Index Hint to object “A” in query block “SEL$1″. Unfortunately, this query doesn’t have an object “A”. That was an alias I added to the testing query that I used to generate the SQL Profile.

So let this be a lesson to you, er.. me, not to mess around with aliases when doing this sort of thing. Let it also be a lesson to test your handy work to make sure it does what you think it should have done.

Let’s fix it. There are a couple of ways. I could go back and repeat the whole process without using an alias in my testing statement (probably the least error prone approach). Or if it is really simple like this one, I could just change the Hint.

SQL> -- let's see what the hints are for the original statement
SQL> -- (note the "SKEW"@"SQL$1")
SQL>
SQL> @sql_hints
Enter value for sql_id: 84q0zxfzn5u6s
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" "SKEW"@"SEL$1")
 
5 rows selected.
 
SQL> -- let's see what the profile hints look like again
SQL> -- (note the "A"@"SEL$1")
SQL>
SQL> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_moved
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX (@"SEL$1" "A"@"SEL$1" "SKEW_COL1")
 
5 rows selected.
 
SQL> -- but we don't have an alias "A" in our original statement
SQL> -- so if we change the "A" to "SKEW" it should work
SQL>
SQL> @fix_sql_profile_hint
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_moved
Enter value for bad_hint: INDEX (@"SEL$1" "A"@"SEL$1" "SKEW_COL1")
Enter value for good_hint: INDEX (@"SEL$1" "SKEW"@"SEL$1" "SKEW_COL1")
version: 10
 
PL/SQL procedure successfully completed.
 
SQL> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_moved
 
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX (@"SEL$1" "SKEW"@"SEL$1" "SKEW_COL1")
 
SQL> -- looks correct now, let's try it again
SQL>
SQL> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
SQL> /
 
AVG(PK_COL)
-----------
   15636133
 
SQL> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          2           .00           36 select avg(pk_col) from kso.skew where col1 = 136133
 
SQL> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    14M(100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    16M|   167M|    14M  (1)| 48:14:47 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    16M|       | 35797   (2)| 00:07:10 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
Note
-----
   - SQL profile "PROFILE_84q0zxfzn5u6s_moved" used for this statement
 
 
24 rows selected.
 
SQL> -- yep, all fixed

One other thing you might want to be aware of. If you find yourself having a hard time telling if a hint is working or not, you can “Wolfgang” it. (that’s doing a 10053 trace – see the classic paper on the subject here: A Look Under the Hood of the CBO ). The trace file will contain a section at the bottom that shows the hints that were applied (used=1) along with whether there were any syntactical errors (err=1).

SQL> -- clear statement from shared pool (10053 trace only works when it's hard parsed)
SQL> @flush_pool
 
System altered.
 
SQL> set echo on
SQL> @debug_on
SQL> alter session set events '10053 trace name context forever, level 1';
 
Session altered.
 
SQL> set echo off
SQL> @avgskewi
 
AVG(PK_COL)
-----------
   15636133
 
SQL> exit
 
> !sql
sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 12 15:31:54 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
 
SQL> @dest
 
NAME                           VALUE
------------------------------ --------------------------------------------------------------------
background_dump_dest           /u01/app/admin/LAB1024/bdump
core_dump_dest                 /u01/app/admin/LAB1024/cdump
user_dump_dest                 /u01/app/admin/LAB1024/udump
 
SQL> !ls -altr /u01/app/admin/LAB1024/udump
total 15104
drwxr-x---  9 oracle dba    4096 Jun 19 14:16 ..
-rw-r-----  1 oracle dba     617 Jun 19 14:16 lab1024_ora_8832.trc
 
...
 
-rw-r-----  1 oracle dba   37799 Oct 12 15:25 lab1024_ora_22856.trc
drwxr-x---  2 oracle dba    4096 Oct 12 15:25 .
 
SQL> -- the newest one should be it
SQL>
SQL> !vi /u01/app/admin/LAB1024/udump/lab1024_ora_22856.trc
 
...
 
Dumping Hints
=============
  atom_hint=(@=0xf6265764 err=0 resol=1 used=0 token=1086 org=2 lvl=2 txt=OUTLINE_LEAF ())
  atom_hint=(@=0xf6265558 err=0 resol=1 used=0 token=83 org=2 lvl=3 txt=INDEX ("SKEW" "SKEW_COL1") )
  atom_hint=(@=0x78eda64c err=0 resol=0 used=1 token=976 org=2 lvl=1 txt=OPTIMIZER_FEATURES_ENABLE ())
  atom_hint=(@=0x78eda6bc err=0 resol=0 used=1 token=1007 org=2 lvl=1 txt=IGNORE_OPTIM_EMBEDDED_HINTS ())
  atom_hint=(@=0xf62654fc err=0 resol=1 used=1 token=1086 org=2 lvl=2 txt=OUTLINE_LEAF ())
  atom_hint=(@=0xf62658d0 err=0 resol=1 used=0 token=454 org=2 lvl=2 txt=ALL_ROWS )
  atom_hint=(@=0xf62653a0 err=0 resol=1 used=1 token=83 org=2 lvl=3 txt=INDEX ("SKEW" "SKEW_COL1") )
 
...
 
Here's the trace file dump for the hints part with original one before we fixed the alias.
 
Dumping Hints
=============
  atom_hint=(@=0xf6265b94 err=0 resol=1 used=0 token=1086 org=2 lvl=2 txt=OUTLINE_LEAF ())
  atom_hint=(@=0xf6265988 err=0 resol=0 used=0 token=83 org=2 lvl=3 txt=INDEX ("A" "SKEW_COL1") )
  atom_hint=(@=0x78ece548 err=0 resol=0 used=1 token=976 org=2 lvl=1 txt=OPTIMIZER_FEATURES_ENABLE ())
  atom_hint=(@=0x78ece5b8 err=0 resol=0 used=1 token=1007 org=2 lvl=1 txt=IGNORE_OPTIM_EMBEDDED_HINTS ())
  atom_hint=(@=0xf626592c err=0 resol=1 used=1 token=1086 org=2 lvl=2 txt=OUTLINE_LEAF ())
  atom_hint=(@=0xf6265d00 err=0 resol=1 used=0 token=454 org=2 lvl=2 txt=ALL_ROWS )

Any questions or comments? Please feel free to add them.

Leave a Reply