|
|
|
Forum Home
»
DBA
Topic:
analyze table / analyze index
Risposte:
6
Pagine:
1
Ultimo Invio:
12-mar-2010 16.32
da:
Roberto0
|
 |
|
mauste
Posts:
16
Da:
Italia
Registrato:
10-mar-2010
|
|
analyze table / analyze index
Inviato:
10-mar-2010 17.35
|
|
Utilizzo ancora un DB 8.0.5 su di una macchina W2000S in cui diverse tabelle sono legate tra loro con vari indici unici e non (nessun cluster); a seguito di un rebuild index fatto su queste tabelle la situazione riguardo query mirate è peggiorata notevolmente (200.000 rec in Fullscan 15 sec. direi drammatico); provo con analyze sull'indice lento,computo la statistica e valido la struttura (NULLA): pare che l'optimizer scelga il full scan di default; explain plan dice sempre full scan allora provo a forzare la query con l'indice explicito (/* inedex.....*/ risolta in 30ms); prendo una mazza e stramazzo la tabella (ancora con analyze table xxx compute statistics)... risolto!!! adesso quella query mirata è scan rowid in circa 30ms. Da quì la domanda: non è soltanto necessario computare le statistiche sull'indice e fare un rebuild di quest'ultimo per far digerire all'optimizer il macigno!!! ci vuole anche analize sulla tabella? perchè visto che l'indice è gestito a parte?
|
|
Roberto0
Posts:
136
Registrato:
4-giu-2009
|
|
Re: analyze table / analyze index
Inviato:
11-mar-2010 12.55
|
|
Il comportamento da te descritto mi fa pensare che il piano eseguito subito dopo il calcolo statistiche del solo indice, sia quello precedente: soft parse, e che dopo il calcolo statistiche della tabella & indice (che invalida il cursore in shared pool), il reparsing (hard parse) abbia risolto il problema.
|
|
mauste
Posts:
16
Da:
Italia
Registrato:
10-mar-2010
|
|
Re: analyze table / analyze index
Inviato:
11-mar-2010 17.35
|
|
Grazie per la risposta... questo problema è soltanto l'apice; in che modo sono legate le statistiche indice/tabella con il piano dell'optimizer? dove posso trovare delle info comprensibili al riguardo? la tua supposizione è corretta (cursore invalidato e da rivalidare con analyze compute su l'indice): ma la tabella? come spieghi che la cosa sia stata ottimizzata soltanto dopo l'analyze su quest'ultima? --- * il DB è popolato da diverse tabelle in uno schema utente; il rebuild degli indici eseguito in modo casuale (alcuni analizzate prima ed altri no), il successivo analyze eseguito sulle tabelle interessate da query provenienti esternamente da applicazioni JAVA ha causato lo sconquasso generale (risultati in tempi enormi o peggio, blocco dell'applicazione e perdita della connessione TNS con errori tipo ORA-00600). Una cosa che mi viene in mente riguarda lo stato delle statistiche all'atto del popolamento iniziale (dopo non sono state più eseguite per svariati anni): e se invalido la totalità delle statistiche sugli indici e quelle sulle tabelle (soltanto quelle dinamiche naturalmente), teoricamente o combino un casino oppure !!! magari analizzando successivamente il trace SQL passo per passo analizzo soltanto dove serve? e poi credo che delle volte l'optimizer scelga si... ma anche in modo non proprio esplicito. Che ne pensi?
|
|
Roberto0
Posts:
136
Registrato:
4-giu-2009
|
|
Re: analyze table / analyze index
Inviato:
12-mar-2010 11.27
|
|
Grazie per la risposta... questo problema è soltanto l'apice; in che modo sono legate le statistiche indice/tabella con il piano dell'optimizer?
L'optimizer nella fase di (hard) parsing determina il piano di esecuzione in base alle statistiche preventivamente raccolte (e memorizzate nel dictionary)... e ad altro.
dove posso trovare delle info comprensibili al riguardo?
Io consiglio sempre: "Troubleshooting Oracle Performance" di Cristian Antognini. Very very good!!!
la tua supposizione è corretta (cursore invalidato e da rivalidare con analyze compute su l'indice): ma la tabella? come spieghi che la cosa sia stata ottimizzata soltanto dopo l'analyze su quest'ultima?
Innanzitutto la mia era solo una supposizione, ma forse non è stata compresa. Io ho detto (ma ripeto dovrei fare qualche verifica in più per dire che è vero) che l'analyze dell'indice NON ha invalidato il piano di esecuzione scadente dopo il rebuild (tra l'altro nel comando di "index rebuild" si deve sempre specificare compute statistics, tanto subito dopo si dovranno raccogliere le statistiche per quell'indice, farlo durante non pesa quasi nulla - Oracle10g per questo lo fa di default).
Il piano di esecuzione è stato invalidato con l'analyze sulla tabella, e quindi se ne è dovuto creare uno nuovo basandosi sulle statiche tabella/indice appena raccolte.
Ad ogni modo la situazione del database mi è abbastanza confusa e faccio un po' fatica a capire tutte le implicazioni e problemi. Ci aggiungi poi un bel ORA-00600 (che di solito deriva da bugs e/o incompatibilità varie applicative/sistema operativo) e completiamo il quadro.
La raccomandazione generale è che le statistiche devono essere aggiornate, cioè compatibili con i dati presenti nel database, e deve esserci corrispondenza tra le statistiche delle tabelle e quelle degli indici, altrimenti l'optimizer ha un quadro falsato della realtà.
se invalido la totalità delle statistiche sugli indici e quelle sulle tabelle (soltanto quelle dinamiche naturalmente), teoricamente o combino un casino oppure !!! magari analizzando successivamente il trace SQL passo per passo analizzo soltanto dove serve? e poi credo che delle volte l'optimizer scelga si... ma anche in modo non proprio esplicito. Che ne pensi?
Penso che la professoressa d'italiano non avesse una buona opinione di te 
A parte gli scherzi, mi trovo anch'io nel dover "tunare" (l'ho messo tra virgolette per evidenziare che è un obbrobio linguistico, per evitare le ire della tua professoressa anche su di me) un database di produzione di cui non raccolgono le statistiche da TRE ANNI! Mi sono detto prima cosa aggiornare le statistiche, altrimenti non ho neanche dati di riscontro su cui lavorare. Vista la delicatezza e dimensione del database ho salvato preventivamente le statistiche e poi le ho rinfrescate. Rusultato: un disastro! Bene (anzi male), restore statistiche vecchie. Risultato: permane il disastro. Abbiamo poi capito il perché: non avevo raccolto gli istogrammi, che invece c'erano, anche se datati, nelle vecchie statistiche. Raccolti gli istogrammi tutto si è sistemato.
|
|
Roberto0
Posts:
136
Registrato:
4-giu-2009
|
|
Re: analyze table / analyze index
Inviato:
12-mar-2010 12.37
|
|
Ah, ho riletto quello che ho scritto: obbrobio è sbagliato, si scrive obbrobrio.
|
|
mauste
Posts:
16
Da:
Italia
Registrato:
10-mar-2010
|
|
Re: analyze table / analyze index
Inviato:
12-mar-2010 13.04
|
|
Grazie per l'obbrobrio... lo accetto comunque! (chiaro che invalido non è quello con un'invalidità...) qualche volta è difficile scambiare delle idee con qualcuno che ne sa più di te: nel mio ambiente sono solo e trovare risposte a quesiti come quello che ti ho postato non è sempre semplice. Comunque le risposte le ho trovate naturalmente in rete e penso di aver capito dove sta il guaio! beh ho accettato il "Peggio che mai" nel senso che ho stramazzato le tabelle con un bel analyze table seguito da un bel delete statistics (soltanto sulle tabelle dinamiche); il risultato è che straordinariamente il tutto ha ripreso a funzionare boh! adesso sto approfondendo la cosa, anche perchè non l'ho progettata io e poi vedremo.
Le informazioni riguardanti la plan table e l'explain si trovano tutte "a bordo dell'Oracle 8.0.5" nel senso che basta cercarle e sono spiegate molto ma molto bene (Grazie lo stesso... a volte anche cercarle è un problema specialmente se non c'è tempo ed il DB è quello di produzione -* peggio è se del DB l'ultimo BK è stato fatto 3 mesi fà).
A questo punto mi viene un sospetto: uno dei parametri configurabili allo startup riguarda il modo con cui l'optimizer lavora (CHOOSE-RULE); supponiamo che di default sia stato impostato sulla prima; il DB ha diversi Schema/User ed uno di questi possiede delle tabelle (Statiche e Dinamiche) usate da un'applicativo esterno al DB; allo startup dell'applicativo (in origine) le tabelle popolate erano quelle statiche (qualche migliaio di row) mentre le dinamiche andavano popolate man mano; le statistiche sulle table erano state computate soltanto su quelle statiche (mah strano! anche se computare statistiche su tabelle dinamihe vuote credo che non abbia un gran senso), e lo stesso vale per gli indici; l'applicativo ha lavorato per qualche anno ma da qualche tempo gli utenti lamentavano un degrado prestazionale su alcuni passaggi; cerco di trovare una spiegazione ma da un controllo sommario riscontro una certa lentezza nell'interrogazione di alcune tabelle (via index e non); poi provo con il forzare l'indice e queste rispondono in tempi accettabili quindi opto per un analyze/compute soltanto sugli indici (nulla); poi proseguo e tento un reindex (mah... non si sa mai): figuriamoci--> è successo il finimondo (alcune cose non funzionavano e l'applicativo mi congelava le sessioni); per ultimo stramazzo la/le tabelle con un analyze/compute--> qualcosa è cambiato ma le cose non sono gestite in modo corretto (osservano gli statement SQL via TOAD questi non vengono eseguiti correttamente... c'è qualcosa di strano!). Due notti passate in bianco controllando indici/tabelle/statement/viste etc e poi... decido per pulire le statistiche in modo mirato sulle tabelle dinamiche man mano che venivano inviate le richieste SQL.
Ed allora MIRACOLO !!! tutto torna a funzionare correttamente, come mai? e se chi ha progettato l'applicativo avesse pensato di passare le query SQL basate sulla loro struttura e non affidandosi al metodo risolutivo dell'optimizer dell'Oracle? per caso non c'è un'istruzione che a livello di sessione modifica il piano valutativo dell'optimizer (tipo ALTER SESSION....) magari non inserita nello statement di connessione?
che dici regge come ipotesi ? oppure è una....
Approposito eccola!
OPTIMIZER_MODE initialization parameter
The user would include the following in the initialization file:
OPTIMIZER_MODE = FIRST_ROWS_N
OPTIMIZER_MODE session parameter.
The user would issue the following statement:
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_N
If the session setting is used, it will override any initialization settings.
OPTIMIZER hint.
The query text would include the following hint:
SELECT /*+ FIRST_ROWS(N) */ ....
If the hint is used, it will override any session and initialization settings.
Il messaggio è stato scritto da: mauste a 12-mar-2010 14.50
|
|
Roberto0
Posts:
136
Registrato:
4-giu-2009
|
|
Re: analyze table / analyze index
Inviato:
12-mar-2010 16.32
|
|
Le informazioni riguardanti la plan table e l'explain si trovano tutte "a bordo dell'Oracle 8.0.5" nel senso che basta cercarle e sono spiegate molto ma molto bene
Contento tu...
(mah strano! anche se computare statistiche su tabelle dinamihe vuote credo che non abbia un gran senso
Ha senso, se no Oracle come fa a sapere che sono vuote? A proposito non è che tu usi il termine tabelle dinamiche per delle tabelle di appoggio che vengono riempite e troncate? In tal caso usa le più efficienti Global Temporary Table, che servono proprio per quello, ma hanno prestazioni molto superiori.
Riguardo il modo di operare dell'optimizer Oracle ti dirò quello che vale in Oracle9i, che FORSE assomiglia alla versione vintage 8 (più probabilmente assomiglia alla 8i). A te l'onere della verifica.
Di default il parametro optimizer_mode vale choose, cioè se tutte le tabelle coinvolte in uno statement non hanno statistiche, l'optimizer funziona in modalità rule, se invece almeno una c'ha statistiche funziona come CBO: Cost Base Optimizer (=all_rows), cioè sceglie il piano a sua stima migliore, basandosi sui dati statistici a disposizione e su defaults se non disponibili. Ovviamente più dati statistici reali ci sono meglio il CBO riuscirà a produrre qualcosa di valido.
Quindi probabilmente quando rimuovi le statistiche da quelle tabelle ti ritrovi in modalità rule (o forse in all_rows, però senza tutte le statistiche che servirebbero) che normalmente usa sempre gli indici, e forse per le tue queries è meglio così, dato che va tutto più veloce.
Il parametro optimizer_mode può essere modificato, sia a livello di sessione che di sistema (in Oracle8 non esisteva l'spfile e alcuni parametri non erano dinamici come lo sono invece nelle versioni successive, a te le verifiche del caso).
Le impostazioni possibili oltre alle tre che ti ho detto, sono quelle (che tu alla fine riporti) del tipo first_rows_%, che servono per privilegiare i piani che forniscono subito un certo numero di righe. Sono utili di solito solo per alcune queries mirate in certi tipi di applicazioni.
Infine l'optimizer Oracle è considerato maturo e affidabile da Oracle9i, quindi è possibile che nonostante le statistiche siano adeguate sbagli. Comunque oltre alle statistiche degli oggetti, CBO si basa su altri parametri che possono migliorare le stime, ad esempio optimizer_index_caching e optimizer_index_cost_adj, che avevano una grossa importanza soprattutto nelle versioni più vecchie di Oracle (e ne hanno persa con l'introduzione del costo attribuito alla CPU a partire da Oracle9: statistiche di sistema)
|
|
| |
