Fix, disable or drop a SQL plan baseline plan

Command

-- Show the plan content:
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
         sql_handle => '<sql_handle>',
         plan_name  => '<plan_name>',
         format     => 'TYPICAL'));

SET SERVEROUTPUT ON
DECLARE
  n PLS_INTEGER;
BEGIN
  -- FIXED: this plan is preferred, new plans are not evolved automatically
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle => '<sql_handle>', plan_name => '<plan_name>',
         attribute_name => 'fixed', attribute_value => 'YES');

  -- Take the bad plan out of use (without dropping it):
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle => '<sql_handle>', plan_name => '<bad_plan_name>',
         attribute_name => 'enabled', attribute_value => 'NO');
END;
/

-- Drop one plan or the SQL's entire baseline:
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '<sql_handle>', plan_name => '<plan_name>');
  -- without plan_name, all plans of the SQL are dropped:
  -- n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '<sql_handle>');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) dropped');
END;
/

What it does

Manages baseline plan attributes: a FIXED plan is preferred over other accepted plans and stops automatic evolution of new plans; ENABLED=NO disables a plan without deleting it; DROP removes it permanently.

When to use it

To pin a plan firmly for a critical SQL, to disable a bad plan that was accepted by mistake, or to clean up baselines that are no longer needed.

Caution

Requires Enterprise Edition. FIXED plans can degrade as data grows and prevent the optimizer from using a better plan; review them periodically. A SQL whose plans are all ENABLED=NO is optimized freely. DROP is irreversible; back up to a staging table with DBMS_SPM.PACK_STGTAB_BASELINE first.