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.