Tags

, ,

1- Traceing the problemtic SQL Statement
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
SELECT .......;
--->> Beware of using bind variables for the performance, (i.e. :B1)
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

--Control Scripts
select sql_handle, plan_name, sql_text, enabled, accepted, fixed,enabled,optimizer_cost,created from dba_sql_plan_baselines where creator ='USER' order by last_modified desc
select * from dba_sql_plan_baselines where creator ='USER' order by last_modified desc
--SQL_Hnadle: SQL_f28e653f5834f8e7 - Plan_Name: SQL_PLAN_g53m57xc39y77cd106f7c

--We can see that the plan was created.
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_f28e653f5834f8e7'));

2-Create a staging table and than import the healty execution plan.
exec DBMS_SPM.CREATE_STGTAB_BASELINE('STGTAB', 'USER');

--insert the healty execution plan on the staging table.
var x number;
begin
:x := DBMS_SPM.PACK_STGTAB_BASELINE('STGTAB', 'USER', sql_handle => 'SQL_f28e653f5834f8e7', plan_name => 'SQL_PLAN_g53m57xc39y77cd106f7c' );
end;
/

3- Export / Import Ops.
expdp \'/ as sysdba\' DIRECTORY=EXP_DIR DUMPFILE=spbtest.dmp LOGFILE=spbtest.log tables=USER.STGTAB
impdp \'/ as sysdba\' DIRECTORY=EXP_DIR DUMPFILE=spbtest.dmp LOGFILE=spbtest.log tables=USER.STGTAB

4-Unpack the healty execution plan on the destination DB.
var x number;
begin
:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB', 'USER');
end;
/

--Now fix and accept the newly migrated sql plan and do not forget to maek autopurge as 'NO'.

SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_f3cbd6cd1d671659',
plan_name => 'SQL_PLAN_g7kyqtnfqf5ktd5c592a9',
attribute_name => 'FIXED', --ENABLED,ACCEPTED,AUTOPURGE,ADAPTIVE,FIXED
attribute_value => 'YES');

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped :=DBMS_SPM.drop_sql_plan_baseline(sql_handle => NULL,plan_name => 'SQL_PLAN_g7kyqtnfqf5kta3ffd38f');

DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

Advertisements