Photo by Einar Storsul on Unsplash

Share the post "Apply Execution Plan From Graveyard"

  • Facebook
  • X

….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 🙂

Share the post "Apply Execution Plan From Graveyard"

  • Facebook
  • X
, ,

Related Posts

Oracle Database SQL Query performance

Enforce Execution Plan of “hinted” SQL To “non-hinted” SQL

Aamir
Oracle Database 12cR1 Oracle Database 19c

Unified Auditing

Aamir

Leave a Reply

Your email address will not be published. Required fields are marked *