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.

9 Comments

  1. [...] Kerry Osborne -How to Attach a SQL Profile to a Different Statement [...]

  2. [...] 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 [...]

  3. Sufi says:

    Excellent post… Thanks!

    I wonder how you are able to even find time for this work :-)

  4. osborne says:

    It’s usually stuff that gets figured out while trying to solve a real problem for a real customer. Writing up the info often takes longer than actually solving the problem. But it helps when you run into a similar issue and can’t remember how you solved it the last time. ;) I am fortunate in that I get to work on a lot of interesting things and I have enough time in my schedule to occasionally get something posted. But there are more unpublished notes for myself than there are published ones, because it takes along time to clean them up.

    Kerry

  5. Nick says:

    Kerry,

    Thanks a lot this article really helped me solve performance issue with complex SQL, which try as I may I could not get the CBO to come up with a good plan. What I then did was:
    1) hinted the SQL to get a better plan
    2) used EM SQL Tuning Advisor to check my hinted SQL
    3) a SQL profile was subsequently created which improved the hinted plan
    4) using your article linked the SQL profile PLAN to a BASELINE created against the original SQL

    Thanks.

    Nick

  6. Nick says:

    Kerry,

    More detail on the process I followed:
    – Use HINTS and SQL Profile to get good plan
    –1) Run bad sql to fill cusor cache, but kill after short while
    – Get SQL_ID and PLAN_HASH_VALUE from v$sql
    SELECT sql_id, child_number, plan_hash_value from v$sql where sql_text like ‘%BLA BLA BLA%’;

    –2) Create baseline for bad SQL and PLAN
    declare
    nRet pls_integer;
    begin
    nRet := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id=>’981t428aj1ah6′,plan_hash_value=>’579167269′,fixed=>’YES’,enabled=>’YES’);
    end;
    /
    SELECT * FROM dba_sql_plan_baselines;

    –3) Now generate new plan using hints /*+ ORDERED */ could be starting point. You just need to get it to execute in reasonable time.
    – Run hinted SQL through SQL Tuning Advisor in EM and accept SQL profile
    SELECT * FROM dba_sql_profiles;

    –4) Loaded hinted SQL plan, after tuning and accepting profile, into bad SQL baseline (identified by sql_handle)
    declare
    nRet pls_integer;
    begin
    nRet := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id=>’9rmm9ycmkrqkb’,plan_hash_value=>’2233217598′,sql_handle=>’SQL_aa6e0ff42af35d08′,fixed=>’YES’,enabled=>’YES’);
    end;
    /
    SELECT * FROM dba_sql_plan_baselines;

    –5) Drop original BAD baseline through EM

    –6) Validate by running SQL to see performance improvement
    – Check v$sql should have entry connected to SQL PROFILE plan for both BAD and hinted SQL
    SELECT * from v$sql where plan_hash_value=’2233217598′;

    Regards,

    Nick

  7. osborne says:

    Thanks Nick,

    I am increasingly liking baselines these days. Main reason is that you can have multiple plans which is really helpful if you have bind sensitive statements. The other thing that’s cool is that once you have a baseline, additional plans are collected automatically for that statement (even if optimizer_capture_sql_plan_baselines is set to false). So you are bulding a repository for potentially better plans as well. :)

    Kerry

Leave a Reply