Oracle Support Sanctions Manually Created SQL Profiles!

I originally titled this post: “SQLT – coe_xfr_sql_profile.sql”

Catchy title huh? – (that’s why I changed it)

I’ve been promoting the use of SQL Profiles as a plan control mechanism for some time. The basic idea is to use the undocumented procedure dbms_sqltune.import_sql_profile to build a set of hints to be applied behind the scenes via a SQL Profile. The hints can be created anyway can think of, but one of my favorite ways to generate them is to pull the hints from the other_xml field of v$sql_plan. This is a technique suggested to me originally by Randolf Geist. I have used this approach several times in the past but occasionally I’ve had a few doubts as to whether this is a good idea or even if SQL Profiles can apply all valid hints (see Jonathan Lewis’s comments on this post, Why Oracle Isn’t Using My Profile, where he expresses some doubts as well – he’s also written a bit about SQL Profiles on his site as you might imagine).

So anyway, I just found out this week that there is a script published on Oracle’s Support site that does exactly the same thing. It’s part of the SQLT zip file published in note 215187.1. By the way, SQLT has quite a bit of interesting information in it and the source (PL/SQL) is not wrapped, so it’s worth having a look at. There’s not much in the way of information about it out there, although I did see a reference to it in a comment on one of Jonathan’s recent posts. Maybe I’ll get around to doing another post on that topic some other time. Anyway, the name of the SQL Profile building script is coe_xfr_sql_profile.sql. It basically pulls the hints from the other_xml field of v$sql_plan and turns them into a SQL Profile. So I’m feeling better about myself now that I know that this approach is at least in some way sanctioned by Oracle support.

Here’s an example:

 
SYS@LAB112> @fs
Enter value for sql_text: %skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
688rj6tv1bav0      0  568322376          1          6.78      163,077 select avg(pk_col) from kso.skew where col1 = 1
abwg9nwg8prsj      0 3723858078          1           .01           39 select avg(pk_col) from kso.skew where col1 = 136135
 
2 rows selected.
 
SYS@LAB112> @sql_hints
Enter value for sql_id: abwg9nwg8prsj
Enter value for child_no: 0
 
OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))
 
6 rows selected.
 
SYS@LAB112> @coe_xfr_sql_profile    
 
Parameter 1:
SQL_ID (required)
 
Enter value for 1: abwg9nwg8prsj          
 
 
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3723858078        .006
 
Parameter 2:
PLAN_HASH_VALUE (required)
 
Enter value for 2: 3723858078
 
Values passed:
~~~~~~~~~~~~~
SQL_ID         : "abwg9nwg8prsj"
PLAN_HASH_VALUE: "3723858078"
 
 
Execute coe_xfr_sql_profile_abwg9nwg8prsj_3723858078.sql
on TARGET system in order to create a custom SQL Profile
with plan 3723858078 linked to adjusted sql_text.
 
 
COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_abwg9nwg8prsj_3723858078.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_abwg9nwg8prsj_3723858078.sql 11.4.1.4 2010/07/23 csierra $
SQL>REM
SQL>REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_abwg9nwg8prsj_3723858078.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script is generated by coe_xfr_sql_profile.sql
SQL>REM   It contains the SQL*Plus commands to create a custom
SQL>REM   SQL Profile for SQL_ID abwg9nwg8prsj based on plan hash
SQL>REM   value 3723858078.
SQL>REM   The custom SQL Profile to be created by this script
SQL>REM   will affect plans for SQL commands with signature
SQL>REM   matching the one for SQL Text below.
SQL>REM   Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM   SQL> START coe_xfr_sql_profile_abwg9nwg8prsj_3723858078.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. Should be run as SYSTEM or SYSDBA.
SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM   3. SOURCE and TARGET systems can be the same or similar.
SQL>REM   4. To drop this custom SQL Profile after it has been created:
SQL>REM  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_abwg9nwg8prsj_3723858078');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM  for the Oracle Tuning Pack.
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select avg(pk_col) from kso.skew where col1 = 136135
  7  ]';
  8  h := SYS.SQLPROF_ATTR(
  9  q'[BEGIN_OUTLINE_DATA]',
 10  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 11  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
 12  q'[DB_VERSION('11.2.0.1')]',
 13  q'[ALL_ROWS]',
 14  q'[OUTLINE_LEAF(@"SEL$1")]',
 15  q'[INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))]',
 16  q'[END_OUTLINE_DATA]');
 17  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 18  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 19  sql_text    => sql_txt,
 20  profile     => h,
 21  name        => 'coe_abwg9nwg8prsj_3723858078',
 22  description => 'coe abwg9nwg8prsj 3723858078 '||:signature||'',
 23  category    => 'DEFAULT',
 24  validate    => TRUE,
 25  replace     => TRUE,
 26  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 27  END;
 28  /
 
PL/SQL procedure successfully completed.
 
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
 
            SIGNATURE
---------------------
 15022055147995020558
 
 
... manual custom SQL Profile has been created
 
 
COE_XFR_SQL_PROFILE_abwg9nwg8prsj_3723858078 completed
 
SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
SYS_SQLPROF_0126f1743c7d0005   SAVED           ENABLED  select avg(pk_col) from kso.skew                                       NO
PROF_6kymwy3guu5uq_1388734953  DEFAULT         ENABLED  select 1                                                               YES
PROFILE_cnpx9s9na938m_MANUAL   DEFAULT         ENABLED  select /*+ opt_param('statistics_level','all') */ * from kso.skew wher NO
PROF_79m8gs9wz3ndj_3723858078  DEFAULT         ENABLED  /* SQL Analyze(252,1) */ select avg(pk_col) from kso.skew              NO
PROFILE_9ywuaagwscbj7_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROF_arcvrg5na75sw_3723858078  DEFAULT         ENABLED  select /*+ index(skew@sel$1 skew_col1) */ avg(pk_col) from kso.skew wh NO
SYS_SQLPROF_01274114fc2b0006   DEFAULT         ENABLED  select i.table_owner, i.table_name, i.index_name, FUNCIDX_STATUS, colu NO
SYS_SQLPROF_0127d10ffaa60000   DEFAULT         ENABLED  select table_owner||'.'||table_name tname , index_name, index_type, st NO
SYS_SQLPROF_01281e513ace0000   DEFAULT         ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO
PROFILE_5bgcrdwfhbc83_EXACT    DEFAULT         ENABLED  select avg(pk_col) from kso.skew where col1 = :"SYS_B_0"               YES
coe_abwg9nwg8prsj_3723858078   DEFAULT         ENABLED                                                                         NO
 
17 rows selected.
 
SYS@LAB112> -- that's interesting - looks like the sql_text has gotten wiped out
SYS@LAB112> -- let's see if it works anyway
SYS@LAB112> 
SYS@LAB112> select avg(pk_col) from kso.skew where col1 = 136135;
 
AVG(PK_COL)
-----------
   15636135
 
SYS@LAB112> @fs
Enter value for sql_text: select avg(pk_col) from kso.skew where col1 = 136135
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
abwg9nwg8prsj      0 3723858078          1           .02           47 select avg(pk_col) from kso.skew where col1 = 136135
 
1 row selected.
 
SYS@LAB112> @dplan
Enter value for sql_id: abwg9nwg8prsj
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  abwg9nwg8prsj, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136135
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    32 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    32 |   768 |    32   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    32 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136135)
 
Note
-----
   - SQL profile coe_abwg9nwg8prsj_3723858078 used for this statement
 
 
24 rows selected.

So it is very similar to my create_sql_profile.sql script. The Oracle COE script does have the advantage of creating an output script that can be run to create the SQL Profile. That means you have a chance to edit the hints before creating the SQL Profile. It also means you can easily move a SQL Profile from one environment (TEST for example) to another (PROD for example).

But the best thing about it is that I no longer have to be concerned about using an undocumented procedure to do something that it may not have been intended to do in the first place!

Leave a Reply