Appunti sul funzionamento di CBO in Oracle

mercoledì 25 giugno 2008 alle 25:59 | Pubblicato su Performance Tuning | 3 commenti
Tag: , ,

Ieri ho deciso di organizzare decentemente i miei feed preferiti (si dira così?). In sostanza ho cominciato ad utilizzare Google Reader, mentre prima utilizzavo “My Yahoo“, utilizzando un modulo per ogni feed RSS. Il motivo principale per cui ho iniziato tale migrazione è perchè My Yahoo non mi accettava l’iscrizione al feed RSS di Christian Antognini. Poi mi sono accorto che effettivamente Google Reader funziona proprio bene e allora ho cominciato ad aggiungere iscrizioni. Durante questa operazione ho aggiunto anche http://structureddata.org e casualmente sono ricapitato sull’ennesimo articolo/post sull’argomento ottimizzatore Oracle CBO, bind peeking, istogrammi (“Choosing an optimal stats gathering strategy“). Quello che faccio ancora fatica  a digerire è la tesi, sostenuta da molti, che uno dei problemi di CBO sia la convivenza tra bind peeking e istogrammi.

Sul tema bind peeking e istogrammi e CBO in generale ho scritto diverse cose, ho letto molto, ma comincio ad avere un po’ di confusione in testa, quindi oggi scrivo l’ennesimo post sull’argomento. Probabilmente ripeterò cose già scritte e non escludo di smentire cose che ho già scritte, per questo spero che qualcuno mi aiuti.

Il motivo principale per cui non digerisco la tesi secondo cui bind peeking e istogrammi non possono coesistere e producono effetti negativi sulle scelte dell’ottimizzatore è il fatto che mi sono trovato proprio di fronte a un caso che smentisce tale tesi. Ne ho gia parlato e recentemente ho riscontrato un’altro caso e grazie all’utilizzo di DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>’XXX’,FORMAT=’BASIC +peeked_binds) ho scoperto che il problema dipende dal fatto che, forse a causa di qualche baco, talvolta non viene effettuato il “bind peeking”. Avendo una distribuzione dei dati particolare CBO, in assenza di un valore per le bind variables genera un piano di esecuzione che predilige un full scan piuttosto che l’uso di un indice. Viceversa, quando il bind peeking avviene, CBO, grazie agli istogrammi sa che il valore passato è molto selettivo e quindi usa l’indice (correttamente nel mio caso).

Mentre mi sono imbattuto in tale post stavo facendo alcuni test sulla base di una cosa che ho visto in un thread di discussione sul gruppo USENET CDOS. Mi è tornato in mente una interessante discussione nata sul post di Doug Burns “How useful are diagnostic/optimization tools? Another view”, in particolare in tale discussione è intervenuto Jonathan Lewis che ha ricordato come per default gli istogrammi vengono generati per le colonne che vengono usate nella clausola where delle query e questa informazione viene registrata da Oracle sulla tabella SYS.COL_USAGE$.

Il mio test ieri sera dava un risultato anomalo, perchè facevo una prova di query con una precisa colonna nella condizione di where, poi rilanciavo il calcolo delle statistiche ma l’istogramma non veniva generato come mi aspettavo. Poi ieri sera ho lasciato le cose li e quando stamattina ho ripreso il test ho notato che l’istogramma era stato generato come mi aspettavo. Allora ho ricominciato il test dall’inizio ricreando la tabella e andando a vedere il contenuto di COL_USAGE$, al che ho capito che la tabella non viene popolato in modo sincrono e quindi ho cominciato a fare alcune ricerche al riguardo. Ho trovato qualche documento interessante, fra qui questo, di  David Litchfield, in cui si dice che la tabella, in Oracle 10gR2 viene popolata da SMON ogni 20 minuti. Quindi, (non ho fatto un test preciso ma mi fido) possono passare al massimo 20 minuti, prima che una colonna usata una clausola WHERE di uno statement di SELECT possa venire registrata su tale tabella; in tal caso se successivamente vengono raccolte le statistiche con il parametro METHOD_OPT al suo valore di default (‘FOR ALL COLUMNS SIZE AUTO’) per tale colonna viene generato un istogramma.

Un’altra informazione interessante che ho trovato su ORACLE-L è la procedura dbms_stats.flush_database_monitoring_info() (in effetti per questa bastava studiare bene i manuali Oracle). Questo mi ha aiutato ad accelerare i miei test, senza dover aspettare i fatidici 20 minuti si SMON. Sempre nella stessa ricerca mi sono imbattuto in uno script di Tim Gorman, per creare la vista DBA_COLUMN_USAGE che potrabbe tornare utile in test come quelli che ho fatto io.

I miei test proseguono sul comportamento dei metodi DBMS_STATS.GATHER_*_STAT a seconda del valore del parametro METHOD_OPT. In particolare non ho capito la differenza tra method_opt=>’FOR ALL COLUMNS SIZE SKEWONLY’ e method_opt=>’FOR  COLUMNS SIZE SKEWONLY’, ma per questo dovrò fare ulteriori ricerche e studi.

Questo post, come molti altri da me scritti, è un po’ così, come dire, confuso. Questo perchè rispecchia molto bene il mio stato mentale attuale (sarà il caldo). Il fatto è che volevo salvare da qualche parte e cercare di riorganizzare i risultati dei miei test e delle mie ricerche. D’altra parte è il motivo principale che mi ha spinto a iniziare (ormai tanto tempo fa) a scrivere su questo blog.  Spero di far seguire a questo un’altro post più chiaro e con ulteriori informazioni.

3 commenti »

RSS feed dei commenti a questo articolo. TrackBack URI

  1. Sarà confuso come dici, ma questo tuo post mi ha fatto venire voglia di mandare al diavolo progetti e clienti e gettarmi nello studio approfondito di questo accidenti di CBO (come di altre parti del prodotto a me praticamente sconosciute).

    Peccato non poterlo fare….

  2. Ciao,
    ho letto il tuo post e i link che hai inserito. Ho quindi provveduto ad effettuare un po di test ma mi sono rimasti alcuni aspetti da chiarire definitivamente:

    1) – “peeking binds”, ovvero la registrazione del valore attribuito alle bind, avviene solo se vi sono istogrammi calcolati per l’oggetto?

    2) – _cursor_bind_capture_interval=”interval (in seconds) between two bind capture for a cursor”, ma io vedo che le bind sono “catturate” immediatamente (non devo attendere 15 minuti), forse non mi è chiaro il senso di “interval between two bind capture”?
    es.:
    — prima esecuzione
    variable a number;
    exec :a := 10;
    select count(*) from t where x a questo punto vedo correttamente Peeked Binds
    1 – :A (NUMBER): 30

    — seconda esecuzione
    exec :a := 20;
    select count(*) from t where x a questo punto vedo Peeked Binds
    1 – :A (NUMBER): 30
    ma non è quanto ho impostato, ma è il valore precedente…centra qualcosa?

  3. Ciao Sandro,
    1) il bind peeking avviene indipendentemente dalla presenza di istogrammi. Se la distribuzione è non uniforme (skew) e non ci sono istogrammi probabilmente il bind peeking serve a poco però avviene comunque.
    2) il bind capture è una cosa diversa dal bind peeking, a questo riguardo ti dovrebbe chiarire il post di Jonathan Lewis bind-capture, in particolare il commento numero 8. In pratica il bind capture non influenza CBO (almeno credo, non c’è evidenza di ciò). Sia chiaro, mi riferisco a 10g, in 11g potrebbe esserci qualche differenza.


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 )

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 )

Google+ photo

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

Connessione a %s...

Blog su WordPress.com. | The Pool Theme.
Entries e commenti feeds.

Iscriviti

Ricevi al tuo indirizzo email tutti i nuovi post del sito.

Unisciti agli altri 71 follower

%d blogger cliccano Mi Piace per questo: