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_vars2.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… [...]