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.

3 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Ciao Cristian,
    in realtà con codice Java succede abbastanza spesso, anche a me è capitato e non avendo accesso al codice Java ho effettuato un trace 10046 sulla sessione con level 12 con quella query, e ho trovato nel trace per “dty” il tipo dato e l’ho confrontato con il datatype della colonna e non combaciavano (il dty sarebbe il numerino relativo al dump del datatype, ogni datatype ha il suo, l’elenco mi pare sia su metalink altrimenti lo togli fuori con la dump), inoltre non era presente alcun valore di bind passato per quel cursore;
    mi sono accorto a quel punto della conversione implicita effettuata da Oracle ed ho dedotto che l’applicazione Java non stava passando il corretto datatype (da qui la conversione implicita) e quindi non utlizzava l’indice come avrebbe dovuto

  2. […] 8 Aprile 2009 di Cristian Cudizio Non molto tempo fa ho scritto un post intitolato “Prestazioni con tipi DATE e TIMESTAMP” che si concludeva con degli interrogativi. Siccome  ogni volta che ho a che fare con i tipi […]

  3. Ciao, a noi è successa una cosa simile, però con Oracle 9, Oracle 10 eseguiva i piani corretti.
    Il codice java era:

    st.setTimestamp(1, new Timestamp(new Date().getTime()));
    il problema nostro è che

    st.setDate(1, new Date());

    in java ignora i minuti/secondi per noi rilevanti.

    Per ovviare al problema usciamo
    st.setObject(i, new oracle.sql.DATE(new java.sql.Timestamp((new Date()).getTime())));

    Questo workaround ci risolve il problema delle prestazioni e della precisione del tipo DATE

    ciao


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

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: