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.
Blogroll Report 24/07/2009 – 31/07/2009 « Coskan’s Approach to Oracle:
[...] Kerry Osborne -How to Attach a SQL Profile to a Different Statement [...]
July 31, 2009, 8:53 pmKerry Osborne’s Oracle Blog » Blog Archive How to Attach a SQL Profile to a Different Statement - Take 2 - Kerry Osborne’s Oracle Blog:
[...] 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 [...]
October 13, 2009, 10:00 am