Plan einer SQL Plan Baseline fixieren, deaktivieren oder löschen

Befehl

-- 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;
/

Was es tut

Verwaltet Attribute von Baseline-Plänen: Ein FIXED-Plan wird gegenüber anderen akzeptierten Plänen bevorzugt und stoppt die automatische Evolution neuer Pläne; ENABLED=NO deaktiviert einen Plan, ohne ihn zu löschen; DROP entfernt ihn dauerhaft.

Wann einsetzen

Um für eine kritische SQL einen Plan fest zu verankern, einen versehentlich akzeptierten schlechten Plan zu deaktivieren oder nicht mehr benötigte Baselines zu bereinigen.

Vorsicht

Erfordert die Enterprise Edition. FIXED-Pläne können mit wachsenden Daten schlechter werden und verhindern, dass der Optimizer einen besseren Plan nutzt; überprüfe sie regelmäßig. Eine SQL, deren Pläne alle ENABLED=NO sind, wird frei optimiert. DROP ist unumkehrbar; sichere vorher mit DBMS_SPM.PACK_STGTAB_BASELINE in eine Staging-Tabelle.