Statspack

martedì 3 maggio 2011 alle 03:10 | Pubblicato su Performance Tuning | 3 commenti
Tag: , ,

Da una veloce ricerca nel blog mi sembra di non aver mai dedicato un intero post a Statspack, uno degli strumenti di monitoraggio e diagnosi delle prestazioni di Oracle più utile e potente. Siccome io lo utilizzo ancora, se non altro perché non è soggetto a licenze particolari come lo sono i nuovi strumenti introdotti dalla versione 10g di Oracle, è il caso di parlarne un po’. Probabilmente non l’ho mai fatto perché ritenevo che su internet ci fossero gia sufficenti informazioni, compresi i manuali Oracle; siccome però Oracle spinge sui nuovi strumenti a pagamento nascondendo di fatto le informazioni e siccome oggi ho incontrato un problema di cui voglio tener traccia qui, oggi finalmente dedico un breve post a questo potente strumento.

Statspack è un package PL/SQL e un insieme di tabelle che servono per raccogliere statistiche sul sistem Oracle. Statspac richiede una tablespace con almeno 100 MB libero, deve essere installato sul database da monitorare da un utente con privilegi SYSDABA. Lo script per l’installazione è $ORACLE_HOME/rdbms/admin/spcreate.sql.  Lo script creerà un utente PERFSTAT.

Statspack permette di fare delle fotografie del sistema (SNAPSHOT) ovvero sia di raccogliere  le statistiche che Oracle normalmente mantiene in in dato istante temporale. Per generare uno snapshot delle statistiche bisogna eseguire come utente PERFSTAT la procedura STATSPACK.SNAP(). Ci possono essere vari livelli di statistiche da 1 a 10, da meno dettagliate a più dettagliate (il default è 5). I parametri di default si cambiano in modo permanente con la procedura STATSPACK.MODIFY_STATSPACK_PARAMETER().

Per generare un report che consiste nel riassunto delle statistiche raccolte tra due snapshot si può usare lo script $ORACLE_HOME/rdbms/admin/spreport.sql. Lo script $ORACLE_HOME/rdbms/admin/sppurge.sql si può usare per eliminare vecchi snapshot.

Per disintallare statspack si usa lo script $ORACLE_HOME/rdbms/admin/spdrop.sql, da lanciare sempre con un utenza con privilegi SYSDBA.

Oggi per la prima volta da quando lo uso ho trovato un problema su statspack, su una installazione 10.2.0.3. Il problema si manifestava con l’errore

ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated

al tentativo di creare uno snapshot.

Per fortuna qui ho subito trovato la descrizione e la soluzione al problema con il riferimento alla nota 382993.1 del supporto Oracle.

Approfitto anche per ricordare che Kerry Osborne tempo fa aveva parlato di statspack su 11g e di un paio di problemini che su questa versione sono sti introdotti.

Ottimizzazione di una query

martedì 23 febbraio 2010 alle 23:13 | Pubblicato su Performance Tuning | 19 commenti
Tag: ,

Una volta scrivevo molto di più; adesso sono settimane che penso: “ecco uno spunto per un post sul mio blog!” poi passa la giornata e mi passa ogni stimolo. Insomma sto passando un periodo molto confuso.

Lo spunto per questo post risale a qualche giorno fa (forse ormai saranno anche settimane) si tratta di una prova che ho fatto nel disperato tentativo di far andare più veloce una query. Si tratta di una query relativamente semplice, ma che lavora su una tabella molto grossa (attualmente viaggia sui 35 milioni di record e aumenta di circa 80000 record al giorno). La query viene utilizzata per un report.  Il problema di questa tabella, oltra a essere grande è che viene interrogata in decine di modi diversi quindi anche determinare degli indici validi è difficile. Però essendomi stato richiesto in particolare di ottimizzare (o almeno provarci) una query specifica ho fatto un po’ di prove. Inizialmente ho fatto delle prove direttamente in produzione, poi non riuscendo ad ottenere nulla ho deciso di provare a vedere che impatto potevano avere degli indici mirati, quindi ho esportato la tabella e l’ho importata in ambiente di test (dove la tabella non era popolata in modo significativo). Ho fatto due tentativi di indici e mi sono reso conto che Oracle preferiva fare uno “INDEX SKIP SCAN” su un indice preesistente, apparentemente meno selettivo, piuttosto che usare un nuovo indice dedicato e apparentemente più selettivo. Sono giunto alla conclusione (assolutamente empirica) che il clustering factor incida negativamente, anche se in realtà pare alto per tutti gli indici.

Ho deciso allora di provare gli strumenti di Oracle il quale ha scoperto che ci poteva essere un piano di esecuzione migliore (che usava l’indice che avevo creato io)

Il risultato è quello dell’immagine, il primo picco è l’esecuzione normale, la seconda con l’outline il SQL Profile (vedi nota) suggerita da Oracle.

La conclusione cui voglio giungere (perché io non amo le interfacce grafiche e non credo negli strumenti che risolvono tutti i problemi) è che l’ottimizzatore Oracle funziona bene, mentre sugli strumenti di diagnostica e di “tuning” (o ottimizzazione) sembra (qui però sono sulla 10.2.0.4) ci sia qualcosa da dire :)

Nota (17/03/2010):

Grazie a Kyle Hailey che mi ha fatto notare che poteva essere un SQL Profile e non una Outline come avevo scritto inizialmente. Questo evidenzia una mia grossa lacuna al riguardo che spero di colmare presto e conto di scriverne un post.

Bind Variables e V$SQL_BIND_CAPTURE

mercoledì 1 luglio 2009 alle 01:10 | Pubblicato su Performance Tuning, Varie | 1 commento
Tag: , , , ,

Sono rientrato lunedì da due settimane di “vacanze” ma ancora non posso dire di essere in forma, chissà perché ma 7/8 ore di sonno al giorno per me non sono sufficenti :(

Ultimamente forse anche a causa della stanchezza, non ho molti spunti per scrivere nuovi post, ieri però ne ho trovato uno grazie ad un thread di discussione su oracleportal. Siccome avevo analizzato la vista V$SQL_BIND_CAPTURE tempo fa (ne avevo anche parlato in un mio post di un anno e mezzo fa) e ieri non mi ricordavo bene come funzionava e che informazioni mostra,  approfitto per un post riassuntivo.

La vista V$SQL_BIND_CAPTURE è stata introdotta con la versione 10g di Oracle e serve a visualizzare i valori delle bind variables utilizzati nell’esecuzione delle query sul database, ma con certe limitazioni, spiegate sulla documentazione. La prima è che vengono “catturare” solo le bind variables presenti nelle clausole WHERE o HAVING degli statement SQL, quindi quelle negli INSERT e negli UPDATE non sono mai catturate e visualizzate da nessuna parte. La seconda limitazione, sempre documentata, ma in modo più nascosto, è che le bind variables per uno stesso cursore vengono catturate al massimo ogni 15 minuti, per non sovraccaricare il sistema; questo è spiegato nella descrizione del campo LAST_CAPTURED.  Più precisamente il popolamento di questa vista è controllato da due parametri “nascosti” e non documentati:

  • _CURSOR_BIND_CAPTURE_INTERVAL che definisce in secondi l’intervallo minimo fra due “campionamenti” delle bind variables per due cursori; il suo valore di default è 900, pari proprio a 15 minuti
  • _CURSOR_BIND_CAPTURE_AREA_SIZE che definisce la dimensione massima dell’area di memoria occupata dalla vista, e quindi il numero massimo di record.

Una discussione su V$SQL_BIND_CAPTURE e la “cugina” V$SQL_BIND_METADATA si può trovare in un post del blog di Miladin Modrakovic che approfitto per segnalare.

Al riguardo scrisse un post anche Jonathan Lewis il quale rivelava di aver confuso il contenuto di quella vista con i valori utilizzati dall’ottimizzatore per la generazione dei piani (bind peeking). Risulta invece che il contenuto di V$SQL_BIND_CAPTURE è la versione formatatta ed estesa del contenuto del campo BIND_DATA della vista V$SQL, il quale ho verificato con due prove cambia insieme al contenuto di V$SQL_BIND_CAPTURE (non ho fatto un’analisi precisa ma vado in fiducia).

Per completezza dell’informazione, come precisato benissimo da Dion Cho in un commento dello stesso post di Lewis, le peeked binds vengono salvate da oracle nel campo v$sql_plan.other_xml, solitamente nella prima riga del piano di esecuzione.

La vista V$SQL_BIND_CAPTURE è una vista “istantanea”, nel senso che ad ogni suo aggiornamento il contenuto precedente va perso, però AWR ,per default ogni ora, salva una foto della vista nella tabella DBA_HIST_SQLBIND e quindi chi ha acquistato il Diagnostic Pack può interrogare questa tabella per fare qualche analisi, non indagini precise a causa appunto delle limitazioni del campionamento ogni 15 minuti effettuato per la V$SQL_BIND_CAPTURE E e di ogni ora per la DBA_HIST_SQLBIND.

ASH Simulator

mercoledì 8 aprile 2009 alle 08:28 | Pubblicato su Performance Tuning | Lascia un commento
Tag: , ,

Fra i vari strumenti di diagnostica e analisi introdotti nella versione 10g del database Oracle vi è il cosiddetto Active Session History (ASH), uno strumento per quello che si definisce “Reactive Database Tuning“. Per una spiegazione più chiara e precisa rimando alla documentazione Oracle (anche se questa non è proprio dettagliata), o meglio alle presentazioni di uno degli ideatori di ASH: Kyle Hailey.

L’idea è quella di campionare, ogni secondo, le sessioni “attive”, registrando cosa stanno facendo. Una sessione può essere in tre stati:

1) inattiva, non sta facendo nulla

2) attiva e sulla CPU, sta elaborando

3) attiva e in attesa, ovvero sta aspettando qualcosa per c0ncludere l’elaborazione, ad esempio sta aspettando un blocco dati da disco.

Un processo di background (MMNL, manageability monitor light) si occupa di fare questo campionamento, salvando i dati in un’area della SGA che è pubblicata attraverso una vista chiamata V$ACTIVE_SESSION_HISTORY. Una parte di questi dati viene poi salvata in AWR.

Si un tratta in sostanza di interrogare continuamente la vista V$SESSION e vedere se ci sono query, sessioni, eventi che ricorrono di più per capire se ci sono problemi di prestazioni, il tutto tuto poi è analizzabile facilmente con i grafici visualizzati sull’Enterprise Manager. E’ un’idea, quella di interrogare la vista V$SESSION, che gia in passato ho utilizzato proprio per far fronte a richieste urgenti di verificare problemi di prestazioni sul database (il reactive database tuning appunto), solo che io mi limitavo a interrogare la V$SESSION, in join con la V$SESSION_WAIT (era oracle 9.2, dove le informazioni sull’evento di attesa non erano ancora presenti nella V$SESSION) e la V$SQL, senza salvare i dati, analizzavo quindi la situazione istantaneamente. ASH fa molto di più.

Il problema di ASH è che per essere utilizzato richiede la “Diagnostic Pack License”. E’ per questo che circa un’anno fa mi piacque molto l’iniziativa di Kyle Hailey: un simulatore di ASH, utilizzabile in versioni Oracle precedenti alla 10g o in versioni senza la licenza necessaria per ASH. Sono passati diversi mesi e finalmente alcune settimane fa ho avuto l’occasione/necessità di provare il pacchetto di simulazione.

In realtà ho fatto alcune modifiche al simulatore per adattarlo alle mie esigenze, l’ho installato sul server stesso, togliendo quindi i  database link. Inoltre l’ambiente in cui mi serviva era un RAC, quindi ho dovuto modificare le tabelle (e di conseguenza le procedure) per gestire anche l’istanza, mantenendo l’utilizzo delle viste GV$, che nel pacchetto originario sono utilizzate perché l’interrogazione delle viste tramite database link pare genere un errore ORA-02070. Ho fatto anche ulteriori modifiche per aggiungere maggior granularità alle informazioni raccolte, distinguendo per i vari statement i  vari “child cursor” ed ho aggiunto anche il SQL_ID (probabilmente non incluso nel package originario perché nato per versioni precedenti alla 10g).

Le procedure, oltre a raccogliere le informazioni sulle sessioni attive (ASH “puro”) raccolgono anche, a intervalli diversi, le informazioni sugli oggetti (io le raccolgo una volta al giorno), e le informazioni sugli statement (V$SQL, V$SQL_PLAN, V$SQLTEXT, ogni mezz’ora). Ho messo tutto su un utente SASH a cui ho dato solo i privilegi necessari a interrogare tutte le viste di sistema necessarie; per come sono scritte le procedure originali esse andrebbero messe su uno schema privilegiato, come SYS.

Dopo un po’ di messa a punto sono riuscito ad utilizzare con soddisfazione questo simulatore e ho voluto confrontarlo con l’ASH integrato di Oracle 10g, vi sono alcune differenze, ma per le mie esigenze trascurabili. Sono rimasto più perplesso dal confronto con i grafici e le tabelle visualizzati nella sezione “Top Activity” (attività principale) che secondo le presentazioni di Hailey dovrebbero essere basate su ASH.

Bind Variable Peeking, un bel post riassuntivo

giovedì 2 aprile 2009 alle 02:56 | Pubblicato su Performance Tuning | 2 commenti
Tag: , , ,

Kerry Osborne, recente ingresso del mio blogroll, nonché del mio feed reader, alcuni giorni fa ha pubblicato sul suo blog un bel post intitolato “Bind Variable Peeking – Drives Me nuts“. Osborne spiega come l’introduzione del meccanismo del bind variable peeking, di cui ho molto parlato anche io, abbia portato un po’ di scompiglio.  Il post è interessante e secondo me ben scritto, fa una breve storia che spiega come Oracle con la versione 9i abbia introdotto quel malefico meccanismo che ha fatto impazzire molte persone, me compreso,

Kerry Osborne ribadisce più volte di preferire la stabilità delle prestazioni alle prestazioni in assoluto, cioè preferisce un piano di esecuzione che dia tempi di esecuzione sulle stesse query omogenee, seppur non ottimali, piuttosto che l’instabilità tipica dei piani di esecuzione delle query in Oracle 9 e 10 con l’ottimizzatore basato sui costi (CBO) e il bind peeking. Questo chiaramente se non si può raggiungere l’ottimo, che come spiega Osborne è raggiungibile solo utilizzando i valori “letterali” nelle query che possono soffrire di questi problemi. 11g ha introdotto il meccansimo chiamato “Adaptive Cursor Sharing” che dovrebbe aver migliorato molto la situazione. Osborn poi ricorda anche che in 11g è migliorato molto il sistema di raccolta delle statistiche automatico.

Insomme come ho titolato si tratta di un bel post riassuntivo della grande problematica dell’instabilità dei piani con CBO e il bind peeking.

Indagini sul comportamento di Oracle RAC

venerdì 20 febbraio 2009 alle 20:06 | Pubblicato su Performance Tuning | 1 commento
Tag: , , ,

Quasi un mese fa ho scritto un post intitolato “Problemi di interconnessione con RAC“. Il fatto di avere un valore stranamente alto nella statistica “packet reassembles failed” visualizzato con il comando neststat -s faceva pensare a qualche problema harware.  In realtà dopo alcuni reboot delle macchine si sono notati comportamenti variabili, che, insieme alla mancanza di altri indizi di anomalie hardware,  mi portano a pensare non si tratti di problemi della singola macchina; al massimo può essere un problema dello switch al quale le interfacce di interconnessione sono attaccate.

Il fatto è che all’aumentare dell’attività di modifica e conteporanea interrogazione massiva dei dati, le prestazioni del RAC  peggioravano, per cui è capitato che una query, un full scan su una tabella non enorme, che in condizioni normali ci impiega tra uno e dieci secondi, ci mettesse anche dieci minuti.

Ho fatto un piccolo test, l’impianto ricordo è un RAC 10.2.0.4 a 4 nodi, con ASM su Linux x86-64. Ho lanciato in sequenza sui quattro nodi una coppia di query che fanno un full scan su due tabelle da circa 80000 blocchi (da 8kB), ad ogni giro ho lanciato questa query:


select TO_CHAR(INST_ID,'9') INST_ID,
TO_CHAR(executions,'999','NLS_NUMERIC_CHARACTERS = '',.''') EXECS,
TO_CHAR(buffer_gets,'99999999','NLS_NUMERIC_CHARACTERS = '',.''') BG,
TO_CHAR(disk_reads,'99999999','NLS_NUMERIC_CHARACTERS = '',.''') DR,
TO_CHAR(CLUSTER_WAIT_TIME/1000000,'99999D99','NLS_NUMERIC_CHARACTERS = '',.''') avg_wclu,
TO_CHAR(USER_IO_WAIT_TIME/1000000,'99999D99','NLS_NUMERIC_CHARACTERS = '',.''') avg_wio,
TO_CHAR(CPU_TIME/1000000,'9999999D99','NLS_NUMERIC_CHARACTERS = '',.''') avg_CPU,
TO_CHAR(elapsed_time/1000000,'999999D99','NLS_NUMERIC_CHARACTERS = '',.''') ELAPs ,
TO_CHAR((CPU_TIME+USER_IO_WAIT_TIME+CLUSTER_WAIT_TIME)/1000000,'99999D99','NLS_NUMERIC_CHARACTERS = '',.''') SUMw,
TO_CHAR((CLUSTER_WAIT_TIME/1000/(BUFFER_GETS-DISK_READS)),'999999D99','NLS_NUMERIC_CHARACTERS = '',.''') "AVGWX_GSBLOCK (ms)",
TO_CHAR((user_io_WAIT_TIME/1000/(DISK_READS)),'999999D99','NLS_NUMERIC_CHARACTERS = '',.''') "AVGW_X_IOBLOCK (ms)"
from Gv$sql where sql_text like'select /* CRISTIAN_TEST%' ORDER BY INST_ID,SQL_TEXT;

L’intento era quello di verificare “l’efficenza” della Global Cache, e l’affermazione secondo cui reperire un blocco dalla cache di un’altro nodo dovrebbe essere più efficente e veloce che reperirlo da disco, il tutto utilizzando le interessanti informazioni fornite dall GV$SQL

Ecco il risultato di questa query dopo circa quattro giri:

INST_ID EXECS BG        DR        AVG_WCLU  AVG_WIO   AVG_CPU     ELAPS      SUMW      AVGWX_GSBLOCK (ms) AVGW_X_IOBLOCK (ms) 
------- ----- --------- --------- --------- --------- ----------- ---------- --------- ------------------ ------------------- 
 1         5     447250    238414    331,58     22,48       17,53     365,27    371,59       1,59                ,09          
 1         4     300141     59236     13,92      5,39        9,21      25,30     28,52        ,06                ,09          
 2         4     357740    202052     12,82     20,75       12,88      41,83     46,46        ,08                ,10          
 2         4     300161      4386     56,95      1,78       11,17      66,46     69,90        ,19                ,41          
 3         4     357708    220675     24,39     21,01       14,31      54,25     59,71        ,18                ,10          
 3         4     300142       771     31,37       ,65        8,81      37,34     40,83        ,10                ,84          
 4         4     357700    237445     32,37     18,48       14,21      60,23     65,05        ,27                ,08          
 4         4     300324      1237     60,16      1,44       12,43      68,08     74,04        ,20               1,17

E’ accaduto che al terzo lancio di una delle due query sul primo dei due nodi, l’esecuzione è durata quasi dieci minuti contro una media compresa tra cinque e quindici secondi. La “causa” di ciò è evidenziata nella tabella sopra, ovvero in un elevato tempo di attesa sui blocchi tramite GCS.  Ho fatto delle approssimazioni nell’analisi e nella costruzione di questa tabella; ho preso i buffer gets come numero totale di blocchi necessari,  i “disk reads” come numero di blocchi non presenti in cache e letti su disco, la differenza l’ho considerata come blocchi recuperati dalla “Global Cache”. Quest’ultima può sembrare ed essere veramente una approssimazione forte, ma i risultati secondo me dimostrano comunque ciò che volevo vedere. Una parte dei blocchi probabilmente è gia presente nella cache locale, ma il tempo per accedere a questi dovrebbe essere trascurabile. Ho fatto dei controlli incrociati sulle V$SESSION_EVENT e V$SESSTAT e penso che le approssimazioni siano accettabili.

Il punto è che un caso anomalo (su cinque esecuzioni) ha portato sulla prima istanza, per la prima delle due query ad avere un tempo medio di attesa su un blocco (“gc cr multi block request”) molto alto. Quello è il caso eclattante e grave, ma nella stessa tabella vi sono altre due righe in cui risulta un tempo medio di attesa sul blocco via GCS  (AVGWX_GSBLOCK (ms)) più alto di quello di attesa sul blocco da disco (AVGW_X_IOBLOCK (ms))

Questa mia indagine è nata dalla stessa analisi fatta su una serie di query lanciate dall’applicativo, infatti li il problema sembra analogo:

INST_ID EXECS BG        DR        AVG_WCLU  AVG_WIO   AVG_CPU     ELAPS      SUMW      AVGWX_GSBLOCK (ms) AVGW_X_IOBLOCK (ms) 
------- ----- --------- --------- --------- --------- ----------- ---------- --------- ------------------ ------------------- 
 2        41    3585914   1987821    328,85    173,20      124,13     586,50    626,17        ,21                ,09          
 2        41    3585734   1937563    211,74    165,57      104,53     454,35    481,85        ,13                ,09          
 3       206   17995303   3855412   1000,00    329,20      515,54    1673,86   1844,74        ,07                ,09          
 3        45    3878755   1640542    680,64    158,28      134,38     930,06    973,29        ,30                ,10          
 3        38    3291803   1322199    587,86    135,06      106,99     799,74    829,91        ,30                ,10          
 3       185   16172079   3765261    884,73    282,55      406,00    1425,50   1573,28        ,07                ,08          

Si tratta di un piccolo campione. Mancano le istanze 1 e 4 che in questo momento, tramite “SERVICES” sono state escluse in quanto il sistema non è a pieno reggime e con due istanze si comporta molto meglio che con tutte e quattro. In ogni caso nel campione si vede che quasi sempre sembra sia più veloce acquisire i blocchi da disco che non dalla cache di altre istanze.

Se non è un problema hardware, o un bug ed il normale comportamento di RAC è un grosso problema.

Nella mia indagine potrebbe esserci qualche buco, sono gradite eventuali segnalazioni, sta di fatto che il comportamento anomalo, che ha pesato sull’utente finale è una certezza

Prestazioni con tipi DATE e TIMESTAMP

martedì 27 gennaio 2009 alle 27:19 | Pubblicato su Performance Tuning | 3 commenti
Tag: , , , , ,

Sono un po’ confuso.  Nei giorni scorsi ho risolto un problema di prestazioni che credo solo un paio d’anni fa avrei fatto molta fatica a risolvere.

Dopo aver risolto il problema però ho approfondito l’argomento e mi è rimastasta una perplessità.

Il problema è nato, manco a dirlo, da prestazioni scarse su query che nella nostra applicazione esistono praticamente da sempre. Si tratta di query su un tabella di storico sulla quale vanno per data. Trattandosi di database 10.2.0.4 con CBO e con una quantità di dati che comincia a diventare importante, il fatto di andare in “full scan” sulla tabella piuttosto che utilizzare il comodo indice sulla data mi ha fatto subito pensare a due possibilità: a) statistiche non aggiornate b) problemi con il bind peeking.

Le statistiche in realtà erano regolarmente aggiornate, dal task attivato automaticamente alla creazione del database, quindi tutte le notti alle 22 e i fine settimana. Rimaneva la seconda ipotesi, qui mi sono state di molto aiuto le mie ricerche durate un sacco di tempo, ma soprattutto l’aiuto di Alberto Dell’Era di cui ho parlato, poco più di un anno fa.

Grazie all’utilizzo della procedura DBMS_XPLAN.DISPLAY_CURSOR, con l’opzione che mostra anche le peeked bind ho subito notato che nell’elenco delle variabili “peeked”, ovverro analizzate in fase di parsing e generazione del piano di esecuzione mancavano proprio le date. A parte ciò in un primo momento non ho notato null’altro e non ho capito cosa stava accadendo realmente.  Cercando di entrare più nel dettaglio allora ho cominciato a spulciare la V$SQL, la V$SQL_PLAN e…. la V$SQL_BIND_METADATA. Proprio quest’ultima mi ha dato la risposta. Infatti in questa vista si trovano i metadati sulle bind variables, “peeked” o no. Qui spiccava un datatype=180. Ho avuto un attimo di panico perché nell’agitatazione non trovavo la tabella con i codici dei datatype di Oracle, ma quando l’ho trovata ho capito. Nella tabella il campo data è di tipo DATE, quindi codice 12.

Riguardando bene l’output di DBMS_XPLAN.DISPLAY_CURSOR in effetti la risposta c’era ed era una cosa del tipo:

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

1 – filter(INTERNAL_FUNCTION(“CAMPODATA”)=:1)

Il punto era nel codice sorgente dell’applicazione, java,  c’era una cosa tipo:

stmt.setTimestamp(1,ts);

inveche che

stmt.setDate(1,sqldata);

Allora ho fatto rapidamente un programmino di test per verificare se questo era veramente il problema ed ho trovato conferma. L’applicazione, a causa di una modifica recente, che fino a quel momento non aveva creato problemi a causa dei pochi dati, passava la data come tipo dato sbagliato, inducendo Oracle a fare una conversione implicita (sul campo e non sulla variabile passata) che gli impediva di prendere in considerazione l’indice.

Osservazioni e considerazioni

Risolto il problema, alcuni dubbi mi sono rimasti sul comportamento di Oracle. Innanzitutto, dopo una consultazione con il laboratorio di sviluppo si è preferito cambiare il tipo dato sulla tabella: la conversione da tipo date a tipo timestamp è stata rapida e indolore. Evidentemente Oracle codifica i dati di tipo date e timestamp in modo compatibile e fa l’”ALTER TABLE” in modo intelligente senza andare a ripassarsi tutta la tabella.

Un’altra cosa che ci siamo chiesti (io e qualche sviluppatore) è: perché Oracle sceglie di convertire il campo sulla tabella piuttosto che la variabile passata? Una risposta, in questo caso potrebbe essere che converte il tipo dato “meno preciso”, quindi il campo date sulla tabella e non la variabile di tipo timestamp (la cui conversione significherebbe perdere i millesimi di secondo).

Però se io provo a fare una cosa del genere:

String stri = “11-GEN-08″;
stmt.setString(1,stri);

Oracle si comporta bene, converte comunque la variabile stringa a data e usa l’indice.

Performance Tuning con CBO e DBMS_SHARED_POOL

giovedì 18 dicembre 2008 alle 18:13 | Pubblicato su 11g, Performance Tuning | 7 commenti
Tag: , , , ,

In questo blog  ho scritto più volte sull’argomento performance tuning con particolare riguardo ai problemi di “instabilità dei piani di esecuzione” che l’accoppiata CBO e bind variables. Da diverso tempo non mi capitano casi di query con piani esecuzione sballate (direi che è una cosa positiva), però alcuni mesi fa ho avuto un incontro presso un cliente con il suo, assieme ad un consulente Oracle per formare il DBA su come affrontare casi di query con piani di esecuzione sballati che bloccano il sistema. Infatti in precedenza c’era stato un caso simile e sia da parte mia che da parte dello stesso consulente Oracle sentito al telefono erano state suggerite due strade: il ricalcolo delle statistiche su una delle tabelle interessate o il flush della shared pool, con in rischio però che quest’ultima soluzione non fosse certa, perchè se durante il flush il piano è utilizzato, esso è “pinned” cioè bloccato e non viene espluso dalla cache. Stiamo parlando di azioni d’emergenza per sbloccare un sistema con il quale lavorano centinaia di operatori.

Un punto emerso in quell’occasione, come in altre, era il fatto che non c’era un metodo per cacciare dalla cache sono un preciso piano di esecuzione. Bene, oggi, grazie a questo post di Doug Burns, ho scoperto non solo che questo è possibile in 11g, ma anche, leggendo il post citato di Fairlie Rego, che con una patch applicabile dalla versione 10.2.0.2 è possibile anche su 10gR2.

DBMS_SHARED_POOL.PURGE

In Oracle 11g è stato aggiunto un nuovo met0do PURGE al vecchio package DBMS_SHARED_POOL, che permette di espellere singoli oggetti dalla shared pool come ad esempio singoli cursori (e quindi il relativo piano di esecuzione). Fairlie Rego fa un esempio. Non ho capito bene come si valorizza il parametro heaps ne quali sono i possibili valori (a parte il 65 usato nell’esempio). Da quel che si deduce dall’esempio in ogni caso, si elimina dalla cache tutto il cursore e suppongo anche tutti i figli (child)  e i loro piani di esecuzione. Esiste una nota Metalink, la 457309.1, che spiega abbastanza bene uso e disponibilità di questa caratteristica.

Riguardo al disponibilità ho notato una cosa bizzarra, nella nota metalink 457309.1 spiega che questo nuova procedura è disponibile come patch per le versioni 10.2.0.2 e 10.2.0.3 ed è inclusa nella patch set 10.2.0.4 (oltre alla versione 11g). A questo proposito ho notato una cosa bizzarra, controllando su una nostra installazione 10.2.0.4 Enterprise Edition non ho trovato traccia del package DBMS_SHARED_POOL, su una analoga installazione, ma Standard Edition c’è il package con tanto di procedura PURGE. Su una istanza 10.2.04 Entrerprise Edition che uso per test miei il package c’è , con la procedura PURGE. Infine sull’istanza di test 11.1.0.7, Enterprise Edition, il package c’è.

Nella documentazione del package, relativa alla versione 11g,  ho notato che si specifica che il package non viene creato sulla versione standard edition (come mai?). Ho provato a dare un’occhiata allo script di creazione dal quale si ho solo notato come la modifica relativa alla nuova procedura risalga nientemeno che ad agosto 2006.

Sulla documentazione della 10gR2 invece si dice solo che la creazione del package non è in clusa nello script catproc.sql. In effetti non ho trovato dove tale creazione sia invocata, ma su tutti i miei 10g con versione <10.2.0.4 il package è installato.

Sull’istanza 10.2.0.4 Enterprise Edition dove il package non è installato pare vi sia stato un backporting al contrario della 11g, nel senso che l’applicazione del patchset ha rimosso dalle procedure di creazione del database l’installazione del package in caso di Enterprise Edition. Infatti su quella macchina ho installato la patchset prima di creare il database. Su una istanza simile su cui ho seguito lo stesso procedimento ma ho installato Standard Edition il package c’è.

Sulla istanza di test con 10.2.0.4 Enterprise Edition la patchset 10.2.0.4 l’ho installata dopo la creazione del database (creazione avvenuta con la versione 10.2.0.3), quindi il package era già installato ed è stato “patchato” aggiungendo la nuova procedura”.

Conclusione

A parte quindi le variabili che condizionano l’installazione di default del package (mi chiedo se la sua installazione sia rischiosa), la sostanza è che Oracle ha aggiunto uno strumento che in particolari situazioni può essere utile (se funziona veramente :)).

A pensarci bene, nelle situazioni che ho visto io probabilmente ci sarebbe comunque dovuto essere uno stop di tutti gli operatore per impedire l’invocazione della query con conseguente pinning e impossibilità di rimuovere il piano di esecuzione della query.

P.S.

Terminata la scrittura di questo post ho continuato la lettura dei post indicati da Doug Burns, in particolare questo. Viene segnalato che su 10.2.0.4  la procedura DBMS_SHARED_POOL.PURGE non funziona e si rimanda alla nota metalink Note:751876.1 la quale in conclusione spiega che la procedura è “event protected”  quindi va abilitata esplicitamente tramite parametro nel init.ora, settando l’evento 5614566 (che è anche il numero della patch).  Questo rende un po’ la vita difficile

Informazione di servizio: Seminario

venerdì 22 agosto 2008 alle 22:56 | Pubblicato su Diario, Performance Tuning | 6 commenti
Tag: , , ,

Evidentemente ho un certo sesto senso; due giorni fa ho aggiunto al mio “blogroll” e segnalato esplicitamente con un post il link al sito di Kyle Hailey. Ieri sera mi è arrivata una “newletter” da Oracle University in cui spicca (almeno a me è subito balzato all’occhio) la notizia che Oracle University ha organizzato per il 13 e 14 Ottobre prossimi un seminario tenuto proprio da Kyle Hailey. Seguendo il link si trovano tutte le informazioni. L’anno scorso (più meno nello stesso periodo mi pare) ci fù Jonathan Lewis, bisogna quindi ammettere che in qualche modo Oracle University Italia organizza delle cose interessanti. Purtroppo (sigh, sob ,sigh e strasob) ho poche possibilità di partecipare a un tale evento, anche se mi piacerebbe molto. Purtroppo nella mia situazione lavorativa è già stato un privilegio partecipare al corso “Oracle Database 10g: Workshop Administration II” per conseguire la certificazione OCP.

In ogni caso mi sembra giusto segnalare l’evento che interessa chiunque voglia approfondire al tematica del performance tuning.

Un’altra fonte di informazioni su “Performance Tuning”

mercoledì 20 agosto 2008 alle 20:22 | Pubblicato su Diario, Performance Tuning | 3 commenti
Tag: , ,

Già qualche mese fa (Alex Gorbachev l’annunciò il 22 Maggio scorsoKyle Hailey ha varato un nuovo sito web dedicato interamente ad ASH, il sito è intitolato “ASH Masters“. Solo oggi, dopo un po’ di tempo, sono ripassato sia sul sito perfvision.com sia su ashmasters.com e dopo aver dato un’occhiata ai documenti pubblicati mi sono deciso ad aggiungere tale sito nel mio blogroll.  La posizione è l’ultima, la 16 ma solo perchè non voglio stare a cambiare tutta la numerazione, non certo per importanza. La quantità e la qualità del materiale disponibile sia sul sito ASH Masters e perfvision.com è notevole per cui non potevo esimermi dal segnalare tale fonte. L’argomento è come anticipato nel titolo “performance tuning”. Kyle Hailey è un esperto in analisi delle prestazioni di database Oracle e in particolar modo è concentrato sulla divulgazione della potenzialità delle informazioni disponibili attraverso quella nuova caratteristica introdotta con Oracle 10g chiamata “Active Session History (ASH)”.

Sul suo sito vi sono numerose “presentazioni” relative ai seminari tenuti da lui incentrati sull’analisi delle prestazioni su database Oracle. In più Kyle Hailey mette a disposione dei tool per simulare ASH senza dover acquisire costosissime licenze. Invito quindi chiunque sia interessato all’argomento a visitare i due siti e visionare la documentazione pubblicata.

Pagina successiva »

Crea un sito o un blog gratuitamente presso 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