SQL Plan Management e SQL Plan Baselines

venerdì 4 maggio 2018 alle 04:20 | Pubblicato su 12c, Diario, Performance Tuning | Lascia un commento
Tag:

Con la versione 11.1 Oracle ha sostituito il meccanismo chiamato “Stored Outlines” con uno nuovo chiamato “SQL Plan Baselines”. L’obiettivo è rimasto lo stesso, quello di prevenire il peggioramento delle prestazioni dovuto a cambiamenti di sistema, di versione dell’ottimizzatore o altro che possa far cambiare il piano di esecuzione scelto per una query SQL. Evidentemente le stored outlines erano limitate rispetto alle nuove “SQL Plan Baselines” tanto appunto da richiedere una cosa molto più sofisticata che quindi ha preso un nuovo nome. Cercherò di riportare una sintesi di quanto riporta il manuale “Oracle 12.1 Database SQL Tuning Guide”, capitolo 23: Managing SQL Plan Baselines” Posso dire che a suo tempo ho studiato le Stored Outlines ma non sono mai riuscito ad utilizzarle perché non si adattavano alle esigenze che avevo. Le SQL Plan Baselines le ho esplorate da poco e non potrò per ora portare esperienze pratiche, mi limiterò quindi a quanto trovo sul manuale.

SQL Plan Baseline

Una SQL plan baseline (SPB) è un insieme di piani accettati che l’ottimizzatore è autorizzato ad utilizzare per uno statement SQL. Tipicamente i piani vengono introdotti in una SQL plan baseline dopo che è stato verificato che non portano a maggior uso di risorse con conseguente peggioramento delle prestazioni. Questo sistema si affianca al meccanismo dei “SQL Profile”, generati dal SQL Tuning Advisor che però sono un meccanismo reattivo, mentre l’SPB vuole essere un meccanismo proattivo: ha lo scopo di prevenire situazioni di peggioramento di prestazioni. Questo nel quadro di un aggiornamento di versione Oracle, di cambiamento di parametri o di cambiamento dell’hardware sottostante. All’interno di una SQL plan baseline ogni piano è specificato usando un insieme di “outline hints” che lo definiscono completamente. Questo si contrappone a SQL Profiles che piuttosto specificano solo degli hint che cercano di correggere errate stime sui costi del piano e quindi non forzano l’uso di un preciso piano.

SQL Plan Capture

La gestione dei piani di esecuzione tramite le SQL Plan baseline avviene in diversi passi, il primo di questi è la cattura e il salvataggio di tutte le informazioni sui piani di esecuzione di un insieme di statement SQL.  Le informazioni vengono salvate nella “SQL Management Base” (SMB). La cattura dei piani può avvenire in modo automatico tramite il settaggio del parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a “true” (per default è “false”). Quando il parametro è a true il database crea una SQL plan baseline per ogni statement SQL “ripetibile” eseguito sul database stesso.

La cattura manuale avviene tramite procedure del package PL/SQL DBMS_SPM (DBMS_SPM.LOAD_PLANS_FROM_%) e consiste nel caricamento o da un “SQL tuning set (STS)” o dalla “share SQL area” o da una “tabella di staging” o da una “Stored Outline”. Non posso non riportare una immagine dal manuale che mi sembra molto chiara e completa:

Un piano caricato manualmente viene salvato come “accettato”

SQL Plan Selection

Come si vede dalla figura sopra nella SQL Management Base viene salvato anche la storia di un piano di esecuzione, cioè vengono registrati i cambi di piano

Quando il database esegue un “hard parse” di uno statement SQL l’ottimizzatore genera un piano ottimale per costo. Per default poi l’ottimizzatore cerca un piano che corrisponda nella SQL plan baseline di quello statement. Se non trova una SQL plan baseline per quello statement usa il piano gia generato (crea la SPB e aggiunge il piano come “accepted”), se la trova e il piano generato è gia nella baseline allora lo usa, se non c’è lo aggiunge alla “plan history” marcato come “unaccepted”. In quest’ultimo caso, se esistono piani fissati viene usato quello con il costo minore, se non esistono piani fissati viene usato il piano nella baseline con il costo minore, se non esistono piani riproducibili nella baseline (ad esempio perché nel frattempo è stato rimosso un indice) allora l’ottimizzatore usa il nuovo piano generato. Un piano fissato è un piano accettato e marcato manualmente come preferito.

SQL Plan Evolution

L’evoluzione di un piano è il procedimento che consiste nel verificare che i piani non accettati abbiano pretazioni almeno pari a quelli gia accettati (SQL Plan verification) e in tal caso nella loro aggiunta ai piani accettati della baseline. È possibile configurare il SQL Plan management affinché vengano eseguite tutte le possibili combinazioni dei due passi, cioè si può fare la verifica del piano senza l’aggiunta alla baseline e si può fare l’aggiunta senza fare la verifica. Il package PL/SQL DBMS_SPM fornisce le seguenti procedure:

  • SET_EVOLVE_TASK_PARAMETER
  • CREATE_EVOLVE_TASK
  • EXECUTE_EVOLVE_TASK
  • REPORT_EVOLVE_TASK
  • IMPLEMENT_EVOLVE_TASK
  • ACCEPT_SQL_PLAN_BASELINE

Queste procedure servono per avviare manualmente il processo di evoluzione. Oracle comunque raccomanda di lasciare al task automatico SYS_AUTO_SPM_EVOLVE_TASK (introdotto in Oracle 12c) il compito di fare il lavoro di verifica e accettazione durante la finestra di manutenzione pianificata (normalmente ogni notte)

SQL Management Base (SMB)

Si tratta della struttura logica dove vengono salvate le informazioni relative alle SQL plan baseline, fisicamente stanno nella tablespace SYSAUX. Le componenti sono quattro:

  • SQL statement log
  • SQL plan history (che include le sql plan baseline)
  • SQL profiles
  • SQL patches

SQL statement log

Durante il “parse” dello statement oracle calcola una “firma” (SQL signature)  normalizzando rispetto a maiuscole e minuscole e a spazi il testo, quando avviene la cattura automatica viene cercata la firma all’interno del “SQL statement log” (SQLLOG$), se non c’è l’aggiunge se c’è lo statement si può definire un “repeated SQL statement”

SQL Plan History

si tratta dell’insieme di piani di esecuzione catturati,  si quelli accettati, quindi nelle SQL plan baseline che quelli non accettati. Dalla versione 12c viene salvato il piano intero per tutti i piani, in questo modo non deve essere ricompilato lo statement per rigenerare il piano. I piani possono essere abilitati (enabled), che è il default o no. Nel caso non siano abilitati non vengono utilizzati anche se accettati.

La SQL Plan history può essere analizzata tramite la vista DBA_SQL_PLAN_BASELINES, tramite la procedure DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE è possibile vedere il dettaglio di una SQL plan baseline.

 

Oltre al parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES citato prima c’è un’altro parametro OPTIMIZER_USER_SQL_PLAN_BASELINES che per default è impostato a true e fa si che comunque per gli statement che hanno gia SQL plan baseline esistenti vengano aggiunti nuovi piani come non accettati. Se viene impostato a false viene disabilitato completamente l’uso delle baseline.

Sul manuale si trovano ulteriori dettagli ed esempi sulla gestione che non sto qui a riportare per non appesantire troppo il post

Annunci

Lascia un commento »

RSS feed for comments on this post. TrackBack URI

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

w

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger hanno fatto clic su Mi Piace per questo: