Bad execution plans (Cattivi piani di esecuzione)

Le cose dette durante il seminario, di Tom Kyte sono state davvero incredibili ed alcune mi hanno colpito più di altre. Ad esempio: "quando Oracle sbaglia a calcolare la cardinalità delle righe allora sceglie un piano di esecuzione sbagliato". Coinciso e chiaro. Ovviamente una simile affermazione ha stimolato la mia curiosità e la sete di conoscenza ha fatto il resto. Cercando in rete, ho trovato sul blog di Kyte stesso un commento all'Hotsos Symposium 2006. Qui ne riporto un estratto:

That last one was Wolfgang Breitling?s talk. I really enjoyed that one for the simple reason it is a method I follow myself. The crux of the matter is ? you have a query that should perform better, you know it can. But the optimizer has generated the wrong plan (you know it did). Rather than just hinting it and fixing that one, sole query ? you can try to figure out "why" (and hence fix an entire class of poorly performing queries in the process, instead of tuning by hand every single one). The premise: bad query plans result from incorrect cardinality estimates. If you want to "tune a query", look to the estimated cardinality values (explain plan or v$sql_plan) and compare them to the actuals (row source operation in a TKPROF report, v$sql_plan_statistics). His approach, one I share, is to do that and then try to come up with a way to "fix it" ? to get the optimizer to achieve the correct cardinality. It might be optimizer dynamic sampling, it might be setting/gathering more detailed statistics ? but it will fix not only that particular query but an entire class of queries that might suffer from the same issue. I really liked this one.

Da ciò possiamo trarre alcune interessanti ed utili informazioni:

• Per tunare una query dobbiamo valutarne il piano di esecuzione e la cardinalità delle righe risultanti
  

Per farlo abbiamo a disposizione (a partire da Oracle 9i) due nuove viste: V$SQL_PLAN e V$SQL_PLAN_STATISTICS. Queste, contengono il piano di esecuzione e le relative statistiche dello statement quando è stato eseguito. E la cosa è molto importante. Infatti, quando affrontiamo problemi di tuning, non sappiamo in che modo l'ottimizzatore ha valutato una query nell'istante in cui l'ha eseguita. Utilizzando queste due viste di sistema invece possiamo valutare le scelte del CBO.

• In linea di massima, per fissare una classe di query che soffrono di uno stesso problema è sufficiente raccogliere le statistiche.

Il collezionamento delle statistiche è cruciale: statistiche mancanti o vecchie confondono l'ottimizzatore e spesso portano alla scelta sbagliata del piano di esecuzione: occorre valutare ad esempio se è il caso o meno di utilizzare gli histogrammi affinché la cardinalità stimata sia la più corretta possibile.

A partire dalla versione 10, l'ottimizatore RBO è stato desupportato. Per taleo motivo, le statistiche vengono collezionate in automatico. Il parametro OPTIMIZER_DYNAMIC_SAMPLING influenza il modo in cui il CBO si comporta. Di seguito riporto i valori di tale parametro per le versioni a partire da Oracle 9iR1 (non esiste un corrispettivo per release precedenti), mentre consiglio il seguente link per maggiori informazioni.

OPTIMIZER_FEATURES_ENABLE	OPTIMIZER_DYNAMIC_SAMPLING
>= 10.0.0	2
9.2.0	1
9.0.1	0

Relativamente all'ottimmzatore CBO, il mio suggerimento è per due siti: i loro autori sono dei veri eseperti in materia ed i loro documenti sono dei veri casi di studio.

Wolfgang Breitling
Gli articoli che ha scritto sono davvero interessanti. Uno di questi, A Look under the Hood of CBO: The 10053 Event, descrive come interpretare le decisioni del CBO.

Jonathan Lewis
A parte che l'intero sito è una miniera di informazioni su tutto ciò che riguarda Oracle, l'autore ha srcitto un libro, Cost Based Oracle: Fundamentals, incentrato tutto sul CBO.

2 giorni con Tom Kyte...

Credo fosse estate o giù di lì quando lessi che Tom Kyte sarebbe venuto a Roma per tenere il suo primo seminario in Italia. Ricordo che come un bambino, ero tutto emozionato all'idea di poter seguire personalmente l'autore di ben 3 libri incredibili (tutti acquistati ovviamente).

Dovevo assolutamente partecipare. Ed infatti mossi mari e monti per cercare di non mancare l'appuntamento. Come a scuola, seduto nell'ultima fila ero armato di auricolari per ascoltare la traduzione in italiano (già, purtroppo ho questa mancanza: l'inglese): non mi sono perso una sola parola di quanto il buon Tom ha detto.

Ma ovviamente dovevo fare qualcosa. Dovevo segnare quel giorno e così alla fine della seconda giornata ho fatto 3 domande.

* Domanda. Come capire se soffro di "Write Consistency" (un effetto collaterale della "lettura consistente" che Oracle mette a disposizione)?
* Risposta. Non esistono particolari tecniche che consentono di individuare se la nostra applicazione è sotto l'effeto del "Write Consistency". L'unico suggerimeto è quello di lanciare una prima volta lo statement dopo aver messo sotto trace la nostra sessione e leggere i corrispondenti valori di "query" e "current" dall'output del file prodotto. Lanciare quindi una seconda volta lo stesso statement e ricontrollare il file di trace prodotto. Se siamo nelle condizioni di "Write Consistency", allora vedremo il valore "current" molto maggiore rispetto al caso precedente. E dovremmo basarci solo sul valore di "current" e non quello di "query", perché i blocchi letti in modo consistente in questo contensto non fanno testo (di fatto possiamo dire che il 95% delle letture sul db avviene dagli undo segment)

* Domanda. La seconda invece è stata relativa ad una cosa che avevo letto sul libro "Effective Oracle by Design". Non so se ricordate, ma ad un certo punto Kyte dimostra che spostare il codice di un trigger in una stored procedure (meglio in un package ovviamente) porta ad un guadagno in performance. Non dice tuttavia il perché di questo miglioramento. Quale occasione migliore della sua stessa presenza per avere una risposta?
* Risposta. Ebbene, ciò che succede è che Oracle esegue il cache del codice PL/SQL delle stored procedure (e quindi dei package) ma non di quello dei trigger. Spostando quindi la logica fuori dal trigger, consentiamo al db di effettuare meno parsing, guadagnando quindi in performance. Questo spostamento del codice fuori dal trigger non sarà più necessario a partire dalla versione 11g in quanto Oracale effetuerà anche il caching del codice PL/SQL di un trigger.

* Domanda. Ed infine una cosa che dovevo assolutamente sapere: quando uscirà il suo prossimo libro.
* Risposta. Purtroppo sembra che non lo abbia ancora finito di scrivere. Ci tocca quindi aspettare.

Ma non basta. Mi sono fatto firmare anche gli ultimi due libri (se riuscivo a trovare quello su 8i, mi sarei fatto autografare anche quello) che aveva scritto.

Insomma un evento eccezionale a cui non potevo assolutissimamente mancare. Che figata. Pensate, da anni seguo ciò che scrive sul suo sito e leggo gli aricoli che scrive su Oracle Magazine ed adesso addirittura ho seguito un suo seminario.

Wow.

Un commento sul corso. La struttura è stata di tipo standard:

Giorno 1
========
1. The Tools I Use
2. All About Binds
3. SQL Techniques
4. Read and Write Consistency

Giorno 2
========
1. Effective Schema
2. 10gR1
3. 10gR2

Come già detto è stato molto bello e direi anche emozionante (almeno per me). La seconda giornata però, mi duole ammetterlo, ha disatteso le mie aspettative. Si è parlato fondamentalmente delle nuove caratteristiche della 10g: utile sicuramente, ma un corso di "New Features" lo possono sostenere tutti. Lui, Tom, poteva puntare in alto anche il secondo giorno ed invece si è limitato a descriverci cosa è stato introdotto nell'ultima release del db e come sfruttare tale caratteristiche.

...e adesso posso dire....io c'ero!!!!! Il prossimo appuntamento, se ci riesco, è il 6-7 Febbraio 2006 a Milano per ascoltare e seguire un'altro mostro sacro: Jonathan Lewis. Quì il link dell'agenda.

Il materiale, slide e codice di esempio, lo si trova sul suo sito nella sezione "Files": il titolo è Denmark_Italy.zip. Spero presto di scrivere presto un resoconto di queste due indimenticabili ed incredibili giornate sul mio sito web.

L'evoluzione

Un articolo molto interessante su OakTable descrive come Oracle si sia evoluto a partire dalla sua prima versione (V2.3).

Il sottoscritto ha visto tuttavia solo le ultime modifiche e devo dire che se guardo indietro, la vecchia 8.1.5 mi sembra davvero un dinosauro. Lavoro ancora con la 8i (aggiornata a 8.1.7.4) e sento la mancaza di molte delle caratteristiche presenti in 9i come le EXTERNAL TABLES.

Anche se ormai sono diversi anni che non lavoro più come DBA, ho seguito la crescita del db sempre dal punto di vista dell'amministratore, ed effettivamente i cambiamenti sono stati sostanziali. L'idea che mi sono fatto è che così come la 8i ha rappresentato un salto generazionale rispetto ad oracle 7.3 ed 8, la 10g lo è stato per 8i e 9i.

Studiando quali sono state le modifiche introdotte con l'ultima release del db mi sono reso conto come il tema "Performance & Tuning" abbia subito un radicale e profondo cambiamento: a partire da 10g credo si possa dire che sia cambiata la filosofia di approccio.

Tutto è cominciato da YAPP
il documento scritto da Anjo Kolk, che di fatto fornisce uno strumento su come affrontare i problemi di performance. Diventa allora famosa l'espressione

Response Time = Service Time + Wait Time

in cui l'obiettivo è cercare di ridurre il tempo di risposta. (Su tale tema, Cary Millsap e Jeff Holt hanno scritto un libro davvero bello). Con 8i, Oracle introduce statspack, che con l'aiuto del sito di Kolk, diventa uno strumento davvero utile per individuare i problemi di performance. Con 9i, Oracle subisce una spinta in avanti non indifferente. Dapprima viene introdotto il timing in microsecondi e riportato il timing in V$SQL e poi, in 9iR2, statspack si modifica riportando la statistica "CPU Time" e la dicitura "Top Wait Events" diventa "Top Timed Events", ad indicare come sempre più l'obbiettivo di analisi sia orientato nel cercare di ridurre il tempo di risposta.

E poi arriva Oracle 10g. Viene introdotto AWR che di fatto è uno statspack direttamente dentro il kernel dell'rdbms. C'è ASH, che permette di capire per sommi capi, i potenziali problemi. Ma soprattutto conosciamo ADDM, l'inteliggenza che ci aiuta a capire dove sono i problemi nel db. Sostanzialmente adesso se vogliamo sapere dove il db langue, basta tirare giù un report di ADDM per capire dove dobbiamo indirizzare i nostri sforzi. Ma non solo. ADDM suggerise anche che fare.

WOW.