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.

27 Comments

  1. Kerry,

    nice post!

    Regarding your last comment about the “Dumping Hints” section of the 10053 optimizer trace file: Do you see any ERR=1 messages? Although the index hint is obviously invalid (wrong alias), there is no explicit error reported. At least the USED=0 might be an indicator, but I would definitely prefer to see at least at that detail level when a hint is invalid/malformed.

    Randolf

  2. osborne says:

    Thanks.

    No promises on the trace file output. It’s flakey at best (or I just don’t understand it well enough yet). But it’s another place you can look for some clues if you’re trying to workout whether a hint is being used or not. I have seen the err flag set where the statement was told to use an index on a column that wasn’t specified in the where clause. But as I said, it’s really not completely clear to me how those flags are used.

    Kerry

  3. Fikret says:

    hello i want to ask one question from you my database is 11.1.0.7 but move_sql_profile.sql doesn’t work in 11g can you reproduse this query again?

  4. osborne says:

    Sorry about that Fikret. The version I originally put up only worked with 10g. That was an oversight on my part (I forgot that 11g modified the layout of the underlying SQL Profile structures). I have modified the move_sql_profiles.sql script to work with either version. Please let me know if you have any further issues.

    Kerry

  5. Coskan says:

    Hi Kerry,

    Very nice post thank you very much for sharing.
    Couple of things for the scripts
    1- move_sql_profile
    select
    sql_fulltext
    into
    cl_sql_text
    from
    v$sqlarea
    where
    sql_id = ‘&&sql_id’

    this can return more than 1 when you have multiple children so rownum should be used to avoid the fetch error.

    2- Maybe I am missing some step but on both 11G and 10G I am having “ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement” when I run move_sql_profile or move_sql_profile11(which I created as the same way of sql_profile11 script you use)

  6. osborne says:

    Thanks for the comments Coskan.

    On the first suggestion, I think v$sqlarea should only return one row per sql_id. I don’t recall ever seeing more than one row for a statement in that view. It’s possible I have a typo somewhere using v$sql which has a row for every child. Please send the error you got and I’ll do a little more looking.

    On the second one, that error should only show up if there is an existing SQL Profile already on that statement. Let me know if that is not the case and we’ll figure out what’s going on.

    Also, I just this morning modified the move_sql_profile.sql script to allow it to work for 10g or 11g, so you might want to grab that new version instead of having separate versions for 10g and 11g.

    Thanks again for the comments.

    Kerry

  7. Coskan says:

    It was my fault that I was using the same sqlid for the moved one everything works fine :)

  8. osborne says:

    No problem. Happens to me all the time (usually I figure out my mistake right after I post something). ;)

    Kerry

  9. [...] Kerry Osborne-How to Attach a SQL Profile to a Different Statement – Take 2 [...]

  10. Fikret says:

    Hi I want to ask one question again move_sql_profile.sql works fine. I changed plan for my sql with move_sql_profile but query works slowly again. when i use my query with hint it works fine but the plan is the same

  11. osborne says:

    Fikret,

    I’m not sure I understood your question. If you can provide more details I’ll be happy to to take a look.

    Kerry

  12. Fikret says:

    I have a query when I execute it oracle does not use index. when i use index hint in query works very fine and i want that oracle use my hint. and i created whth mov_sql_profile.sql and i saw that plan changed. And plan shows that it uses index. But in really not using because when i use manually that query with hint it is running quickly but when i remove hint at this time plan not changed but works very hard as it is plan not this plan.
    Please sorry for my bad English

  13. osborne says:

    Fikret,

    Please email me the statements and the plans and the hints associated with the Profile and I’ll have a look.

    Kerry

  14. Rajan says:

    Kerry, Thanks for the post.

    I have created a SQL profile from A database for a select query(4 tables).
    Table TP has function based index Ti. In the profile (used your script) ,
    It is mentioned as below for table which has functional Index.

    q’[INDEX_RS_ASC(@"SEL$1" "TP"@"SEL$1" "FUNCTION_BASED_INDEX")]‘,

    and for Normal index as below.

    q’[INDEX_RS_ASC(@"SEL$1" "BA"@"SEL$1" ("TABLE_NAME"."BRANCH_CD"))]‘

    Now when i import this profile to database B, The functional index was not used, rather it is going for FTS on that particular table alone.
    Rest of the plan is unchanged.
    Am i missing anything here?
    Is there a way i can fix the profile(not sure of the syntax on how to pass the function based index to fix_sql_profile_hint.sql)?

    Regards,
    Rajan.

  15. osborne says:

    Hi Rajan,

    The hint should have the actual name of the index (I’m assuming the name of your index is FUNCTION_BASED_INDEX). If the index exists (with the same name) on the other system, it should work.

    Which script were you using by the way?

    Kerry

    • Rajan says:

      Hi Kerry, Thanks for the reply.
      I used the script coe_xfr_sql_profile.sql written by Carlos(COE/sqlt) .
      Plan is being enforced after hardparse(flushed shared pool). Also can see profile name is displayed in explain plan.
      Below is the index status(It does exist with the name on target,as it is a rman refreshed copy of same database).
      I have changed the names for security reasons.

      OWNER INDEX_NAME INDEX_TYPE TABLE_NAME STATUS LAST_ANAL PAR
      —————————— —————————— ————————— —————————— ——– ——— —
      OWNER FUNCTION_BASED_INDEX FUNCTION-BASED NORMAL TABLEA VALID 21-AUG-13 NO

      —-Source Plan

      |* 7 | TABLE ACCESS BY INDEX ROWID| TABLEA |
      |* 8 | INDEX RANGE SCAN | FUNCTION_BASED_INDEX |
      |* 9 | TABLE ACCESS BY INDEX ROWID | TABLEB |

      –Target plan(After profile implemented)
      |* 7 | TABLE ACCESS FULL | TABLEA | 1738 | 147K| | 16706 |
      |* 8 | TABLE ACCESS BY INDEX ROWID| TABLEB | 1 | 25 | | 2 |

      After creating the profile, i have confirmed that hints exist as of production.

      HINT
      ————————————————————————————————————————————————————————————
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE(’9.2.0′)
      DB_VERSION(’11.1.0.7′)
      OPT_PARAM(‘_complex_view_merging’ ‘false’)
      RBO_OUTLINE
      OUTLINE_LEAF(@”SEL$1″)
      FULL(@”SEL$1″ “TR”@”SEL$1″)
      INDEX_RS_ASC(@”SEL$1″ “TP”@”SEL$1″ “FUNCTION_BASED_INDEX”)
      INDEX_RS_ASC(@”SEL$1″ “BA”@”SEL$1″ (“table columns removed”))
      INDEX(@”SEL$1″ “XR”@”SEL$1″ (“table columns removed”))
      FULL(@”SEL$1″ “CD”@”SEL$1″)
      LEADING(@”SEL$1″ “TR”@”SEL$1″ “TP”@”SEL$1″ “BA”@”SEL$1″ “XR”@”SEL$1″ “CD”@”SEL$1″)
      USE_NL(@”SEL$1″ “TP”@”SEL$1″)
      USE_NL(@”SEL$1″ “BA”@”SEL$1″)
      USE_NL(@”SEL$1″ “XR”@”SEL$1″)
      USE_MERGE(@”SEL$1″ “CD”@”SEL$1″)
      END_OUTLINE_DATA

      Only during execution , the index is missed out by optimizer and it is going for FTS.
      Below is the 10053 trace output.

      Content of other_xml column
      ===========================
      db_version : 11.2.0.3
      parse_schema : SYS
      plan_hash : 1998664599
      plan_hash_2 : 2574832551
      sql_profile : coe_54z12rqbbsr4s_2508851243
      Outline Data:
      /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE(’9.2.0′)
      DB_VERSION(’11.2.0.3′)
      OPT_PARAM(‘_complex_view_merging’ ‘false’)
      ALL_ROWS
      OUTLINE_LEAF(@”SEL$1″)
      FULL(@”SEL$1″ “TR”@”SEL$1″)
      FULL(@”SEL$1″ “TP”@”SEL$1″)
      INDEX_RS_ASC(@”SEL$1″ “BA”@”SEL$1″ (“table columns removed”))
      INDEX(@”SEL$1″ “XR”@”SEL$1″ (“table columns removed”))
      FULL(@”SEL$1″ “CD”@”SEL$1″)
      LEADING(@”SEL$1″ “TR”@”SEL$1″ “TP”@”SEL$1″ “BA”@”SEL$1″ “XR”@”SEL$1″ “CD”@”SEL$1″)
      USE_NL(@”SEL$1″ “TP”@”SEL$1″)
      USE_NL(@”SEL$1″ “BA”@”SEL$1″)
      USE_NL(@”SEL$1″ “XR”@”SEL$1″)
      USE_MERGE(@”SEL$1″ “CD”@”SEL$1″)
      END_OUTLINE_DATA
      */

      Regards,
      Rajan

  16. osborne says:

    Hi Rajan,

    Would it be possible to send the 10053 trace file?

    Kerry

  17. osborne says:

    Ah – just noticed the OPTIMIZER_FEATURES_ENABLE set to 9.2 and that the versions of the DB are not the same. I’m suspecting that might be what’s causing your problem. I did a quick test and setting OFE to 9.2 disabled a function based index on my 11.2.0.2 DB.

    Kerry

    • Rajan says:

      Hi Kerry,

      Let me give some backuground of this. We have a production 11107(OFE set to 9.2.0).We decided to upgrade to 11203 and also OFE
      as a tech roadmap. On pre-prod,the upgrade was successful and all the queries are running fine with OFE 11203 except this select.
      We found that there is a plan change(completely new plan), hence decided to import profile from prod. The profile is created on 11107
      using the script coe_xfr_sql_profile.sql. I was thinking that profile will work on any database version for the
      same sql id. It is almost same but it is ignoring function based index that too during run time. Is there a way to fix or reason behind this
      behaviour or is it documented somewhere?

      Regards,
      Rajan

      • osborne says:

        I believe it’s related to the specific combination of version and setting of OFE. Since you have generated the actual hints using Carlos’s script, you could try just editing the script that creates the SQL Profile and remove the OFE setting to see if that fixes the issue.

        Using SQL Profiles in this manner is not really documented anywhere by Oracle that I know of (other than in SQLT). There may be something in MOS about version and OFE behavior that disables function based indexes but I haven’t run across it.

  18. Eric Fall says:

    Kerry,
    I’m using move_sql_profile.sql in a Dataguard Logical Standby environment. Our Logical Standby is basically a realtime reporting environment. The query runs in the Logical Standby instance and the move of the profile works there. However, I need to also move the profile in our Dataguard Primary (otherwise I’ll have to move it each time I refresh the Logical Standby). Since the query never runs in the Dataguard Primary, when I run move_sql_profile, it fails. It looks to me like it is because you are pulling the sql_text from v$sqlarea. To resolve this I was going to modify move_sql_profile.sql to pull the sql_text directly from dba_sql_profiles. Does that sound correct to you?

    thanks,
    Eric

    • Eric Fall says:

      I should have thought this thru futher. It won’t work. I need the new sql text and dba_sql_profile has the old sql_text. I’ll think about this some more, but if you have suggestions, I’m all ears.

      thanks,
      Eric

    • Eric Fall says:

      A simple answer, I should have thought of it originally.
      Once I have the new profile moved to my new sql_id in my Logical Standby, I can then run coe_xfr_sql_profile.sql and supply the new sql_id and hash value. That gives me a script I can run anywhere.

      Thanks for your time, it does however bring up a limitation. You cannot run the move_sql_profile.sql in an environment where the sql_text is no longer in v$sqlarea. I was about to created a database link from Primary to Logical Stdby to grab the sql_text, but the above workaround is much easier.

      thanks,
      Eric

    • Eric Fall says:

      some of my comment was missed.
      It should read
      Prior to the upgrade the query had 2 where clauses like
      where col2 > x
      and col2 x
      After the upgrade the added clause was
      and x < y
      Same meaning but new sql_text.

  19. osborne says:

    Ha. Eric you get the award for the most comments in a row. :) The move_sql_profile.sql is not one I use much and I only use it if I’m trying to change a statement by hint or alter session, get the plan I want, and then attach the profile to another unhinted statement. And you are right, Carlos’s coe_xfr_sql_profile.sql is much better for moving a profile from one system to another.

    Kerry

Leave a Reply