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.

Advertisements

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

%d bloggers like this: