08 - bis) SQL Tuning Advisor

Essendo il capitolo su Automatic Database Management piuttosto lungo, spezzo il riassunto/traduzione in due articoli e in questo articolo (o post?) metto la parte riguardante l'utilizzo del SQL Tuning Advisor. (manuale: concepts e Performance Tuning Guide )

SQL Tuning Advisor implementa la funzionalit definita come "Automatic SQL Tuning". Per realizzare questa funzionalit si lavorato sull'ottimizzatore SQL di Oracle, aggiungendo una "modalit estesa" che viene chiamata "Automatic Tuning Optimizer".

Normamente l'ottimizzatore SQL di Oracle, quella componente che si occupa della generazione dei piani di esecuzione, lavora in modalit "normale", quindi genera in tempi rapidi dei piani di esecuzione che nella gran parte dei casi sono buoni. Quando l'ottimizzatore lavora in modalit "Tuning" viene chiamato Automatic Tuning Optimizer (ATO) e lavora in modo da privilegiare la ricerca di un piano di esecuzione ottimale utilizzando molte pi risorse mettendo in secondo piano il tempo di elaborazione. Questo significa che l'elaborazione in questa modalit pu richiedere secondi o minuti, contro al massimo qualche decimo di secondo che in modalit normale pu richiedere.

In realt quello che ATO genera non un piano di esecuzione, ma una serie di suggerimenti, con script per la loro implementazione, con spiegazione delle motivazioni e stime sui guadagni di prestazioni.

La modalit con cui viene realizzata la funzionalit di Automatic SQL Tuning distinta da quattro metodi:

Statistics Analysis : in sostanza l'ottimizzatore controlla che tutti gli oggeti coinvolti abbiano statistiche precise ed aggiornate.
SQL Profiling: in questa fase l'ottimizzatore raccoglie ulteriori statistiche, al fine di eliminare errori di approssimazione. Sulla base di informazioni di precedenti esecuzione elabora l'impatto di modifiche ai parametri (ad esempio OPTIMIZER_MODE=FIRST_ROWS ...). con queste informazione elabora dei SQL Profile, che sono informazioni che possono essere in seguito usate dall'ottimizatore in modalita normale per trovare il piano di esecuzione ottimale. (Il vantaggio di questo metodo che non occorre toccare gli statement gia scriitti)
Access Path Analysis: l'ottimizzatore valuta se possono essere aggiunti indici che migliorino l'accesso ai dati
SQL Structure Analysis:l'ottimizzatore analizza come scritto lo statement

L'interfaccia di accesso alle funzionalit di ATO SQL Tuning Advisor che pu lavorare su uno statement o su un gruppo di statement, chiamati SQL Tuning Set (STS).

L'uso di SQL Tuning Advisor pu partire su suggerimento di ADDM che ad ogni snapshot analizza il carico del database e identifica gli statement pi "pesanti" e per ognuno fornisce una raccomandazione di lanciare SQL Tuning Advisor. E' poi possibile decidere manualmente di lanciare SQL Tuning Advisor, in questo caso si pu partire da AWR o dal contenuto della cache (V$SQLAREA).

Come tutti gli altri advisor SQL Tuning Advisor pu lavorare in modalit "Limited" o "Comprehensive". Nel primo caso vengono utilizzati sono i metodi di analisy Statistics Analysis, Access Path Analysis e SQL structure analysis. Nella modalit comprehensive viene usato anche il metodo SQL Profiling..

Il risultato saranno una serie di "recommendations" con script per implementarle, con la "rationale" ovvero le motivazioni e la stima sul guadagno di prestazioni.

L'interfaccia di accesso a SQL Tuning Advisor e ovviamente EM Database Control, comunque esiste un package PL/SQL specifico: DB;S_SQLTUNE (qui c' una contraddizione all'affermazione che tutti gli advisor hanno una interfaccia di utilizzo comune nel package DBMS_ADVISOR).

L'utilizzao di SQL Tuning Advisor tramite il package DBMS_SQLTUNE un processo che si sviluppa in pi passi:

Creazione di un SQL Tuning Set (se si vogliono analizzare pi statement SQL che fanno parte di un unica procedura)
Creazione del SQL Tuning task
Esecuzione del SQL Tuning task
Visualizzazione dei risultati del SQL Tuning Task
Implementazione dei suggerimenti dove ritenuto appropriato

Vediamo nel dettaglio i singoli passaggi.

Creazione di un SQL Tuning Set

Un SQL Tuning Set un oggetto particolare, utilizzato dal package DBMS_SQLTUNE per raggruppare insieme pi statement SQL. Un STS pu essere creato a partire da un'altro STS, da AWR e dalla "cursor cache". A tale proposito il package prevede tre diverse funzioni di tipo "PIPELINED":

DBMS_SQLTUNE.SELECT_SQLSET
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
DBMS_SQLTUNE.SELECT_CURSOR_CACHE

Riporto dal manuale un esempio di creazione e caricamento di un STS:

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sql_tuning_set', 
    description  => 'I/O intensive workload');
END;
/

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  'peak baseline',
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set',
             populate_cursor => baseline_cursor);
END;
/

E questa un esempio di come visualizzare il contenuto di un STS:

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET( 
   'my_sql_tuning_set',
   '(disk_reads/buffer_gets) >= 0.75'));

Creazione di un SQL Tuning Task

La creazione di un SQL Tuning Task avviene mediante la procedura DBMS_SQLTUNE.CREATE_TUNING_TASK, il quale in realt defininito in quattro versioni (overloaded):

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  user_name        IN VARCHAR2  := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER   := NULL,
  scope            IN VARCHAR2 := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER   := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2 := NULL,
  description      IN VARCHAR2 := NULL)
RETURN VARCHAR2;

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  begin_snap      IN NUMBER,
  end_snap        IN NUMBER,
  sql_id          IN VARCHAR2,
  plan_hash_value IN NUMBER   := NULL,
  scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,
  time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,
  task_name       IN VARCHAR2 := NULL,
  description     IN VARCHAR2 := NULL)
RETURN VARCHAR2;

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sqlset_name       IN VARCHAR2,
  basic_filter      IN VARCHAR2 :=  NULL,
  object_filter     IN VARCHAR2 :=  NULL,
  rank1             IN VARCHAR2 :=  NULL,
  rank2             IN VARCHAR2 :=  NULL,
  rank3             IN VARCHAR2 :=  NULL,
  result_percentage IN NUMBER   :=  NULL,
  result_limit      IN NUMBER   :=  NULL,
  scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
  time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
  task_name         IN VARCHAR2 :=  NULL,
  description       IN VARCHAR2 :=  NULL
  plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
  sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

La prima versione permette la creazione del task direttamente dal testo dello statement SQL, la seconda versione dal suo SQL_ID, ovvero l'identificativo per localizzarlo nella cursor cache, la terza versione permette la crezione a partire da una coppia di snapshot di AWR ed infine la quarta versione permette la crezione di un task a partire da un SQL Tuning Set.

Riporto altri esempi sempre dai manuali:

variable stmt_task VARCHAR2(64);
variable sts_task  VARCHAR2(64);
 
-- Sql text format
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sql_text => 'select quantity_sold from sales s, times t 
   where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')');
 
-- Sql id format (cursor cache)
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24');
 
-- tune in limited scope 
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
   scope => 'LIMITED');
 
-- only give 10 minutes for tuning statement
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'ay1m3ssvtrh24', -
   time_limit => 600);
 
-- Workload repository format
exec :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, -
   end_snap => 2, sql_id => 'ay1m3ssvtrh24');
 
-- Sql tuning set format (first we need to load an STS, then tune it)
 
-- Tune our statements in order by buffer gets, time limit of one hour
-- the default ranking measure is elapsed time.
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sqlset_name  => 'my_workload', -
  rank1        => 'BUFFER_GETS', -
  time_limit   => 3600, -
  description  => 'tune my workload ordered by buffer gets');

Una volta creato il task esso sar visualizzato nella vista DBA_ADVISOR_LOG.

Esecuzione del SQL Tuning Task

tutto sommato la parte pi facile, si usa la procedura DBMS_SQLTUNE.EXECUTE_TUNING_TASK:

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/

Lo stato del task pu essere controllato attraverso le viste [DBA|USER]_ADVISOR_TASKS mentre lo stato di avanzamento visualizzabile oltre in V$SESSION_LONGOPS anche nella specifica vista V$ADVISOR_PROGRESS

Visualizzazione dei risultati del SQL Tuning Task

La procedura DBMS_SQLTUNE.REPORT_TUNING_TASK restituisce (in formato CLOB) un repporto dell'elaborazione del SQL Tuning Task:

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
  FROM DUAL;

Quindi riassumento i passi usare DBMS_SQLTUNE sono quattro (o cinque se si crea un STS):

DBMS_SQLTUNE.CREATE_TUNING_TASK
DBMS_SQLTUNE.EXECUTE_TUNING_TASK
DBMS_SQLTUNE.REPORT_TUNING_TASK
DBMS_SQLTUNE.SCRIPT_TUNING_TASK

Vi sono delle procedure per sospendere, riattivare, cancellare un task in esecuzione e per "dropparlo" .

DMBS_SQLTUNE.INTERRUPT_TUNING_TASK
DMBS_SQLTUNE.RESUME_TUNING_TASK
DMBS_SQLTUNE.CANCLE_TUNING_TASK
DMBS_SQLTUNE.RESET_TUNING_TASK
DMBS_SQLTUNE.DROP_TUNING_TASK

Le procedure del package sono divise in tre gruppi:

Tuning Task (quelli visti sopra)
SQL Profile: per la gestione dei SQL Profile
SQL Set: per la gestione dei SQL Tuning Set

Anche le viste di sistema per accedere ad alcune informazioni specifiche di questo advisor sono speciifiche:

DBA_SQLTUNE_STATISTICS
DBA_SQLTUNE_BINDS
DBA_SQLTUNE_PLANS
DBA_SQLSET
DBA_SQLSET_BINDS
DBA_SQLSET_STATEMENTS
DBA_SQLSET_REFERENCES
DBA_SQL_PROFILES

Che vanno ad aggiungersi alle altre viste comuni a tutti gli advisor:

DBA_ADVISOR_LOG
DBA_ADVISOR_TASKS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_RATIONALE
DBA_ADVISOR_ACTIONS (elenco delle azioni suggerite, collegate alle recommendations)
DBA_ADVISOR_COMMANDS (qui c' la descrizione di comandi che possono venire suggeriti)