Bind peeking e piani di esecuzione: DTY

In questi giorni uno sviluppatore mi ha sottoposto una query abbastanza semplice, che però veniva eseguita troppo lentamente dalla nuova versione della nostra webapp.
Ho subito cercato di replicare il comportamento della query via sqlplus, anche con le variabili di bind, e ho visto che effettivamente la query era lenta.
Dopo avere controllato rifatto le statistiche (macchina di test), tutto è tornato normale e la query era velocissima. Da un rapido esame dell'autotrace era evidente che il piano soffriva di statistiche sballate, ma ciò era nelle attese sul database di test, in cui le tabelle vengono caricate e scaricate continuamente.

La situazione per il thin client Java invece non migliorava, anzi a tratti peggiorava.
Ho fatto attivare il trace dalla sessione Java, ma simpaticamente proprio il piano di esecuzione che mi interessava non ha fatto a tempo a finire nel file di trace; pazienza, tanto già sapevo che era sballato e che un piano corretto esisteva (quello scelto via sqlplus e visto con autotrace), l'importante era capire perché. I dati di consistent gets/query, nel trace, erano completamente diversi. La query via Java muoveva 1000 volte la quantità di dati mossa da sqlplus, con le stesse 807 righe risultanti.

Mi sono ricordato di un post di Cristian Cudizio sul peeking, nel quale veniva citato sia il metodo di Alberto Dell'Era per estrarre le variabili di bind usando la feature non documentata dbms_xplan.display_cursor(null, null, 'ADVANCED'), sia un post di Jonathan Lewis in cui ho rapidamente identificato la stessa mia situazione.

Subito ho tentato di vedere le variabili di bind con il metodo di Alberto, ma serve almeno l'accesso alla V$SESSION, e non avevo molta intenzione di indagare, visto che, a mio modo di vedere, avrebbe dovuto funzionare con gli stessi privilegi necessari per l'autotrace.
Ho utilizzato invece il trace 10g via DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE), applicandola solamente alle sessioni del pool di connessioni esclusivo dello sviluppatore in questione.
Per ogni sessione del pool ho avviato il trace con
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => sid, binds => true). Non serve infatti il serial#.

È risultato che sqlplus eseguiva la query velocemente perché io correttamente impostavo il tipo delle variabili di bind a char(5), mentre il thin client Java finiva per impostare il tipo a varchar2.

Dove sono disponibili queste informazioni? Nel file di trace era presente la sezione delle variabili di bind, e per tutte era indicato, oltre il valore, una serie di dati tra cui
oacdty=01
che contraddistingue il tipo varchar (DTY = data type), mentre il tipo di sqlplus era char/nchar (96).

Vi risparmio il piano di esecuzione sbagliato, quasi comico.
Ho potuto vagliare comunque, in seguito, la bontà del SQL tuning advisor di Enterprise Manager, che con una comprehensive analysis mi ha fornito sia il piano sbagliato che quello corretto, proponendomi però di applicare un SQL profile alla query, invece che risolvere correttamente il problema.