How to Attach a SQL Profile to a Different Statement
One of the old tricks with Outlines was to switch hints between two statements. This allowed hints to be applied to a statement to influence the plan that the optimizer chose. The original statement (without the hints) could then be made to behave by swapping the Outlines. It was always a questionable approach in my mind. However, DBMS_SQLTUNE has a built-in procedure to import hints into a SQL Profile which makes it feel a little less risky. So here’s a quick little script to do the old “Outline Switcheroo Trick”, but with SQL Profiles. It’s based on some work done a few months back by myself and Randolf Geist. Here’s a couple of posts to look at for background info:
This iteration has a couple of new scripts:
create_sql_profile2.sql – creates a profile for one statement based on hints from another (wrapper for rg_profile_hints3.sql)
rg_sqlprof3.sql – modified version of Randolf’s original script, pulls hints from v$sql_plan
sql_profile_hints.sql – shows the hints in a SQL Profile for 10g
NOTE: I have posted an update to this approach here:
How to Attach a SQL Profile to a Different Statement – Take 2
You may want to skip the example below and refer to it instead (don’t miss the caveats at the bottom of this post though).
Here’s an example:
> sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:38:22 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @flush_pool
System altered.
Elapsed: 00:00:00.31
SQL> set echo on
SQL> set timing on
SQL> @avgskewi
SQL> select avg(pk_col) from kso.skew
2 where col1 = 23489
3 /
AVG(PK_COL)
-----------
Elapsed: 00:00:00.01
SQL> @avgskewi_hint
SQL> select /*+ full(skew) */ avg(pk_col) from kso.skew
2 where col1 = 23489
3 /
AVG(PK_COL)
-----------
Elapsed: 00:00:11.23
SQL> set timing off
SQL> @find_sql
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs,
2 (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
3 buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
4 sql_text
5 from v$sql s
6 where upper(sql_text) like upper(nvl('&sql_text',sql_text))
7 and sql_text not like '%from v$sql where sql_text like nvl(%'
8 and sql_id like nvl('&sql_id',sql_id)
9 order by 1, 2, 3
10 /
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
7s0b9ygcrj77u 0 3723858078 1 .01 134 select avg(pk_col) from kso.skew where col1 = 23489
9r9wq9xqsw6mu 0 568322376 1 10.97 173,731 select /*+ full(skew) */ avg(pk_col) from kso.skew where col
1 = 23489
SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
2 /
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
Plan hash value: 3723858078
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 53 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 54 | 594 | 53 (2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SKEW_COL1 | 54 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=23489)
20 rows selected.
SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
2 /
Enter value for sql_id: 9r9wq9xqsw6mu
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9r9wq9xqsw6mu, child number 0
-------------------------------------
select /*+ full(skew) */ avg(pk_col) from kso.skew where col1 = 23489
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31719 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 54 | 594 | 31719 (37)| 00:00:43 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=23489)
19 rows selected.
SQL> -- so one statement hinted to use full table scan the other not hinted is using an index
SQL> -- let's try creating a profile from the hinted version and putting it on none hinted statement
SQL> set echo off
SQL> @create_sql_profile2
Enter value for sql_id to generate profile from: 9r9wq9xqsw6mu
Enter value for child_no to generate profile from: 0
Enter value for sql_id to attach profile to: 7s0b9ygcrj77u
Enter value for child_no to attach profile to: 0
Enter value for category:
Enter value for force_matching:
PL/SQL procedure successfully completed.
SQL> @sql_profiles
Enter value for sql_text: %skew%
Enter value for name:
NAME CATEGORY STATUS SQL_TEXT FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_922pr090z0bvm DEFAULT ENABLED select sql_id, dbms_lob.substr(sql_text,3999,1) sql_text from dba_hist NO
PROFILE_7s0b9ygcrj77u_attach DEFAULT ENABLED select avg(pk_col) from kso.skew NO
SQL> @sql_profile_hints
Enter value for profile_name: PROFILE_7s0b9ygcrj77u
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
SQL> @flush_pool
System altered.
SQL> @avgskewi
AVG(PK_COL)
-----------
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
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
7s0b9ygcrj77u 0 568322376 1 10.29 174,071 select avg(pk_col) from kso.skew where col1 = 23489
SQL> @dplan
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31719 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 54 | 594 | 31719 (37)| 00:00:43 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=23489)
Note
-----
- SQL profile "PROFILE_7s0b9ygcrj77u" used for this statement
23 rows selected.
SQL> -- so this worked, statement is now using a profile generated from hints on another statement
A couple of notes:
- Beware of table aliases as they can cause hints to fail.
- Beware of any structural changes as they can cause hints to fail.
- Beware of the non-specific format of the INDEX hint.
(it leaves the optimizer with a lot of flexibility than you probably want it to have)
(for more details, see this post: Why Isn’t Oracle Using My Outline / Profile / Baseline?
Your comments are always welcomed.
