Tony’s Oracle Tips

December 16, 2011

FORCE_MATCH for Stored Outlines and/or SQL Baselines?????

Filed under: Uncategorized — tonyhasler @ 2:11 pm

Note that there is a follow up to this post here that you should read after this post.

Stored outlines were introduced in Oracle 9i as a way of helping stabilise execution plans.  In 11gR1 these are deprecated in favour of enterprise-edition-only SQL Baselines (sorry standard edition users )-:) but as of 11gR2 neither facility has the capability built into SQL profiles for matching a plan to any statement that matches text when literal values and whitespace are ignored.  I heard this raised as an enhancement request at last week’s Uk Oracle User Group conference.

By a strange coincidence, I was faced with this precise problem this week.  The issue arose with a large application that was very time consuming and expensive to regression test.  It involved a dynamically generated SQL statement against partitioned tables that avoided the use of bind variables for literals, including a couple of dates.  Apart from the literal values, the query was identical from call to call.

When historic data was queried, the execution plan was fine.  However, when the current day was queried the plan was often very poor.  Neither gathering statistics during the day nor copying partition statistics from one day to the next helped.

My long term plan is to identify why the statistics copying isn’t working well and either to customise the copy process or to abandon the use of partition level statistics altogether.  But that is root cause analysis.  What about a recovery plan for my client?

The funny thing is that SQL baselines, stored outlines, and SQL profiles all store hints.  Normally, the types of hints that are stored in a SQL profile are very different from those stored in a SQL baseline or in a stored outline but they don’t have to be.

Let me demonstrate with a two table join for Christmas Day:

CREATE TABLE t1 (d1, c1 DEFAULT 'x') 
PARTITION BY RANGE 
   (d1) 
   ( 
      PARTITION p1 VALUES LESS THAN (DATE '2012-01-01'), 
      PARTITION pdefault VALUES LESS THAN (maxvalue)) 
AS 
       SELECT DATE '2011-12-25', RPAD ('x', 2000) 
         FROM DUAL 
   CONNECT BY LEVEL <= 10000; 

CREATE TABLE t2 
AS 
       SELECT DATE '2011-12-25' d1 
         FROM DUAL 
   CONNECT BY LEVEL <= 100; 

EXEC dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1',no_invalidate=>false); 
EXEC dbms_stats.gather_table_stats(ownname=>user,tabname=>'T2',no_invalidate=>false); 

EXPLAIN PLAN 
   FOR 
      SELECT * 
        FROM t1 NATURAL JOIN t2 
       WHERE d1 = DATE '2011-12-25'; 

SET PAGES 0 LINES 132 

SELECT * FROM TABLE (DBMS_XPLAN.display (NULL, NULL, 'outline')); 

This is the output I get on my 11.2.0.1 test database:

Plan hash value: 937381588             

----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |         |  1000K|  1923M|   932   (1)| 00:00:12 |       |       | 
|   1 |  HASH JOIN               |         |  1000K|  1923M|   932   (1)| 00:00:12 |       |       | 
|   2 |   PART JOIN FILTER CREATE| :BF0000 |   100 |   800 |     3   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL     | T2      |   100 |   800 |     3   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE SINGLE |         | 10000 |    19M|   923   (1)| 00:00:12 |KEY(AP)|KEY(AP)| 
|   5 |    TABLE ACCESS FULL     | T1      | 10000 |    19M|   923   (1)| 00:00:12 |     1 |     1 | 
---------------------------------------------------------------------------------------------------- 

Outline Data                           
-------------                          

  /*+   
      BEGIN_OUTLINE_DATA               
      USE_HASH(@"SEL$58A6D7F6" "T1"@"SEL$1")                          
      LEADING(@"SEL$58A6D7F6" "T2"@"SEL$1" "T1"@"SEL$1")              
      FULL(@"SEL$58A6D7F6" "T1"@"SEL$1")                              
      FULL(@"SEL$58A6D7F6" "T2"@"SEL$1")                             
      OUTLINE(@"SEL$1")                
      OUTLINE(@"SEL$2")               
      MERGE(@"SEL$1")                  
      OUTLINE_LEAF(@"SEL$58A6D7F6")    
      ALL_ROWS                         
      DB_VERSION('11.2.0.1')           
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')                           
      IGNORE_OPTIM_EMBEDDED_HINTS      
      END_OUTLINE_DATA                 
  */

Now what if I re-gather statistics and issue the same statement for New Years day?

EXEC dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1',no_invalidate=>false); 
EXEC dbms_stats.gather_table_stats(ownname=>user,tabname=>'T2',no_invalidate=>false); 
EXPLAIN PLAN 
   FOR 
      SELECT * 
        FROM t1 NATURAL JOIN t2 
       WHERE d1 = DATE '2012-01-01'; 

SELECT * FROM TABLE (DBMS_XPLAN.display (NULL, NULL, 'outline -predicate'));

This is the plan for that date:

Plan hash value: 2331183230                                 
------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |  1019 |     6  (17)| 00:00:01 |       |       |
|   1 |  HASH JOIN              |      |     1 |  1019 |     6  (17)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE SINGLE|      |     1 |  1011 |     2   (0)| 00:00:01 |     2 |     2 |
|   3 |    TABLE ACCESS FULL    | T1   |     1 |  1011 |     2   (0)| 00:00:01 |     2 |     2 |
|   4 |   TABLE ACCESS FULL     | T2   |     1 |     8 |     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------
Outline Data            
-------------           
  /*+                   
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$58A6D7F6" "T2"@"SEL$1")                
      LEADING(@"SEL$58A6D7F6" "T1"@"SEL$1" "T2"@"SEL$1")    
      FULL(@"SEL$58A6D7F6" "T2"@"SEL$1")                    
      FULL(@"SEL$58A6D7F6" "T1"@"SEL$1")                    
      OUTLINE(@"SEL$1") 
      OUTLINE(@"SEL$2") 
      MERGE(@"SEL$1")   
      OUTLINE_LEAF(@"SEL$58A6D7F6")                         
      ALL_ROWS          
      DB_VERSION('11.2.0.1')                                
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')                 
      IGNORE_OPTIM_EMBEDDED_HINTS                           
      END_OUTLINE_DATA  
  */

Now what if I know that the first plan is desirable and the second one isn’t? Well, I can load the outline hints associated with the good plan into a SQL profile:

BEGIN 
   DBMS_SQLTUNE.import_sql_profile ( 
      name          => 'BLOG profile', 
      description   => 'Example profile from Tony Hasler''s blog', 
      category      => 'DEFAULT', 
      sql_text      => q'[ 
 SELECT * 
        FROM t1 NATURAL JOIN t2 
       WHERE d1 = DATE '2011-12-25' 
   ]', 
      REPLACE       => TRUE, 
      force_match   => TRUE, 
      profile       => sqlprof_attr('BEGIN_OUTLINE_DATA', 
'USE_HASH(@"SEL$58A6D7F6" "T1"@"SEL$1")', 
'LEADING(@"SEL$58A6D7F6" "T2"@"SEL$1" "T1"@"SEL$1")', 
'FULL(@"SEL$58A6D7F6" "T1"@"SEL$1")', 
'FULL(@"SEL$58A6D7F6" "T2"@"SEL$1")', 
'OUTLINE(@"SEL$1")', 
'OUTLINE(@"SEL$2")', 
'MERGE(@"SEL$1")', 
'OUTLINE_LEAF(@"SEL$58A6D7F6")', 
'ALL_ROWS', 
'DB_VERSION(''11.2.0.1'')', 
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.1'')', 
'IGNORE_OPTIM_EMBEDDED_HINTS', 
'END_OUTLINE_DATA')); 
END;

Careful editing is required: each hint has to be an element in the SQLPROF_ATTR array so be careful with multi-line hints. If you have a very long hint you may have to use the overloaded variant of this undocumented routine that allows you to specify XML rather than an array (profile_xml rather than profile). In my case, I was able to simply replace a long list of columns in an index hint with an index name. Also note the need to deal with sinqle quotes inside the strings (as in OPTIMIZER_FEATURES_ENABLE).

Note the important FORCE_MATCH parameter.

With this profile in place, we get the same plan for both Christmas Day and New Years Day.

You may wonder what happens if the SQL text already contains hints? Well, you have to include these embedded hints in the SQL text when you create the profile but the BEGIN_OUTLINE_DATA, IGNORE_EMBEDDED_HINTS, and END_OUTLINE_DATA hints in the SQL profile combine to allow the embedded hints to be overridden.

Advertisements

7 Comments »

  1. It took me a while before I realised that baselines didn’t do the force matching.

    Sql profiles support force matching, stored outlines don’t but sql baselines are the next generation of both using the stored outline style of hints (i.e. hinting access paths and join methods rather than the optimizer estimate adjustment style of sql profiles) whilst using the sqlobj$* repository of sql profiles and the sql signature matching method. So it was a slightly surprising ommission.

    Perhaps this sql might be useful to avoid any editing issues (not tested in anger)?

    select cast(collect(cast(extractvalue(value(x), '/hint') as varchar2(500))) as sqlprof_attr) as outline_hints 
    from   xmltable('/*/outline_data/hint'
           passing (select xmltype(other_xml) xml
                    from   v$sql_plan
                    where  other_xml   is not null
                    and    sql_id       = '&sql_id'
                    and    child_number = '&child_number')) x;
    

    Comment by Dom Brooks — December 16, 2011 @ 5:11 pm | Reply

    • Dom,

      This is pretty neat stuff. It never occurred to me to parse the XML directly.

      But if you start with XML which not just leave it as XML and call the variant of import_sql_profile that takes XML? Also, I suspect the BEGIN_OUTLINE_DATA and END_OUTLINE_DATA hints probably have to be added for any SQL that has embedded hints.

      Your knowledge of XML in SQL is far better than mine. Do you want to give it a shot?

      Comment by tonyhasler — December 16, 2011 @ 6:53 pm | Reply

      • The only thing that stopped me was knowing what the xml should look like.
        I assume it’s the same as sqlobj$data.comp_data?

        Comment by Dom Brooks — December 16, 2011 @ 10:00 pm

      • Yes…you seem to need to extract the entire element from the other_xml column and bung it in the profile. Let us try this first and then add the BEGIN_OUTLINE/END_OUTLINE if needed.

        Comment by tonyhasler — December 16, 2011 @ 10:30 pm

      • From my tests, the BEGIN_OUTLINE_DATA/END_OUTLINE_DATA do not seem to be required – i.e we can use the method above to populate a sqlprof_attr collection that does not contain these and a) the sql profile is created, b) the sql profile is reported as used in the execution plan and just to make sure c) the plan does actually change.

        Now in terms of getting an XML snippet from OTHER_XML and just using that as an input into the xml version of IMPORT_SQL_PROFILE, the code below worked for me. Note that the profile_xml takes a clob not xml. It shouldn’t need to use xmltable but I just evolved it from the previous example for simplicity:

        DECLARE
        v_sqlprof_xml clob;
        BEGIN
           select extract(value(x), '/outline_data').getclobval()
           into   v_sqlprof_xml
           from   xmltable('/*/outline_data'
                  passing (select xmltype(other_xml) xml
                           from   v$sql_plan
                           where  other_xml   is not null
                           and    sql_id       = '4xphfjv0h1kg8'
                           and    child_number = '0')) x;
           DBMS_SQLTUNE.import_sql_profile (
              name          => 'BLOG profile',
              description   => 'Example profile from Tony Hasler''s blog',
              category      => 'DEFAULT',
              sql_text      => q'[
              SELECT /*+ xyz */ *
                FROM t1 NATURAL JOIN t2
               WHERE d1 = DATE '2012-01-01'
           ]',
              REPLACE       => TRUE,
              force_match   => TRUE,
              profile_xml   => v_sqlprof_xml);
        END;
        

        Comment by Dom Brooks — December 17, 2011 @ 8:16 am

      • Dom,

        Sorry for the delay in replying. After testing, I agree that the BEGIN_OUTLINE_DATA and END_OUTLINE_DATA hints are unnecessary. I have created a follow up blog with some additional comments.

        Comment by tonyhasler — December 26, 2011 @ 1:50 pm

  2. “…My long term plan is to identify why the statistics copying isn’t working well and either to customise the copy process …”

    absolutely
    I had a similar issue some weeks ago and it turned out that the copy process forgot to adjust the min/max-values for the partition columns which resulted in confusing the optimizer.
    dbms_stats.set_column_stats( ….., …, …, srec => …)
    fixed it

    Comment by Sokrates — December 16, 2011 @ 7:50 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: