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.
