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 filed of v$sql. 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 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!
Mark Bobak:
Wow! This is incredibly timely! Thanks Kerry, this is *exactly* what I was looking for! :-)
July 26, 2010, 7:41 amKapil Goyal:
Thanks Kerry. Now I will be able to use it officially with no concern :) Thanks for sharing it.
July 26, 2010, 11:51 amKyle Hailey:
Great news. Thanks for keeping us updated!
July 26, 2010, 6:53 pmMichael Fontana:
This is superb. While working on a ETL project using the newest version of Oracle, I’ve recently proven that the optimizer can be just as quirky in 11.2 as it’s always been. Queries which should run in seconds can take minutes.
July 26, 2010, 9:38 pmjoel garry:
Those dang licensing restrictions!
words:the Alphonsus
July 29, 2010, 4:25 pmansh A:
Hi
I have seen you use this script @fs.sql but i am not able to find the script for the same…
Please let me know the link for the same.
Regards
August 5, 2010, 3:19 amAnsh
osborne:
It’s just a shortened version of find_sql.sql - (I got tired of typing @find_sql) One of these days I’ll get the scripts organized on a separate page.
col sql_text for a60 wrap
set verify off
set pagesize 999
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs,
August 5, 2010, 9:03 pm(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl(’&sql_text’,sql_text))
and sql_text not like ‘%from v$sql where sql_text like nvl(%’
and sql_id like nvl(’&sql_id’,sql_id)
order by 1, 2, 3
/
Bryan Grenn:
We have been using this procedure for about 6 months now.. It ROCKS.. Especially since we’ve had some plan issues with 11g.. We can go back and create profiles from our old plans.
August 31, 2010, 8:12 pmosborne:
Bryan,
It’s always nice to hear that someone appreciates your work. Everyone likes a pat on the back now and then. Thanks for the feedback.
Kerry
August 31, 2010, 8:49 pmosborne:
Well I knew it was going to get me eventually. I inadvertently hit the SPAM button instead of the APPROVE button on a comment by Kirk Bocas. I was able to hit the back button and get the text though so here it is. (Sorry about that Kirk)
================================================================================================================
Using DBMS_SQLTUNE still requires a OEM Tuning Pack license, and the Tuning Pack requires a Diagnostics Pack license. So to use any of the Tuning Pack functionality you have to license both of these packs.
Any and all methods of accessing Tuning pack functionality require the Pack license.
So as far as I can see, SQL Profiles are only of use/relevant to those sites that have been ripped off by Oracle ?
Kirk Brocas
September 2, 2010, 8:20 pmoratek@gmail.com
osborne:
Kirk,
Don’t hold back - tell us how you really feel!
Yeah I know many of the things I blog about require the Diagnostic and Tuning packs. Seems to me like they ought to be included with the RDBMS license (at least the Enterprise version), but that’s the way the cookie crumbles. Statspack is still free and can be used still in 11g, but DBMS_SQLTUNE is called out in the licensing docs as a feature of the Tuning Pack. So yes, you are right, in order to use this feature the additional add on licenses are required. At this point, the majority of the clients I work with have the Diagnostics and Tuning packs licensed. It seems that many customers have just accepted this add on the way we have had to accept baggage fees from the major airlines. I can choose to take my business else where (Southwest Airlines for example) or I can pay the additional fees (or I can not check my bag). I do realize there are Oracle customers that aren’t licensed for these options. So if you aren’t licensed for this feature, please ignore this post!
Kerry
September 2, 2010, 9:06 pm