….yes, that is correct. 🙂
You can apply an execution plan even if it has been aged out from your local Automatic Workload Repository (AWR). But first you’ll need a graveyard 🙂 , in other words AWR Warehouse Repository. AWR Warehouse can keep history of your database metrics longer than your local database.
AWR Warehouse came in really handy when a monthly job changed the execution plan. Using AWR Warehouse I was able to figure out the execution plan I wanted to force.
But how can I create a SQL Profile or SQL Plan Baseline for a plan that is in a different database? That is where Kerry Osborne scripts came in handy.
To force any plan, you need OUTLINE_DATA. Outline Data is nothing more than bunch of Optimizer Hints that Oracle uses to figure out execution plan and it is stored in OTHER_XML column of v$SQL_PLAN or DBA_HIST_SQL_PLAN view.
In our environment, AWR Snapshot are kept for 30 days in local database, but you can query AWR Warehouse Repository database to get information older than 30 days (depending on the retention set in AWR Warehouse).
To force a plan from AWR Warehouse Repository, you’ll need to extract information from OTHER_XML column.
First I used following query in AWR Warehouse Database to find the plan I want.
SELECT STAT.SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ROUND (SUM (ELAPSED_TIME_DELTA) / 1000000 / 60, 2) elapsed_time_mins, TRUNC (SS.END_INTERVAL_TIME) FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS, dbsnmp.caw_dbid_mapping m WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND UPPER (STAT.SQL_ID) = 'F2P5QTHSZVXQJ' AND LOWER (M.TARGET_NAME) = 'db1.example.com' AND SS.DBID = M.NEW_DBID AND PLAN_HASH_VALUE > 0 GROUP BY STAT.SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, TRUNC (SS.END_INTERVAL_TIME) ORDER BY TRUNC (SS.END_INTERVAL_TIME); SQL_ID PLAN_HASH_VALUE PARSING_SCHEMA_NAME ELAPSED_TIME_MINS TRUNC(SS.END_INTERVAL_TIME) f2p5qthszvxqj 593130478 USER1 102.51 11/14/2016 f2p5qthszvxqj 593130478 USER1 82.78 11/21/2016 f2p5qthszvxqj 593130478 USER1 99.18 11/28/2016 f2p5qthszvxqj 593130478 USER1 104.54 12/5/2016 f2p5qthszvxqj 2168570097 USER1 122.2 12/12/2016 f2p5qthszvxqj 2168570097 USER1 157.34 12/19/2016 f2p5qthszvxqj 2168570097 USER1 196.52 12/26/2016 f2p5qthszvxqj 2168570097 USER1 184.88 1/9/2017 f2p5qthszvxqj 3004456317 USER1 271.19 1/16/2017 f2p5qthszvxqj 2168570097 USER1 184.04 1/23/2017 f2p5qthszvxqj 54869993 USER1 539.46 1/30/2017
As you can see Plan Hash Value, 593130478 was same until 12/5/2016. This plan changed after 12/5/2016 and it is not available in local database. So to force this plan I need information from OTHER_XML column. Running following query against AWR Warehouse Repository gave me the OUTLINE_DATA I needed to create a SQL Profile.
SET PAGES 1000 LINES 200 TRIM ON TRIMS ON HEAD OFF SELECT 'q''[' || EXTRACTVALUE (VALUE (D), '/hint') || ']'',' AS OUTLINE_HINTS FROM XMLTABLE ( '/*/outline_data/hint' PASSING (SELECT XMLTYPE (OTHER_XML) AS XMLVAL FROM SYS.DBA_HIST_SQL_PLAN WHERE SQL_ID = 'f2p5qthszvxqj' AND PLAN_HASH_VALUE = 593130478 AND OTHER_XML IS NOT NULL)) D; q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]', q'[DB_VERSION('12.1.0.2')]', q'[OPT_PARAM('optimizer_index_cost_adj' 50)]', q'[OPT_PARAM('optimizer_index_caching' 50)]', q'[OPT_PARAM('optimizer_dynamic_sampling' 11)]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$A1BD89B9")]', q'[MERGE(@"SEL$5B8A663B")]', q'[OUTLINE_LEAF(@"SEL$2")]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[OUTLINE_LEAF(@"INS$1")]', q'[OUTLINE(@"SEL$7")]', q'[OUTLINE(@"SEL$5B8A663B")]', q'[MERGE(@"SEL$6A1B73E7")]', q'[OUTLINE(@"SEL$6")]', q'[OUTLINE(@"SEL$6A1B73E7")]', q'[MERGE(@"SEL$37633EB5")]', q'[OUTLINE(@"SEL$5")]', q'[OUTLINE(@"SEL$37633EB5")]', q'[MERGE(@"SEL$3")]', q'[OUTLINE(@"SEL$4")]', q'[OUTLINE(@"SEL$3")]', q'[FULL(@"INS$1" "ACCUM_DTL_OUT"@"INS$1")]', q'[NO_ACCESS(@"SEL$1" "ACCUM"@"SEL$1")]', q'[NO_ACCESS(@"SEL$2" "from$_subquery$_003"@"SEL$2")]', q'[GBY_PUSHDOWN(@"SEL$2")]', q'[USE_HASH_AGGREGATION(@"SEL$2")]', q'[FULL(@"SEL$A1BD89B9" "CDLM"@"SEL$5")]', q'[INDEX(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6" ("ACCUM_BUCKETS"."IS_INBOUND" "ACCUM_BUCKETS"."ACC_TYPE" "ACCUM_BUCKETS"."ACAC_ACC_NO"))]', q'[INDEX(@"SEL$A1BD89B9" "CDML"@"SEL$3" ("CMC_CDML_CL_LINE"."CLCL_ID" "CMC_CDML_CL_LINE"."CDML_SEQ_NO"))]', q'[FULL(@"SEL$A1BD89B9" "MEME"@"SEL$4")]', q'[INDEX(@"SEL$A1BD89B9" "CLCL"@"SEL$3" ("CMC_CLCL_CLAIM"."CLCL_ID"))]', q'[LEADING(@"SEL$A1BD89B9" "CDLM"@"SEL$5" "BUCKETS"@"SEL$6" "CDML"@"SEL$3" "MEME"@"SEL$4" "CLCL"@"SEL$3")]', q'[USE_HASH(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[USE_NL(@"SEL$A1BD89B9" "CDML"@"SEL$3")]', q'[NLJ_BATCHING(@"SEL$A1BD89B9" "CDML"@"SEL$3")]', q'[USE_HASH(@"SEL$A1BD89B9" "MEME"@"SEL$4")]', q'[USE_NL(@"SEL$A1BD89B9" "CLCL"@"SEL$3")]', q'[NLJ_BATCHING(@"SEL$A1BD89B9" "CLCL"@"SEL$3")]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6" NONE BROADCAST)]', q'[PX_JOIN_FILTER(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "CDML"@"SEL$3" NONE BROADCAST)]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "MEME"@"SEL$4" BROADCAST NONE)]', q'[PX_JOIN_FILTER(@"SEL$A1BD89B9" "MEME"@"SEL$4")]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "CLCL"@"SEL$3" NONE BROADCAST)]', q'[SWAP_JOIN_INPUTS(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[GBY_PUSHDOWN(@"SEL$A1BD89B9")]', q'[USE_HASH_AGGREGATION(@"SEL$A1BD89B9")]', q'[PARTIAL_JOIN(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]',
Now I have all the information I need to create SQL Profile to force this plan.
Here is the procedure (modified script from Kerry Osborne Website) I used to run in the target database to manually create SQL profile. sql_txt must match the sql from v$sql.sql_fulltext.
SET SERVEROUTPUT ON; SET NUMFORMAT 99999999999999999999 WHENEVER SQLERROR EXIT SQL.SQLCODE; VAR signature NUMBER; DECLARE sql_txt CLOB; h SYS.SQLPROF_ATTR; BEGIN SELECT SQL_FULLTEXT INTO SQL_TXT FROM SYS.V_$SQL WHERE SQL_ID = 'f2p5qthszvxqj' AND CHILD_NUMBER = 0; h := SYS.SQLPROF_ATTR ( q'[BEGIN_OUTLINE_DATA]', --Need to add this q'[IGNORE_OPTIM_EMBEDDED_HINTS]', --Output from a query ran in AWR Warehouse database q'[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]', q'[DB_VERSION('12.1.0.2')]', q'[OPT_PARAM('optimizer_index_cost_adj' 50)]', q'[OPT_PARAM('optimizer_index_caching' 50)]', q'[OPT_PARAM('optimizer_dynamic_sampling' 11)]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$A1BD89B9")]', q'[MERGE(@"SEL$5B8A663B")]', q'[OUTLINE_LEAF(@"SEL$2")]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[OUTLINE_LEAF(@"INS$1")]', q'[OUTLINE(@"SEL$7")]', q'[OUTLINE(@"SEL$5B8A663B")]', q'[MERGE(@"SEL$6A1B73E7")]', q'[OUTLINE(@"SEL$6")]', q'[OUTLINE(@"SEL$6A1B73E7")]', q'[MERGE(@"SEL$37633EB5")]', q'[OUTLINE(@"SEL$5")]', q'[OUTLINE(@"SEL$37633EB5")]', q'[MERGE(@"SEL$3")]', q'[OUTLINE(@"SEL$4")]', q'[OUTLINE(@"SEL$3")]', q'[FULL(@"INS$1" "ACCUM_DTL_OUT"@"INS$1")]', q'[NO_ACCESS(@"SEL$1" "ACCUM"@"SEL$1")]', q'[NO_ACCESS(@"SEL$2" "from$_subquery$_003"@"SEL$2")]', q'[GBY_PUSHDOWN(@"SEL$2")]', q'[USE_HASH_AGGREGATION(@"SEL$2")]', q'[FULL(@"SEL$A1BD89B9" "CDLM"@"SEL$5")]', q'[INDEX(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6" ("ACCUM_BUCKETS"."IS_INBOUND" "ACCUM_BUCKETS"."ACC_TYPE" "ACCUM_BUCKETS"."ACAC_ACC_NO"))]', q'[INDEX(@"SEL$A1BD89B9" "CDML"@"SEL$3" ("CMC_CDML_CL_LINE"."CLCL_ID" "CMC_CDML_CL_LINE"."CDML_SEQ_NO"))]', q'[FULL(@"SEL$A1BD89B9" "MEME"@"SEL$4")]', q'[INDEX(@"SEL$A1BD89B9" "CLCL"@"SEL$3" ("CMC_CLCL_CLAIM"."CLCL_ID"))]', q'[LEADING(@"SEL$A1BD89B9" "CDLM"@"SEL$5" "BUCKETS"@"SEL$6" "CDML"@"SEL$3" "MEME"@"SEL$4" "CLCL"@"SEL$3")]', q'[USE_HASH(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[USE_NL(@"SEL$A1BD89B9" "CDML"@"SEL$3")]', q'[NLJ_BATCHING(@"SEL$A1BD89B9" "CDML"@"SEL$3")]', q'[USE_HASH(@"SEL$A1BD89B9" "MEME"@"SEL$4")]', q'[USE_NL(@"SEL$A1BD89B9" "CLCL"@"SEL$3")]', q'[NLJ_BATCHING(@"SEL$A1BD89B9" "CLCL"@"SEL$3")]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6" NONE BROADCAST)]', q'[PX_JOIN_FILTER(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "CDML"@"SEL$3" NONE BROADCAST)]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "MEME"@"SEL$4" BROADCAST NONE)]', q'[PX_JOIN_FILTER(@"SEL$A1BD89B9" "MEME"@"SEL$4")]', q'[PQ_DISTRIBUTE(@"SEL$A1BD89B9" "CLCL"@"SEL$3" NONE BROADCAST)]', q'[SWAP_JOIN_INPUTS(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[GBY_PUSHDOWN(@"SEL$A1BD89B9")]', q'[USE_HASH_AGGREGATION(@"SEL$A1BD89B9")]', q'[PARTIAL_JOIN(@"SEL$A1BD89B9" "BUCKETS"@"SEL$6")]', q'[END_OUTLINE_DATA]'); --Need to add this :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt); DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( sql_text => sql_txt, profile => h, name => 'mpc_f2p5qthszvxqj_593130478', -- name of profile mpc_sqlid_PlanHashValue description => 'mpc f2p5qthszvxqj 593130478 ' || :signature || '', category => 'DEFAULT', VALIDATE => TRUE, REPLACE => TRUE, force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); END; / WHENEVER SQLERROR CONTINUE SET ECHO OFF; SELECT TO_CHAR (:signature, '999999999999999999999') signature FROM DUAL;
After running the above code, SQL Profile is now being used for this query.
So that is how using AWR Warehouse, you can bring a plan from graveyard back to life 🙂