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!

41 Comments

  1. Mark Bobak says:

    Wow! This is incredibly timely! Thanks Kerry, this is *exactly* what I was looking for! :-)

  2. Kapil Goyal says:

    Thanks Kerry. Now I will be able to use it officially with no concern :) Thanks for sharing it.

  3. Kyle Hailey says:

    Great news. Thanks for keeping us updated!

  4. Michael Fontana says:

    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.

  5. joel garry says:

    Those dang licensing restrictions!

    words:the Alphonsus

  6. ansh A says:

    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
    Ansh

  7. osborne says:

    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,
    (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
    /

  8. Bryan Grenn says:

    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.

  9. osborne says:

    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

  10. osborne says:

    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
    oratek@gmail.com

  11. osborne says:

    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

  12. Kirk Brocas says:

    No worries Kerry, some people probably do mark me as spam, and I like to speak my mind!

    Probably tainted by the fact that I am working at a site at the moment that is running Enterprise Edition, but no Diag and Tuning packs – small company, small budget.

    I agree completely that these features should be included with the RDBMS license for EE – I was telling our rep just that yesterday :) – as well as a few other things like Linux behind the hype and marketing is just a collection of fragmented non-integrated all-me-own-job software on top of a hobbyist flakey kernel, and they should push Solaris more :) Anyway…

    Looks like SQL Plan Baselines SQL Plan Management are available though for ‘free’. Now if I can just work out how to add an optimizer hint to a specific SQL via plan management rather than altering production code….

  13. rasta dba says:

    Hi
    thanks a lot for sharing and help us become better dba
    i am facing a little executing the coe_xfr_sql_profile.sql

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at \SYS.XMLTYPE\, line 254

    thanks a lot for your help

  14. osborne says:

    Well that is a script supplied by Oracle support, so you could open an SR with them. Or you could use one of mine like create_sql_profile.sql. Just do a search in the search box at the top of the page to find the script.

    Kerry

  15. [...] and I have blogged about it before on numerous occasions. You may want to look back at this post, Oracle Support Sanctions Manually Created SQL Profiles, to get a better feel for where the hints came from that are used to replace the OPT_ESTIMATE [...]

  16. Gerry Mowbray says:

    Excellent article, thanks for posting this, we manually created a profile with the good plan using your notes to fix a performance issue today.

  17. osborne says:

    No problem. Thanks for the positive feedback. Now that you’ve got a taste of the drug, make sure you don’t let it take you down the path to addiction. Profiles are an extremely powerful tool, but they are not appropriate for fixing all your problems. You can quickly find yourself having 100′s of these things. If you do, you probably have an underlying problem that needs to be addressed. I wrote a chapter in the book Pro Oracle SQL by Apress that covers this topic in great detail by the way. If you want more details, it’s got pretty much everything I know on the topic. Like Forest Gump said, “And that’s all I’ve got to say about that.”.

    Kerry

  18. Sam says:

    Hi Kerry.

    Do we have a method to pull a plan from 10.2 and bring over to 11.1? There is a query taking a bad plan. Statistics on the tables involved in the query look to be the same (were imported ok). I’d like to bring the plan over but don’t know the steps.

  19. osborne says:

    Sam,

    The method documented here should work. Steps are:

    1. Log into 10g DB
    2. Find sql_id and hplan_hash_value for the statement
    3. run coe_xfr_sql_profile.sql (it will generate a file containing the sql_id and plan_hash_value in the name)
    4. Copy the generated script to the machine running 11g
    5. Log in to 11g DB and verify that the statement is there
    6. Run the generated script
    7. Check to make sure the SQL Profile generates the expected plan

    By the way, I should mention that I consider using SQL Profiles in this manner as a band aid, best used for emergency situations to give you time to figure out why you’re not getting the plan you expect.

  20. Sam says:

    Thank You Kerry… will give a try right away :)

  21. Chuck says:

    Kerry,
    Thanks for the great blog, tons of usefull information.
    I’m having trouble implementing this concept. I’m trying to pull the plan from PROD and implement it in TEST, but the profile is not getting used in test.

    Many issues.., primary one being the length of the hint (ORA-06502).., but found solutions between your blog and Randolf’s (thanks both).

    So I was finally able to extract the hints from the PROD system and create the profile on the TEST system, but it is not using it. Same sqlid, sqltext, but xplans are still different and TEST does not show the profile being used. What am I missing?

  22. osborne says:

    Hi Chuck,

    I’m not sure. There is a signature that’s calculated in order to match a statement to a SQL Profile. Check and see if those are the same. You didn’t say how you were generating the Profile but the text has to match pretty much exactly (except for literals if you use FORCE_MATCHING).

    Kerry

  23. Chuck says:

    The statement has the same signature in V$SQL on both test and prod, but would I expect that signature to be in dba_sql_profiles as well?

    SYSTEM-c4prd1-H3PRD-> select EXACT_MATCHING_SIGNATURE from v$sql
    2 where sql_id = ‘d9a4x6bvkkfhr’;

    EXACT_MATCHING_SIGNATURE
    ————————
    2.9681E+18

    SYSTEM-c4tst1-H3PRD-> select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = ‘d9a4x6bvkkfhr’;

    EXACT_MATCHING_SIGNATURE
    ————————
    2.9681E+18

    SYSTEM-c4tst1-H3PRD-> select name, category, signature, created, last_modified, type, status, force_matching from dba_sql_profiles

    NAME CATEGORY SIGNATURE CREATED LAST_MODIFIED TYPE STATUS
    —————————— —————————— ———- ——————- ——————- ——— ——–
    coe_d9a4x6bvkkfhr_1957636171 DEFAULT 1.5067E+19 09-01-2012 15:56:28 09-01-2012 16:17:58 MANUAL ENABLED

  24. osborne says:

    Yes – the SIGNATURE in dba_sql_profiles should match either EXACT_MATCHING_SIGNATURE or FORCE_MATCHING_SIGNATURE from v$sql depending on whether you used FORCE_MATCHING or not. So something must have happened to make the text string of the SQL text not match exactly.

    By the way, I generally don’t move Profiles from one environment to another. It seems more productive to me to work directly in the production environment on a test copy of the problem SQL (one with a comment in it for example). There’s no guarantee that the hints that produce the desired plan in TEST will produce the same plan in PROD even if you get it to use the Profile. That’s because there are so many possible differences in the inputs into the optimizer (stats, parameters, memory sizing, etc…) If you do want to move an existing Profile you can also use the DBMS_SQLTUNE.PACK_STGTAB_SQLPROF and DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF procedures. We’ve used these for migrating a set of Profiles to a new platform, but I’ve not written about it on the blog as it’s a relatively uncommon thing for me.

    Hope that gives you some ideas.

    Kerry

    • Chuck says:

      Looking in coe_xfr_sql_profile I see that the signatures are created by calling:
      DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt)

      So yes, something must have happened to the sql somewhere along the line.

      I understand your point of not moving profiles between environments, but this is a VLDB and I’m not permitted to modify the sql so I was considering this as an alternative for plan stability when needed.

      Thanks for all the help

  25. Paul says:

    coe_xfr_profile.sql doesn’t handle long sql statements, as has been noted elsewhere in this blog.
    A workaround for that is to have the generated code build the sql statement like so:

    sql_txt := q’[
    SELECT i106134 as E106134,]‘;
    sql_txt := sql_txt || q’[i106140 as E106140,]‘;
    sql_txt := sql_txt || q’[i106142 as E106142,]‘;
    sql_txt := sql_txt || q’[i106157 as E106157,]‘;
    sql_txt := sql_txt || q’[i108775 as E108775,]‘;
    sql_txt := sql_txt || q’[i108776 as E108776,]‘;
    sql_txt := sql_txt || q’[i108778 as E108778,]‘;
    sql_txt := sql_txt || q’[i108783 as E108783,]‘;
    sql_txt := sql_txt || q’[i108788 as E108788,]‘;
    etc.

    I’ve done this with a perl rewrite of coe_xfr, it handled a 40,000 character discoveer query successfully, the profile was used this morning in production, which is encouraging since I had expected a long struggle with text matching. It could be done as easily in pl/sql, however since I was having problems with coe_xfr putting question marks into my sql, I went ahead with perl.

    In perl, based on the search/replace that is done in coe_xfr_sql_profile.sql, I did this:

    $sql_text =~ s/[^[:print:]]/ /g;
    $sql_text =~ s/)/)]’;$chr10 sql_txt := sql_txt || q’[/g;
    $sql_text =~ s/,/,]‘;$chr10 sql_txt := sql_txt || q’[/g;
    $sql_text =~ s/$/ ]‘;/;

    Then, as the code is generated:

    print MYFILE “BEGINn”;
    print MYFILE “sql_txt := q’[n”;
    print MYFILE “$sql_text”;

    Ended up using XML:DOM to parse and generate code for processing very long other_xml from this large query ( 300-step execution plan ); of course it was necessary to include a workaround for very long hints.

    I went through this in an SR with Oracle, who told me that there are no plans to modify coe_xfr_sql_profile.sql to work with long queries.

  26. osborne says:

    Hi Paul,

    Very interesting. Carlos Sierra is the guy that developed the SQLT tools. You might reach out to him directly about the limitations (carlos.sierra@oracle.com). I rarely use that script since I have my own that I’m used to. By the way, I have run into hints that are too long for the 500 character input array but have not hit the 2000 hint limit imposed by the import_sql_profile procedure. Generally the hints that are problematic are the new format INDEX hints. If necessary I shorten those by eliminating the columns and change to use the older index format that specifies the name of the index. Would you mind sharing how you handled the long hints programatically?

    • Carlos Sierra says:

      Hey Kerry,

      Paul contacted me. I am testing a new version of coe_xfr_sql_profile.sql that handles big sql_texts (larger than 32K).
      By the way, coe_xfr_sql_profile.sql handles long hints (larger than 2000).

      FOR i IN (SELECT /*+ opt_param(‘parallel_execution_enabled’, ‘false’) */
      SUBSTR(EXTRACTVALUE(VALUE(d), ‘/hint’), 1, 4000) hint
      FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), ‘/*/outline_data/hint’))) d)
      LOOP
      l_hint := i.hint;
      WHILE NVL(LENGTH(l_hint), 0) > 0
      LOOP
      IF LENGTH(l_hint) <= 500 THEN
      DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
      l_hint := NULL;
      ELSE
      l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
      DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
      l_hint := ' '||SUBSTR(l_hint, l_pos);
      END IF;
      END LOOP;
      END LOOP;

      Cheers — Carlos

  27. Paul says:

    I was told that the owner of the coe_xfr_sql_profile note had said there are no plans to change it to handle long queries. Maybe sql profiles are deprecated, so no further development? I assumed the owner of the note was Carlos, but maybe not. I’ll send him a note.

    Re long hints: Coe_xfr_sql_profile.sql already does a good ( pl/sql) job with long hints. That’s where I got the technique that I used to work around the long sql limitation. This perl code was thrown together quickly, but the results have been ok so far, even though I was sleep deprived while writing it. The other_xml query is much like one from coe_xfr_sql_profile.sql, maybe I’m in copyright violation? Anyway, this perl code generated the same hints as coe_xfr_sql_profile.sql for my single very large test case.

    I notice some oddly-placed lower case n’s in the code I pasted in earlier, they were backslash-n in the source code but the paste didn’t handle the backslash.

    my $other_xml;

    my $xmlquery=”SELECT other_xml
    FROM dba_hist_sql_plan
    WHERE sql_id = TRIM(?)
    AND plan_hash_value = TO_NUMBER(TRIM(?))
    AND other_xml IS NOT NULL
    ORDER BY id”;

    $sth=$dbh->prepare($xmlquery);
    $sth->execute($sql_id, $plan_hash_value);
    while ( my $hrf = $sth->fetchrow_hashref() ) {
    $other_xml.= $$hrf{OTHER_XML};
    }

    my $chr10=chr(10);

    my $xmlfile=”coe_xfr4.pl.$sql_id.$plan_hash_value.tmp.xml”;
    open ( XMLFILE, “> $xmlfile ” ) or die “Couldn’t open $xmlfile for write\n”;
    print XMLFILE $other_xml,”\n”;
    close (XMLFILE);

    my $parser = new XML::DOM::Parser;
    my $doc = $parser->parsefile ($xmlfile);
    my @matching_tags = $doc->getElementsByTagName(“hint”);

    my $matches=0;

    Then, as the code is generated:

    print MYFILE “\nh := SYS.SQLPROF_ATTR(\n”;
    print MYFILE “q’[BEGIN_OUTLINE_DATA]‘,\n”;

    foreach my $match(@matching_tags) {
    traverse($match,\$matches);
    }

    print MYFILE “q’[END_OUTLINE_DATA]‘);\n”;

    Here’s the traverse function. ( google was my friend, the guts of this came from an IBM intro to XML:DOM ).

    sub traverse ( $$ ) {

    my($node)= shift;
    my $matches= shift;
    if ($node->getNodeType == ELEMENT_NODE) {
    # print “getNodeName, “>”;
    foreach my $child ($node->getChildNodes()) {
    traverse($child,\$matches);
    }
    # print “getNodeName, “>\n”;
    } elsif ($node->getNodeType() == TEXT_NODE) {
    my $data = $node->getData;
    $data =~ s///;
    $data =~ s///;

    if ( length($data) > 499 ) {

    my $S=0;
    my $f=0;
    my $thistime = 0;
    my $strlen=length($data);
    # print MYFILE “q’[";
    while ( $S = 400 ) {
    print MYFILE "q'[".substr($data,$S,$thistime)."]‘,\n”;
    $S += $thistime;
    $thistime=0;
    }
    }

    } else {
    print MYFILE “q’[".$data."]‘,\n”;
    }
    $matches++; # not really used
    }

    }

  28. Paul says:

    Another pasting problem, some of the perl code works with xml tags…

    These two lines in the previous post:

    $data =~ s///;
    $data =~ s///

    are in fact removing the tags “hint” and “/hint” from the string, so between the first set of forward slashes.

    Maybe code tags would help?

    $data =~ s///;
    $data =~ s///;

  29. Paul says:

    Not a problem if you don’t publish my responses. I wasn’t aware of the 2,000 char limit, only the 500 char one, which coe_xfr handles well. My longest hint was 1905 characters, a LEADING hint for a 300 step execution plan. I suppose for such a leading hint > 2000 chars it would be ok to remove tokens from the trailing edge of the hint until you’re under the 2000 char limit. I’d guess that you won’t run into INDEX hints > 2,000 in length, so the coe_xfr code would work for your existing long index hints.

  30. paul says:

    I contacted Carlos. He said he will look into it. It’s easy from here to say “here’s how to fix your code” but I don’t envy him the QA part of it.

    • Carlos Sierra says:

      Paul,
      I am the author of the coe_xfr_sql_profile.sql. I had no plans to change it to handle long queries (>32k) since I was under the impression yours was a unique and rare case. Anyways, I updated the script (version 11.4.4.4) and now it seems to be handling long queries well. Please test it and give me feedback.
      Thanks for your patience,
      Carlos

  31. osborne says:

    Thanks for all the info Carlos – and for developing such a useful tool. ;)

    Kerry

  32. paul says:

    I ran a few tests of the new not-yet-released coe_xfr_sql_profile.sql, seems to work well. I used it to fix a production problem Friday morning by generating a profile from a fast plan in one database, then creating the profile for force_match=>true in the problem database. Performance went from 1 hour+ to 3 seconds. Really nice. It’s particularly nice to be able to retrieve the query/plan information from the shared pool – this makes it possible to create profiles for queries that don’t make it into sql history, and you don’t have to wait for the next AWR snapshot to start working on the profile.

    coe_xfr_sql_profile is a big help!! Thanks, Carlos. And thanks Kerry for this blog and for providing those great scripts that I’ve used in the past for creating sql profiles. Sql profiles have saved my bacon many times..

    Also, I’m told that a new version of SQLT with the new coe_xfr script is to be released April 2.

  33. Azhar Syed says:

    Hi ,

    Our query when it runs first time it takes 8 hours and its consecutive runs take about 5-10 seconds. We still are figuring out why in test environment but when i ran the sql tuning advisor it suggested a profile with 99% benefit.So I would apply the profile and move it to prod but the sql_id’s are different in both the DB’s .Is there anyway that we can get around this and still export & import the profile ?

  34. osborne says:

    Carlos’s script creates a text file (sql script) that you can edit. Take a look and see if you can figure out what’s going on. The script uses the sql text of your statement, not the sql_id by the way.

    Kerry

Leave a Reply