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.