Oracle 11g, Istogrammi e DBMS_STATS.AUTO_SAMPLE_SIZE

venerdì 18 luglio 2008 alle 18:28 | Pubblicato su 11g, Performance Tuning | 6 commenti
Tag: , , , ,

Un cliente della mia azienda ha recentemente migrato in produzione la nostra applicazione da Oracle 9.2 a Oracle 11g; in RAC prima e in RAC ora (due nodi). Nei test prima e nel primo periodo di produzione, il nuovo database 11g ha dimostrato di comportarsi molto bene, salvo qualche problemino non ancora risolto ma per ora non bloccante. La cosa che faceva più paura in questo passaggio era l’ottimizzatore, sulla versione 9.2 veniva ancora usato RBO, con la 11g di è dovuto passare a CBO in quanto RBO è ormai da tempo desupportato. Tutto e filato liscio fino ad un paio di giorni fa quando senza alcuna spiegazione logica una query su uno dei due nodi del cluster ha cominciato ad essere eseguita con un piano di esecuzione “sbagliato” che sparava l’utilizzo delle CPU del nodo a percentuali medie superiori al 95%, con conseguente degrado delle prestazioni globali. Questa problematica probabilmente verrà sottoposta al supporto Oracle, quindi per ora non ne riporto altri dettagli.

In emergenza si è riusciti a modificare l’applicazione aggiungendo un’HINT alla query per forzare l’utilizzo dell’indice corretto e così l’emergenza è cessata. Il giorno dopo però ho dato un’occhiata per verificare se ci fossero nuove anomalie; in effetti ho trovato una nuova anomalia, che probabilmente è sempre stata presente ma non era stata notata prima. Un’altra query utilizzava un piano di esecuzione “sbagliato” facendo un full table scan dove era più opportuno utilizzare un indice.

In questo caso dopo una attenta analisi ho individuato però una causa ben definita, si tratta delle statistiche, raccolte a campione su un campione non rappresentativo.  Proprio pochi giorni fa ho riletto due post sulle evoluzioni sul sistema di raccolta delle statische a campione del package DBMS_STAT, uno sul blog di Greg Rahn e l’altro sul blog del gruppo di sviluppo dell’ottimizzatore CBO. Entrambi spiegano e dimostrano come gli algoritmi siano stati migliorati aumentando contemporanemeante la precisione e diminuendo i tempi di elaborazione. Essi riportano come dai loro test i tempi di raccolta delle statistiche con il parametro SAMPLE_SIZE settato a DBMS_STATS.AUTO_SAMPLE_SIZE  siano vicini a quelli con campionamento fisso al 10% e l’accuratezza sia vicina  a quella ottenuta con campionamento fisso al 100%.

Io purtroppo sono capitato in un caso in cui il campione utilizzato da un’accuratezza inadeguata, tanto da sballare l’elaborazione dei piani di esecuzione.  Riporto un po’ di dati:


SQL> select table_name, num_rows, sample_size, last_analyzed
2  from user_tables where table_name='TABELLAX';

TABLE_NAME      NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------- ---------- ----------- -------------------
TABELLAX          479000      479000 17-07-2008 22:04:59

SQL>SELECT COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,NUM_DISTINCT,SAMPLE_SIZE,HISTOGRAM
2  FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='TABELLAX'
3  AND COLUMN_NAME IN ('COLONNA1','COLONNA2');

COLUMN_NAME NUM_DISTINCT  NUM_NULLS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
----------- ------------ ---------- ------------ ----------- ---------------
COLONNA1               9          1            9        5547 FREQUENCY
COLONNA2             369     446894          369       32106 HEIGHT BALANCED

SQL> select column_name,endpoint_number,endpoint_value
2   from user_tab_histograms
3   WHERE TABLE_NAME='TABELLAX' AND COLUMN_NAME
4   IN ('COLONNA1','COLONNA2');

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
----------- --------------- --------------
COLONNA1               5547             -1
COLONNA2                  1             17
COLONNA2                  2             39
COLONNA2                  3             42
..........................................
COLONNA2                254           5647

243 rows selected.

Non ho riportato l'informazione di USER_TAB_COL_STATISTICS LOW_VALUE e HIGH_VALUE che ho appurato essere corretti.

Riepilogando  ho una tabella con circa 470000 record, un particolare campo di questa tabella ha una distribuzione un po' particolare:


SQL> select COLONNA1,count(*)
2  from TABELLAX
3  group by COLONNA1
4  order by COLONNA1;

COLONNA1               COUNT(*)
------------------- ----------
-1                      369476
54                           1
60                           1
66                           1
80                           1
85                           1
105                          1
109                          1
121                          1
124                          1
128                          1
134                          1
155                          1
168                          1
628                          1
1109                         1
1252                         1
1302                         1

ho tagliato un centinaio di righe che sono simili alle ultime. E' quasi superfluo che dica che alla base c'è un problema di progetto applicativo: per motivi che nessuno sa spiegare nella notte dei tempi si è pensato bene di utilizzare il valore "-1" come valore "dummy" al posto nel null, con due conseguenze negative e nessuna positiva. Le conseguenze negative sono:

  1. L'indice su quella colonna è più voluminoso
  2. CBO impazzisce

Tutti i casi di anomalie nel comportamento di CBO che ho riscontrato fin'ora con l'applicazione sviluppata nella mia azienda sono state causate da questo insensato utilizzo del "-1", che avrebbe avuto senso se i record coinvolti fossero stati una piccolissima percentuale, ma non hanno senso quando i record coinvolti sono la maggioranza; mi spiego, se lascio il valore null e poi devo cercare i record con quel campo a null mi precludo (non sempre come ha ben spiegato Richard Foote) l'utilizzo di un indice. Utilizzando il -1 il valore viene sempre indicizzato, solo che se su 470000 record, 350000 hanno quel campo a -1, usare l'indice è controproducente.

Tornando alle statistiche, il risultato del pessimo campionamento, come si vede sopra è un istogramma in cui sono evidenziati solo 5547 record con il valore della colonna COLONNA1 pari a "-1" quindi l'istogramma è decisamente inutile anzi dannoso. Il piano di esecuzione riporta con un valore diverso da "-1" una stima di 239K record estratti:


Execution Plan
----------------------------------------------------------

------------------------------------------------------------
| Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------
| SELECT STATEMENT  |          |   239K|    94M|  8506   (1)|
|  TABLE ACCESS FULL| TABELLAX |   239K|    94M|  8506   (1)|
--------------------------------------------------------------

Tale stima arriva a 478K nel caso di parametro =-1.

About these ads

6 commenti »

RSS feed dei commenti a questo articolo. TrackBack URI

  1. Jonathan Lewis li chiama “Deadly Defaults”.

    Per quanto riguarda l’istogramma, se non fai un sampling al 100% della tabella, sarà sempre sicuramente inutile e dannoso.

    Esempio: tabella con 64178 righe e 1 colonna, tutte uguali a 1 tranne 10 che vanno da 1001 a 1010, colonna dd.

    SQL> explain plan for select * from distrib where dd = 1006;

    Explained.

    Elapsed: 00:00:00.09
    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    —————————————————————————————————————————————————————-
    Plan hash value: 527318038

    —————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————–
    | 0 | SELECT STATEMENT | | 32101 | 96303 | 16 (7)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| DISTRIB | 32101 | 96303 | 16 (7)| 00:00:01 |
    —————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 – filter(“DD”=1006)

    Chiaramente sbagliato il computo delle righe, ma:

    SQL> exec dbms_stats.gather_table_stats(user, ‘DISTRIB’, estimate_percent => 100);

    SQL> explain plan for select * from distrib where dd = 1006;

    Explained.

    Elapsed: 00:00:00.00
    16:15:29 SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    —————————————————————————————————————————————————————-
    Plan hash value: 527318038

    —————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 3 | 16 (7)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| DISTRIB | 1 | 3 | 16 (7)| 00:00:01 |
    —————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 – filter(“DD”=1006)

    13 rows selected.

    SQL> explain plan for select * from distrib where dd = 1;

    Explained.

    Elapsed: 00:00:00.01
    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    —————————————————————————————————————————————————————-
    Plan hash value: 527318038

    —————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————–
    | 0 | SELECT STATEMENT | | 64168 | 187K| 16 (7)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| DISTRIB | 64168 | 187K| 16 (7)| 00:00:01 |
    —————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 – filter(“DD”=1)

    13 rows selected.

    Bye :-)

  2. Ciao Cristian,
    confermo l’esperienza di Rudy per quanto riguarda la necessità di analizzare in compute gli istogrammi rispetto a degli istogrammi in gioco;
    nel mio caso come nel tuo Oracle stimava come ottimale una full table scan su di un tabella (ma sbagliando nel mio caso) e ignorava l’indice composto su 3 colonne in cui sapevo indagando che la distribuzione del dato in almeno due di esse non era uniforme rispetto al dominio di valori possibili;
    con l’utilizzo dell’indice però i tempi di esecuzione diventavano ottimi considerando anche il fatto che, rispetto al bind variable peeking, ero fortunato essendo già rappresentativi i valori passati alle bind variables al primo run

    quindi per queste 3 colonne utilizzai:

    estimate_percent => null (compute)

    method_opt => ‘for columns size skewonly C1,C2.C3′

    e da qui l’utilizzo dell’indice nella successiva stima del piano di esecuzione

    il miglioramento su 11G rispetto al DBMS_STATS.AUTO_SAMPLE_SIZE vorrei proprio provarlo per il mio caso e vedere un pò come va, mi riprometto di farlo quando avrò spazio per un’istanza 11G

    Ciao
    Alessandro

  3. Però sono perplesso. Nel mio caso è molto più facile modificare il codice dell’applicazione ed aggiungere un hint alla query. Altrimenti ritengo che gestire questi casi sia complicato. Ciò che mi viene in mente è che non mi riusulta sia possibile definire ad esempio un parametro personalizzato sulla percentuale di campionamento a livello di tabella. Ad esempio impostare per la tabella TABELLAX del mio caso ESTIMATE_PERCENT=>100. Essendo quella tabella relativamente piccola non sarebbe un problema. Questo permetterebbe di toccare il meno possibile e lasciando quindi quasi tutto automatico come suggerito da Oracle. Le alternative sono leggermente più “onerose”, ad esempio schedulare un task personalizzato, in coda a quello predefinito di ricalcolo delle statistiche, che ricalcoli le statistiche su quella tabella con il parametro voluto. Oppure lanciare il calcolo delle statisiche e poi “lockare” le statistiche su quella tabella. Questa soluzione però è rischiosa, i dati potrebbero cambiare in modo significativo nel tempo (sicuramente se ci sono di mezzo date).
    Insomma ci sono molte cose che si possono fare per parametrizzare CBO, ma richiedono competenza, test e tempo.

  4. “Insomma ci sono molte cose che si possono fare per parametrizzare CBO, ma richiedono competenza, test e tempo.”

    sicuramente,
    ma questo perchè fondamentalmente ogni approccio è da verificarsi caso per caso…..

    nel mio caso specifico mantengo la raccolta di statistiche di default (il default di 9i però) e solo per quelle tre colonne della tabella agisco come ti ho indicato e, così facendo, anche se la tabella è grossa non ci metto molto

    rimango dell’avviso che il tutto automatico non va, preferisco raccoglierle di mio le statistiche e secondo i miei criteri valutati analizzando bene dati e applicazione, e comunque se le statistiche vanno bene le salvo quindi, in caso di problemi, ripristino una situazione che porta l’ottimizzatore ad utilizzare i piani che davano migliori performance e del resto in questo senso non sono stato l’unico a mettersi il problema ;-) :

    http://www.oracleportal.it/oipforum/thread.jspa?forumID=17&threadID=5487&messageID=23466#23466

    Dave Ensor’s dbms_stats paradox:
    “it is only safe to gather statistics when to do so will make no difference”

    http://wedonotuse.blogspot.com/2005/11/how-often-should-stats-be-collected.html

    Alessandro

  5. Ciao Cristian,
    ho provato a postare un commento ma non viene pubblicato

    Alessandro

  6. Ciao Alessandro,
    evidentemente Askimet guarda la lunghezza e la presenza di link nei commenti con sospetto :)
    Per quanto riguarda il contenuto del commento, effettivamente il “tutto automatico” può funzionare in condizioni “normali”, ma i la conformazioni dei dati dipende dalle applicazioni, quindi può avere distribuzioni a cui i default utilizzati non vanno bene. Ora devo trovare un attimo per leggere con calma i due link che segnali.


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 70 follower

%d blogger cliccano Mi Piace per questo: