Fixer, désactiver ou supprimer un plan de SQL plan baseline

Commande

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

Ce que fait la commande

Gère les attributs des plans de baseline : un plan FIXED est préféré aux autres plans acceptés et arrête l'évolution automatique des nouveaux plans ; ENABLED=NO désactive un plan sans le supprimer ; DROP le supprime définitivement.

Quand l'utiliser

Pour figer fermement un plan pour un SQL critique, désactiver un mauvais plan accepté par erreur, ou nettoyer les baselines devenues inutiles.

Attention

Exige Enterprise Edition. Les plans FIXED peuvent se dégrader avec la croissance des données et empêcher l'optimiseur d'utiliser un meilleur plan ; revoyez-les périodiquement. Un SQL dont tous les plans sont ENABLED=NO est optimisé librement. DROP est irréversible ; sauvegardez d'abord dans une table de staging avec DBMS_SPM.PACK_STGTAB_BASELINE.