SQL Plan Baseline

Display Current SQL Plan 11g

=====================

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

Display using SQL Handle

==================

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_c0dccb1dc5650094'));

Query SQL Plan Baseline

==================

select sql_handle, plan_name, enabled, accepted, CREATED from dba_sql_plan_baselines

order by created;

select sql_handle, sql_text, plan_name, enabled, accepted, CREATED from dba_sql_plan_baselines order by created;

Drop SQL Plan Baseline

=================

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => 'SQL_6903a7554f462647',

plan_name => NULL);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

Enable / Disable SQL Plan Baseline

=========================

SET SERVEROUTPUT ON

DECLARE

l_plans_altered PLS_INTEGER;

BEGIN

l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

sql_handle => 'SQL_01674a9b1336e780',

plan_name => 'SQL_PLAN_02tuamc9mdtw0f87642b6',

attribute_name => 'enabled',

attribute_value => 'NO');

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

END;

/