Créer et exécuter une tâche SQL Tuning Advisor et lire ses recommandations

Commande

-- 1) Create and run a task for a SQL in the cursor cache:
VAR task VARCHAR2(64)
BEGIN
  :task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
             sql_id      => '<sql_id>',
             scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
             time_limit  => 300,
             task_name   => 'STA_<sql_id>',
             description => 'Slow report query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => :task);
END;
/
-- For a historical SQL in AWR: begin_snap => <b>, end_snap => <e>, sql_id => '<sql_id>'

-- 2) Status and report:
SELECT task_name, status FROM dba_advisor_log WHERE task_name = :task;

SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200 PAGESIZE 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task) FROM dual;

-- 3) Get the recommendations as an executable script:
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:task) FROM dual;

-- 4) When done:
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => :task);

Ce que fait la commande

Exécute l'optimiseur dans un "mode tuning" étendu pour le SQL et produit des recommandations sur les statistiques manquantes, de nouveaux index, un SQL profile, des plans alternatifs et la restructuration du SQL ; valide la recommandation de profile par des exécutions de test.

Quand l'utiliser

Lorsqu'un SQL problématique isolé nécessite un second avis rapide, ou pour trouver une correction par SQL profile à une régression de plan sans modifier le code.

Attention

Exige la licence Tuning Pack. La portée COMPREHENSIVE peut exécuter le SQL pour le tester (seulement la partie requête pour les DML) ; définissez time_limit en production. Les recommandations d'index ignorent le reste de la charge ; évaluez séparément le coût des DML. L'utilisateur a besoin du privilège ADVISOR.