Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

martedì, dicembre 19, 2006

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_ENABLEOPTIMIZER_DYNAMIC_SAMPLING
>= 10.0.02
9.2.01
9.0.10

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.

15 commenti:

Andrea Salzano ha detto...

Riporto quanto scritto nel manuale
10gR2 (Cap14)
.

14.2.3.1 When to Use Manual Statistics
Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. When Oracle encounters a table with no statistics, Oracle dynamically gathers the necessary statistics as part of query optimization. This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter, and this parameter should be set to a value of 2 or higher.

14.5.6 Estimating Statistics with Dynamic Sampling
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes.

14.5.6.1 How Dynamic Sampling Works
The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities.

14.5.6.4 Dynamic Sampling Levels
The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:

Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

Anonimo ha detto...

La ringrazio per Blog intiresny

Anonimo ha detto...

imparato molto

Anonimo ha detto...

La ringrazio per intiresnuyu iformatsiyu

Anonimo ha detto...

Condivido pienamente il suo punto di vista. Ottima idea, condivido.
E 'vero! Penso che questo sia una buona idea. Pienamente d'accordo con lei.

Anonimo ha detto...

La ringrazio per Blog intiresny

zesovela ha detto...

great post as usual!

paxil

Anonimo ha detto...
Questo commento è stato eliminato da un amministratore del blog.
Anonimo ha detto...

Just to let you know your site looks really weird in Opera on computer with Linux .

Andrea Salzano ha detto...

I'm sorry. I never tried tried the blog on opera browser and I don't know I can modify the html to change the output on opera. If you any idea, please tell me.

Anonimo ha detto...
Questo commento è stato eliminato da un amministratore del blog.
Anonimo ha detto...
Questo commento è stato eliminato da un amministratore del blog.
Anonimo ha detto...
Questo commento è stato eliminato da un amministratore del blog.
Anonimo ha detto...

HI, I just joined this community. I m from romania. I like this forum.......hope to learn lot of things here ;-)





------------------------------------------------------------------------
My Online Poker Blog Online Poker For Poker details

Andrea Salzano ha detto...

You are wellcome