Tony’s Oracle Tips

December 26, 2011

FORCE_MATCH for Stored Outlines and/or SQL Baselines????? – follow up

Filed under: Uncategorized — tonyhasler @ 1:45 pm

As has happened a couple of times recently, comments on a blog of mine have helped me understand the subject matter on which I am commenting far better.

In this case a dialogue with Dom Brooks on my previous blog on how to simulate a stored outline/SQL baseline with FORCE_MATCH using SQL profiles has provided me with a simplified way of approaching the creation of said profiles.

The observation is that there is a column OTHER_XML that appears in a number of tables and views from which you can directly copy the outline hints necessary to fix the execution plan of a statement – and use FORCE_MATCH to boot.

It seems that we are not the first to experiment with this technique. See for example this blog by Kerry Osborne published a while ago but I think these posts have slightly different emphasis.

The OTHER_XML column appears in PLAN_TABLE/SYS.PLAN_TABLE$, V$SQL_PLAN, and DBA_HIST_SQL_PLAN. The outline hints from these views are often displayed using the OUTLINE or ADVANCED formatting options to DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR, or DBMS_XPLAN.DISPLAY_AWR table functions respectively.

There is also an OTHER_XML column in DBA_SQLTUNE_PLANS that provides an alternative way to accept a SQL profile. Let me demonstrate. Let us setup some tables first:


-- 
-- Setup tables for test and gather stats 
-- 
DROP TABLE t1; 
DROP TABLE t2; 
DROP TABLE t3; 

BEGIN 
   FOR r IN (SELECT name FROM dba_sql_profiles) 
   LOOP 
      DBMS_SQLTUNE.drop_sql_profile (r.name); 
   END LOOP; 
END; 
/ 

CREATE TABLE t1 
AS 
       SELECT ROWNUM * 2 c1 
         FROM DUAL 
   CONNECT BY LEVEL <= 100000; 

CREATE TABLE t2 
AS 
       SELECT ROWNUM * 2 - 1 c1 
         FROM DUAL 
   CONNECT BY LEVEL <= 50000 
   UNION ALL 
   SELECT 2 FROM DUAL; 

CREATE TABLE t3 
AS 
       SELECT ROWNUM * 2 c1 
         FROM DUAL 
   CONNECT BY LEVEL <= 200000; 

EXEC dbms_stats.gather_table_stats(user,'T1'); 
EXEC dbms_stats.gather_table_stats(user,'T2'); 
EXEC dbms_stats.gather_table_stats(user,'T3'); 

CREATE INDEX t3_i1 
   ON t3 (c1); 

These tables are setup in way to deliberately confuse the CBO. Now, let us run the SQL Tuning advisor.

 
BEGIN 
   DBMS_SQLTUNE.drop_sql_profile (name => 'Tony''s SQLTUNE profile'); 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      NULL; 
END; 
/ 

VARIABLE task_name VARCHAR2(20) 

BEGIN 
   :task_name := 
      DBMS_SQLTUNE.create_tuning_task ( 
         sql_text => ' 
SELECT * 
        FROM t1, t2, t3 
       WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 
 '); 
   DBMS_SQLTUNE.execute_tuning_task (:task_name); 
END; 
/ 

SET LONG 100000 LONGC 100000 PAGES 10000 

SELECT DBMS_SQLTUNE.report_tuning_task (:task_name) FROM DUAL 
/ 

-- 
-- This is the standard way to accept a profile (with FORCE_MATCHING) 
-- 

--BEGIN 
--   DBMS_SQLTUNE.accept_sql_profile (task_name => :task_name, REPLACE => TRUE, name => 'Tony''s SQLTUNE profile', force_match => TRUE);

--END; 
--/ 

--EXPLAIN PLAN 
--   FOR 
--      SELECT * 
--        FROM t1, t2, t3 
--       WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1; 

--SELECT * FROM TABLE (DBMS_XPLAN.display); 

The hints stored in the SQL profile by the commented-out method are not guaranteed to (and are not intended to) fix the SQL profile in quite the same way as a stored outline or SQL baseline. By extracting the outline hints from the advisor task, however, we can fix the plan with the same level of certainty as a SQL baseline or stored outline.

 

DECLARE 
   v_sqlprof_xml   CLOB; 
BEGIN 
   SELECT REGEXP_SUBSTR (other_xml, '<outline_data>.*</outline_data>') 
     INTO v_sqlprof_xml 
     FROM dba_sqltune_plans JOIN dba_advisor_tasks USING (task_id) 
    WHERE     attribute = 'Using SQL profile' 
          AND other_xml IS NOT NULL 
          AND task_name = :task_name; 

   DBMS_SQLTUNE.import_sql_profile (name => 'Tony''s SQLTUNE profile' 
                                    , description => 'Example profile from Tony Hasler''s blog' 
                                    , category => 'DEFAULT' 
                                    , sql_text => q'[ 
SELECT * 
        FROM t1, t2, t3 
       WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 
]' 
                                    , REPLACE => TRUE, 
                                    force_match => TRUE, 
                                    profile_xml => v_sqlprof_xml); 
END; 
/ 

EXPLAIN PLAN 
   FOR 
      SELECT * 
        FROM t1, t2, t3 
       WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1; 

SELECT * FROM TABLE (DBMS_XPLAN.display); 

Note that REGEXP_SUBSTR seems adequate for extracting the outline hints from the OTHER_XML column.

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.

Blog at WordPress.com.