Appunti sul funzionamento di CBO in Oracle – Parte II

giovedì 26 giugno 2008 alle 26:30 | Pubblicato su Performance Tuning | 10 commenti
Tag: , ,

Oggi ho proseguito i miei test per esplorare un po’ l’utilizzo ed il funziomento delle procedure GATHER_*_STATS del package DBMS_STAS. In particolare sono rimasto un po’ perplesso dai possibili valori del parametro METHOD_OPT. Più precisamente non riuscivo a capire la differenza tra: method_opt=>’FOR ALL COLUMNS SIZE SKEWONLY’ e method_opt=>’FOR  COLUMNS SIZE SKEWONLY’. Dopo  un paio di tentativi e di analisi ho capito che in pratica con method_opt=>’FOR  COLUMNS SIZE SKEWONLY’, probabilmente la procedura si aspetterebbe anche il nome di una colonna (invece di ALL) e quindi finisce per non calcolare mai gli istogrammi.  Viceversa però con method_opt=>’FOR ALL COLUMNS SIZE SKEWONLY’ ho trovato che gli istogrammi vengono calcolati su tutte le colonne, almeno nel mio test.

il test

Riporto il test che ho fatto in modo che si possa capire se il test è significativo o no:

method_opt=>’FOR ALL COLUMNS SIZE SKEWONLY’

SVILUPPO40@PERSEO10 > CREATE TABLE TEST_STATS
 AS SELECT * FROM ALL_OBJECTS;

Tabella creata.

SVILUPPO40@PERSEO10 >update test_stats set object_id=100
 where rownum COMMIT;

Commit completato.

SVILUPPO40@perseo10> DECLARE
2   ELENCO_OBJ DBMS_STATS.ObjectTab;
3  begin
4    dbms_stats.gather_schema_stats(ownname=>'SVILUPPO40',
5    objlist=>elenco_obj,
6    method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
7    for i in elenco_obj.first..elenco_obj.last loop
8      dbms_output.put_line(elenco_obj(i).objname);
9    end loop;
10  end;
11  /

SVILUPPO40@perseo10 > SELECT COLUMN_NAME,HISTOGRAM,NUM_BUCKETS,
2  NUM_DISTINCT,DENSITY,NUM_NULLS
3  FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TEST_STATS';

COLUMN_NAME      HISTOGRAM       NUM_BUCKETS NUM_DISTINCT    DENSITY  NUM_NULLS
---------------- --------------- ----------- ------------ ---------- ----------
OWNER            FREQUENCY                 9            9 ,000064599          0
OBJECT_NAME      HEIGHT BALANCED         254         6263 ,000178709          0
SUBOBJECT_NAME   NONE                      0            0          0       7740
OBJECT_ID        FREQUENCY                22           22 ,000064599          0
DATA_OBJECT_ID   HEIGHT BALANCED         254         1176  ,00085034       6564
OBJECT_TYPE      FREQUENCY                19           19 ,000064599          0
CREATED          HEIGHT BALANCED         254          753 ,002370247          0
LAST_DDL_TIME    HEIGHT BALANCED         254          766 ,002335407          0
TIMESTAMP        HEIGHT BALANCED         254          796 ,002407341          0
STATUS           FREQUENCY                 2            2 ,000064599          0
TEMPORARY        FREQUENCY                 2            2 ,000064599          0
GENERATED        FREQUENCY                 2            2 ,000064599          0
SECONDARY        FREQUENCY                 1            1 ,000064599          0

Selezionate 13 righe.

method_opt=>’FOR  COLUMNS SIZE SKEWONLY’

SVILUPPO40@perseo10 > drop table test_stats purge;

Tabella eliminata.

SVILUPPO40@perseo10 > CREATE TABLE TEST_STATS AS SELECT * FROM ALL_OBJECTS;

Tabella creata.

SVILUPPO40@perseo10 > update test_stats set object_id=100 where rownum<7720;

Aggiornate 7719 righe.

SVILUPPO40@perseo10 > commit;

Commit completato.
SVILUPPO40@perseo10 > DECLARE
2   ELENCO_OBJ DBMS_STATS.ObjectTab;
3  begin
4    dbms_stats.gather_schema_stats(ownname=>'SVILUPPO40',
5    objlist=>elenco_obj,
6    method_opt=>'FOR COLUMNS SIZE SKEWONLY');
7    for i in elenco_obj.first..elenco_obj.last loop
8      dbms_output.put_line(elenco_obj(i).objname);
9    end loop;
10  end;
11  /

SVILUPPO40@perseo10 > SELECT COLUMN_NAME,HISTOGRAM,NUM_BUCKETS,
2  NUM_DISTINCT,DENSITY,NUM_NULLS
3  FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TEST_STATS';

COLUMN_NAME     HISTOGRAM  NUM_BUCKETS NUM_DISTINCT    DENSITY  NUM_NULLS
--------------- ---------- ----------- ------------ ---------- ----------
OWNER           NONE
OBJECT_NAME     NONE
SUBOBJECT_NAME  NONE
OBJECT_ID       NONE
DATA_OBJECT_ID  NONE
OBJECT_TYPE     NONE
CREATED         NONE
LAST_DDL_TIME   NONE
TIMESTAMP       NONE
STATUS          NONE
TEMPORARY       NONE
GENERATED       NONE
SECONDARY       NONE

Selezionate 13 righe.

CBO e Bind Peeking: l’ossessione
Cercando informazioni si possibili valori del parametro METHOD_OPT e del relativo comportamento delle procedure mi sono imbattuto ancora una volta in interessanti documenti.
La nota metalink 1031826.6 “Histograms: An Overview“.Questa nota risulta rivista il 15 gennaio 2007, ma in essa vi si afferma che gli istogrammi sono inutili se tutti i predicati sulla colonna usano bind variables. Suppongo che questo sia stato scritto prima che in 9i fosse introdotto il bind peeking. Ad alimentare il sospetto che la nota sia vecchiotta c’è il fatto che in fondo si fa riferimento alla versione 7.3

La nota metalink 212809.1 “Limitations of the Oracle Cost Based Optimizer” si pone attenzione sul alcuni limiti di CBO.

La nota metalink 377847.1 “Unsafe Peeked Bind Variables and Histograms” mi sembra interessante. In realtà in un primo momento, non avendola letta attentamente ho pensato parlasse di una feature nuova di 11g. Poi facendo una ricerca su “unsafe peeked binds” sono capitato su questa discussione in cui Mark Bobak pone l’attenzione sul fatto che questo comportamento viene attivato con il valore del parameter CURSOR_SHARING=SIMILAR.

La nota metalink 4657871.1 “Managing CBO Stats during an upgrade to 10g or 11g” da qualche suggerimento gestire la migrazione all’ottimizattore CBO di 10g o 11g.

Qual’è il vero problema nella convivenza tra istogrammi e bind peeking?
NON SONO LE CATTIVE PRESTAZIONI O LE SCELTE SBAGLIATE DI CBO.
Come dice bene Alex Gorbachev in un commento al suo post intitolato “Bind peeking with no histograms” l’unico e vero problema della coesistenza di bind peeking e degli istogrammi è l’instabilità dei piani di esecuzione! Ma attenzione, se a seconda del valore pescato durante il bind peeking CBO genera un piano che è buono per quel valore e non per altri (che poi capitano) allora il problema vero è che per quella query non vi è un piano di esecuzione ottimale, esso dipende dai valori passati. Quindi se eliminiamo il bind peeking o l’istogramma il piano generato sarà ottimale per alcuni casi e non per altri, è inevitabile. Ribadisco perciò che nessuna delle due soluzioni, proposte da molti sul web è ottimale.
Nella mia situazione (di cui ho parlato molto ma che non ho mai descritto nel dettaglio, forse un giorno lo farò) in cui la non esecuzione del bind peeking provoca una scelta sbagliata da parte di CBO mi è stato suggerito di modificare la query (e su CDOS di rivedere lo schema dati). Ma siccome con RBO il piano di esecuzione è sempre stato ottimale forse tali consigli non erano proprio buoni.
Viceversa credo, come ho gia detto, che in situazioni simili, quindi di instabilità del piano di esecuzione può risultare ottimale non usare affatto bind variables, usando i valori letterali. Può essere che bilancio tra sovraccarico sulla SHARED_POOL e il miglioramento di prestazioni dovuto al piano ottimale sia vantaggioso, magari combinato con CURSOR_SHARING=SIMILAR e il buon funzionamento della gestione delle ‘unsafe bind variables’ descritto appunto nella nota metalink 377847.1 “Unsafe Peeked Bind Variables and Histograms

10 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Ciao Cristian,
    non so se hai già avuto notizia della cosa ma in 11g la cosa è cambiata ed il team è al lavoro per ottimizzarlo ulteriormente;
    in sostanza negli algoritmi dell’ottimizzatore è stata introdotta la possibilità di avere + piani di esecuzioni a seconda proprio del valore di bind peeked; il CBO in buona sostanza determina/identifica se il cursore è bind sensitive, cioè se il piano di esecuzione è influenzato proprio dal particolare valore della bind; tieni presente che ciò è possibile solo se esiste un istogramma sulla colonna, calcolato quindi in funzione di dati skewed su di essa.
    La fonte è la + autorevole cioè propri il team di Oracle con test case annesso e Q&A molto interessanti, tra l’altro una domanda è stata posta da Wolfgang Brietling (oaktable quality🙂 ):

    http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html

    Ciao
    Alessandro Deledda

  2. Ciao Cristian

    Un commento in riferimento alla frase seguente…

    > Dopo un paio di tentativi e di analisi ho capito che in pratica
    > con method_opt=>’FOR COLUMNS SIZE SKEWONLY’,
    > probabilmente la procedura si aspetterebbe anche il nome
    > di una colonna (invece di ALL) e quindi finisce per non
    > calcolare mai gli istogrammi.

    È importante notare che settando il parameter METHOD_OPT ad un valore senza “ALL” (e senza specificare una lista di colonne) nessuna statistica a livello delle colonne è calcolata. Quindi, non sono solo gli istogrammi a mancare… Quindi, a mio parere, o si specifica “ALL” o si specifica una lista di colonne.

    Chris

  3. Grazie Chris,
    effettivamente facendo un’ulteriore ricerca su Google avevo trovato qualche indizio in tal senso e facendo una nuova prova ho appurato che sul mio ambiente di test (10.2.0.3) senza specificare ALL e senza specificare alcuna colonna non vengono valorizzati ad esempio i campi NUM_BUCKETS, NUM_DISTINCT, DENSITY, NUM_NULLS della vista USER_TAB_COLUMNS, a conferma di quello che mi dici.
    Porgo l’occasione per congratularmi per l’uscita del tuo nuovo libro. Volendo ottimizzare i costi di spedizione sto facendo un’indagine su altri possibili libri da ordinare insieme: qualche consiglio?

  4. Ciao Cristian

    > Porgo l’occasione per congratularmi per l’uscita del
    > tuo nuovo libro.

    Grazie mille!

    > Volendo ottimizzare i costi di spedizione sto facendo
    > un’indagine su altri possibili libri da ordinare insieme:
    > qualche consiglio?

    Per ovi motivi non ho avuto tempo di leggere libri da un bel pezzo (da circa fine 2006). Quindi, non saprei dirti.

    Per quello che rigguarda le spese di spedizione ho sempre trovato interessante come amazon.de e amazon.fr spediscono gratuitamente la merce in Svizzera ma non in Italia. Vai a capirle certe cose… Dove fai le comande di solito?

    Chris

  5. Devo dire che finora ho utilizzato bookpool.com e mi sono trovato molto bene. Soprattutto se non si tratta di nuove uscite solitamente hanno prezzi buoni, ma spediscono solo dagli USA e verso fuori solo con corriere espresso. Francamente quando compro un libro sono ansioso di averlo fra le mani per cui preferisco così che attendere settimane una spedizione ordinaria. Riguardo Amazon mi hai messo una pulce nell’orecchio. Non avevo mai fatto distinzione, nel vedere i prezzi, fra amazon.com, amazon.fr, amazon.co.uk. In realtà .fr e .de ieri li ho esclusi comunque per la mia difficoltà a interpretare il francese ed il tedesco.

  6. Ho fatto una piccola indagine. Su Amazon.co.uk il libro viene venduto a 36,09 sterline, con circa 5 sterline di spedizione (5-6 giorni) per un totale di circa 52 Euro. Su Amazon.de il libro viene venduto a 38 Euro ma non ci sono informazioni sulle spese di spedizione. Su Amazon.fr il libro pare non essere disponibile, però ho trovato le informazioni sulle spese di spezione, circa 6 Euro più IVA (circa 5-6 giorni credo). Su bookpool il libro viene venduto a 46 Dollari e le spese di spedizione sono sui 39 Dollari. Ora ci rifletterò

  7. Mai usato bookpool.com, ma posso pensare che la spedizione espresso sia effettivamente cara… Avendo la fortuna di masticare sia francese che tedesco approfitto delle spedizioni gratuite (il prezzo dei libri è un po’ più alto, ma conviente comunque).
    Se sei interessato ad avere rapidamente una copia del mio libro posso aiutarti. Ne ho ancora una decina sottomano che non hanno ancora proprietario. Lo venderei a prezzo di copertina. Inclusa la spedizione per l’Italia sarebbero 50 o 60 Euro (a dipendenza della spedizione, economy o priority). Lo potrei ancora spedire oggi pomeriggio e in pochi giorni lo riceveresti🙂

  8. @Alessandro,
    solo oggi mi sono accorto che il tuo commento era stato catturato da askimet come spam. Riguardo a 11g, in effetti ho trovato molto interessante la nuova possibilità dell’ottimizzatore di generere più piani di esecuzione. Devo anche testimoniare che un nostro cliente ha recentemente migrato da 9i (con RULE) a 11g e dal punto di vista dell’ottimizzatore dopo ormai almeno due settimane di produzione pare si comporti molto bene.

  9. ci ho pensato sai perchè in effetti non lo vedevo pubblicato;

    ho visto che frequenti ogni tanto anche oracleportal.it, il mio nick è Alasondro, un vero peccato che ultimamente non si sia + visto Alberto Dell’Era bazzicare lì; dipenderà dal fatto che spesso e volentieri il portalino è giù oppure, mi auguro, magari sta preparando qualche nuovo paper di studio…

    @Christian
    approfitto ed anche io mi unisco alle congratulazioni per il tuo libro

    “Per quello che rigguarda le spese di spedizione ho sempre trovato interessante come amazon.de e amazon.fr spediscono gratuitamente la merce in Svizzera ma non in Italia. Vai a capirle certe cose…”

    sorge spontaneo pensare che come ci vedono ci trattano🙂 scherzo ovviamente, spero ……. 😉

    Ciao
    Alessandro

  10. Ciao Alessandro,
    in effetti mi è capitato spesso di trovare giu il sito di oracleportal.it. Per quanto mi è possibile cerco di frequentare i forum e se ritengo di poter dare il mio contributo intervengo. Credo che questo mi aiuti a imparare, magari ci trovo delle situazioni in cui poi mi vengo a trovare anche io e quindi ho gia la soluzione. Per quanto riguarda Alberto Dell’Era devo dire che in effetti i suoi interventi sono sempre di buona qualità. Chissà, forse sta scrivendo un libro anche lui …


Lascia un commento

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...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: