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:
- make a copy of the statement – (I use my build_bind_vars.sql (or build_bind_vars_awr.sql) script)
- do whatever you want to make it get the plan you want including adding hints
- create a Profile on the new statement – (I use my create_sql_profile.sql (rg_sqlprof1.sql) script)
- fix the Profile to eliminate any bad index hints – (I use my fix_sql_profile_hint.sql script)
- move the Profile over to the original statement – (I use my move_sql_profile.sql script)
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.
[…] How to Attach a SQL Profile to a Different Statement – Take 2 […]
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
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
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?
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
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)
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
It was my fault that I was using the same sqlid for the moved one everything works fine 🙂
No problem. Happens to me all the time (usually I figure out my mistake right after I post something). 😉
Kerry
[…] Kerry Osborne-How to Attach a SQL Profile to a Different Statement – Take 2 […]
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
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
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
Fikret,
Please email me the statements and the plans and the hints associated with the Profile and I’ll have a look.
Kerry
[…] b) You can use scripts provided by Kerry Osborne in following article http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take… […]
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.
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
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
Hi Rajan,
can u plese explain the hint
/*+
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
*/
Hi Rajan,
Would it be possible to send the 10053 trace file?
Kerry
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
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
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.
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
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
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
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.
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
[…] http://kerryosborne.oracle-guy.com/2009/07/how-to-attach-a-sql-profile-to-a-different-statement/ http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take… SQL Profiles: Check what they do before accepting Oracle SQL Profile: Why Multiple […]
Thank you for this.
I’ve saw a Senior member of my former team do this and was always curious about what he did. I found out on your blog.
Thank you very much.
[…] http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take… Oracle’s OPT_ESTIMATE hint: Usage Guide […]