Introduzione
Quando si realizza una applicazione Java che si basa su RDMBS, si utilizza l'API JDBC (Java DataBase Connectivity) per dialogare con il database. La comprensione dei meccanismi di funzionamento del RDMBS che si sta utilizzando è una premessa importante per riuscire a scrivere applicazioni che siano in grado di soddisfare i requisiti di prestazioni richiesti. Lo scopo dell'articolo è quello di mostrare come quando il database è Oracle l'uso dei prepared statement in Java sia la soluzione da scegliere nella stragrande maggioranza dei casi; verranno forniti tutti gli strumenti per verificare i risultati ottenuti in modo da poter ripetere le stesse prove su casi d'uso reali. L'obiettivo che ci si prefigge è quello di ottenere applicazioni scalabili, la misura della scalabilità della soluzione proposta verrà effettuata usando le informazioni di trace di Oracle, le statistiche delle sessioni utente e quelle globali del database.

Oracle, prestazioni e scalabilità
Quando si sceglie un database è importante capire per cosa esso è stato progettato e quali sono le tipologie di applicazioni in cui è veramente conveniente utilizzarlo. Oracle è conosciuto come un database per applicazioni entreprise in cui si gestiscono enormi quantità di dati e/o di utenti concorrenti; è in questi ambiti che Oracle mostra il meglio di sè e soprattutto giustifica il suo prezzo. L'aspetto su cui ci si concentrerà è quello delle prestazioni in ambiente multiutente, ovvero sui meccanismi che consentono alle applicazioni di continuare a funzionare in maniera adeguata al crescere degli utenti connessi al sistema. Si parla in questo caso di scalabilità, ovvero dell'andamento delle prestazioni in relazione al variare delle sessioni contemporanee. In questo caso la rilevazione di come si comporta il sistema non deve essere condotta solo in caso monoutente (come avviene nella stragrande maggioranza dei casi quando si esegue il test prestazonale di una applicazione), ma deve essere valutata considerando sollecitazioni simultanee.
In ambito multiutente deve essere posta particolare attenzione alle risorse condivise in quanto oltre al tempo di accesso esclusivo alla risorsa è necessario considerare anche il tempo di sincronizzazione per accedervi e il tempo che le altre sessioni rimangono in attesa. Si consideri ad esempio un sistema in cui ogni processo abbia sia una propria area di memoria privata sia una condivisa con tutti gli altri processi presenti nel sistema, nel caso sia presente una sola entità i tempi di attesa per la risorsa condivisa sono praticamente nulli; se invece nel sistema sono presenti decine, centinaia o addirittura migliaia di utenti concorrenti ognuno di loro per accedere alla risorsa condivisa dovrà aspettare il proprio turno. Se ogni processo utilizza l'area comune per parecchio tempo il sistema sarà inevitabilmente lento e si comporterà sempre peggio all'aumentare del numero degli utenti connessi. Nel mondo reale ci sonomolti casi in cui si verificano situazioni del generem si consideri ad esempio, quando si va a fare la spesa al supermercato, si sceglie un articolo e lo si mette direttamente nel carrello, quando si vuole comprare qualcosa al banco degli affettati si deve prendere il numero e aspettare il proprio turno; se al banco non c'è nessun altro cliente la spesa può procedere normalmente, se invece ci sono molte persone inevitabilmente si crea una fila. Il tempo che una persona impiega per essere servita al banco si traduce in tempo di attesa di tutte le altre persone. Per ridurre il tempo di attesa si può agire su due fattori:

1. Aumentare i commessi che servono al banco
   
2. Diminuire il tempo in cui una persona viene servita
   

Un sistema multiutente soffre dello stesso tipo di problemi, ovvero dell'accesso esclusivo alle risorse condivise; se si è in grado di limitare l'uso delle aree comuni allo stretto indispensabile, l'applicazione, all'aumentare degli utenti concorrenti, avrà un miglior comportamento rispetto ad altre che non seguono le stesse regole. Esistono tecniche generali indipendenti dalla specifica applicazione che consentono di raggiungere l'obiettivo della scalabilità in ambito Oracle? Fortunatamente la risposta è sì e la più importante è l'uso delle bind variables.

Cosa sono le bind variables?
Quando si scrive una query SQL che deve recupera dei dati in base alla sezione dell'utente l'approccio più intuitivo che viene in mente è il seguente:

......
String SQL="Select campo1,campo2,campo3 from tabella1 where campo4='" + valoreUtente + "'";
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery(SQL);
......

In questo caso i valori dei campi sono cablati direttamente all'interno della query.
L'utilizzo delle bind variables applicato all'esempio precedente produrrebbe un codice del tipo:

......
String SQL="Select campo1,campo2,campo3 from tabella1 where campo4=?";
PreparedStatement ps=connection.prepareStatement(SQL);
ps.setString(1,valoreUtente);
ResultSet rs=ps.executeQuery();
..........

a prima vista le due versioni non sembrano troppo diverse, invece l'utilizzo di una tecnica rispetto all'altra ha profonde implicazioni sulla scalabiltà delle applicazioni Oracle, ma anche sulla sicurezza (non solo per applicazioni Oracle). Per capire il perchè di questa affermazione è utile addentrarsi all'interno dei meccanismi che regolano l'esecuzione delle query Oracle.

Quali sono i passi che Oracle compie per eseguire una query.
Quando si sottomette una query ad oracle vengono eseguite le seguenti operazioni (per avere un maggiore dettaglio delle quattro fasi si rimanda a [1])
Parse: controllo sintattico della query, in pratica si verifca che l'SQL sia corretto
Creazione del piano di accesso: produzione di un piano di accesso ottimale per la sessione utente corrente
Esecuzione della query: il piano di accesso viene utilizzato per recuperare/modificare i dati
La fase di parse, dopo il controllo sintattico, verifica che lo stesso SQL non sia già stato sottomesso; Oracle mantiente all'interno della RAM un'area chiamata SharedPool dove sono contenuti gli statement SQL e i loro piani di acesso. Due statement SQL sono uguali quando coincidono letteralmente, considerando anche le maiuscole e le minuscole, per esempio queste due query

Select campo2 from Tabella
Select Campo2 from Tabella

sono considerate diverse (a causa della c maiuscola e minuscola)
Se la query viene trovata all'interno della SharedPool la fase di ottimizzione viene saltata e passa a quella successiva.
La fase di ottimizzazione è la parte più complicata del processo ed è decisamente CPU bound in quanto si deve stabilire qual è, in base alle informazioni presenti all'interno del sistema, il piano di accesso più efficiente. Se ottimizzare il piano di accesso di una query con una sola tabella è semplice, valutare qual è il più conveniente per una query in cui sono in join dieci o più tabelle ognuna con predicati diversi sulle colonne non è affatto banale; in linea teorica se ci sono n tabelle in join fra di loro ci sono n! (N fattoriale) possibili modalità di accesso ai dati; 10! vale 3 628 800.
Non solo la fase di creazione del piano di accesso consuma molta CPU, ma richiede anche l'accesso a strutture dati condivise; il calcolo del costo della query richiede la valutazione di svariate informazioni (per esempio il numero di righe di una tabella, la presenza o meno di un indice su una colonna, etc) condivise il cui accesso deve avvenire in maniera esclusiva per poter garantire la coerenza dell'operazione. Oracle regola l'accesso alle informazioni comuni con delle strutture di sincronizzazione chiamate latch. Queste strutture (descritte più ampiamente in [2] e soprattutto [3]) agiscono secondo uno schema "willing to wait" ovvero se la risorsa è bloccata il processo va in sleep per un certo periodo di tempo e poi riprova l'accesso; dalla discussione precedente risulta che meno latch si acquiscono (ovvero a meno risorse comuni si accede) tanto piu' l'applicazione risulterà scalabile. A questo punto dovrebbe risultare abbastanza chiaro che ogni qualvolta è possibile evitare la fase di generazione del piano di accesso ai dati si riesce a garantire un maggior livello di concorrenza all'applicazione.
L'utilizzo dei prepared statement consente di ridurre la fase di ottimizzazione in quanto essa viene eseguita una volta sola, al contrario utilizzando gli statement ogni volta si incorre nella fase di ottimizzazione con conseguente maggior uso di CPU e dei latch, riducendo la scalabilità dell'applicazione.

Dalla teoria alla pratica
E' venuto il momento di verificare la teoria con dei test pratici che confermino quanto detto al paragrafo precedente. Se qualcuno ha letto il libro Oracle JDBC della O'Reilly[4] al capitolo 19 si esegue un test di prestazioni che verifica l'efficacia dei prepared statement; i risultati però sono di segno opposto a quello che ci si aspetterebbe, ovvero l'uso degli statement sembra piu' efficiente di quello dei prepared statement almeno fino. Come mai?
Prima di addentrarsi nella soluzione del problema è necessario definire lo scopo dei test ed una più metriche di valutazione. La metrica implicitamente utilizzata nel libro della O'Reilly è il tempo di esecuzione del test misurato lato client (ovvero all'interno del programma JDBC). Questo criterio di valutazione è importante, ma non è sufficiente in quanto non ci dice molto sul reale utilizzo di risorse del server. Per poter valutare l'applicazione nella sua complessità è opportuno aggiungere altre due metriche:

1. Il tempo di esecuzione sul server per singola sessione
   
2. I tempi di esecuzione sul server considerando l'inteo database
   

L'esempio tratto dal libro [4] si basa sulla velocità di inserimento su una singola tabella che puo' essere creata con il seguente script:

create table TestXXXPerf(
id number,
code varchar2(30),
descr varchar2(80),
insert_user varchar2(30),
insert_date date
);

alter table TestXXXPerf add constraint TestXXXPerf_Pk primary key(id);

A questo punto è possibile cominciare a verifcare le prestazioni utilizzando il codice allegato al libro (che è stato inserito in una classe TestInsert insieme a metodi per i test successivi). Se si prova ad eseguire i metodi testInsertOReillyWithBinds e testInsertOReillyWithNoBinds si ottengono risultati in linea con quelli del libro (tempi espressi in msec):

10 insert 50 insert 100 insert 500 insert
Con Prepared Statement 103 210 220 270
Senza Prepared Statement 40 140 210 681

In pratica sembra che per sessioni che eseguono "poche" operazioni l'utilizzo dei preared statement non sia la soluzione migliore, almeno dal punto di vista delle prestazioni lato client. Già quest primi test rivelano qual e' l'approccio più scalabile; infatti il tempo di esecuzione delle insert tramite prepared statement è rimasto costante al variare del numero delle insert.
I risultati pubblicati sul libro della O'Reilly hanno causato un po' di perplessità all'interno della comunità Oracle fino a che sul sito asktom.oracle.com qualcuno ha chiesto a Thomas Kyte, uno dei guru Oracle mondiali, chiarimenti su questi risultati. Tom, si è messo al lavoro ed ha scoperto che i risultati così ottenuti sono frutto di una misurazione particolare, il tempo di caricamento della classe Oracle che implementa java.sql.PreparedStatement. Nei test effettuati infatti la classe PreparedStatement viene caricata per la prima volta all'intero della JVM e questo falsa i test in quanto se l'applicazione usa i prepared statement il tempo di caricamento è unico per tutte le istruzioni SQL eseguite dal programma. Per avere la conferma il metodo testInsertOReillyWithBinds è stato riscritto aggiungendo prima dell'esecuzione delle insert uno statement di select (metodo testInsertOReillyWithBindsV2), mentre il codice di cui viene misurata l'esecuzione è rimasto identico. I risultati sono i seguenti (tempi espressi in msec):

I tempi sono decisamente a favore dell'uso dei prepared statement in tutti i casi. Semplicemente aggiungendo la creazione di un prepared statement prima dei test i risultati hanno assunto una prospettiva diversa. Quello che è interessante scoprire è, oltre al tempo impiegato sul client, quello impiegato sul server. Per poter eseguire tale rilevazione è necessario abilitare il trace (per avere maggiori dettagli sul trace delle sessioni e sul suo utilizzo per il tuning si faccia riferimento a [5]) per la sessione; in questo modo si ottiene un file dove sono registrate le principali operazioni compiute per eseguire gli statement. Il metodo testBindVsNoBindV2 esegue gli stessi test precedenti, ma con il trace abilitato, i file prodotti si trovano sotto la directory indicata dal parametro oracle user_dump_dest. I file grezzi non sono di facile lettura, per ottenere un output più comprensibile è possibile utilizzare l'utility oracle tkprof che viene installata sia sul client che sul server. Il risultato è un file di testo che mostra tutte le istruzioni SQL eseguite evidenziando le fasi di parse, execute e fetch con i relativi tempi. L'analisi del file di trace relativo all'uso degli statement mostra i seguenti risultati:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 502 0.44 0.44 0 18 0 0
Execute 503 0.11 0.17 26 535 11200 1001
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1005 0.55 0.62 26 553 11200 1001

Misses in library cache during parse: 502
Misses in library cache during execute: 1

Il riepilogo conferma le supposizioni fatte, ovvero che per ogni statement eseguito viene eseguita anche un'operzione di parse e che il tempo complessivo dell'esecuzione del test è dovuto al parse. L'ouput della sessione che usa i prepared statement mostra caratteristiche differenti:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.03 0 18 0 0
Execute 503 0.07 0.11 0 34 10223 1001
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 506 0.10 0.14 0 52 10223 1001

Misses in library cache during parse: 3
Misses in library cache during execute: 2

In questo caso c'e' il parse viene eseguito una volta sola e il tempo di esecuzione è dominato dalla fase di execute.
I prepared statement sono da preferire sempre, sia che si consideri come metrica il tempo di esecuzione sul client che quello sul server.
Tutti i test finora presentati sono monoutente; poichè uno degli ambiti di maggior utilizzo di Oracle è quello in cui sono presenti molti utenti concorrenti è importante verificare i risultati finora ottenuti simulando un carico di lavoro di questo tipo. La metrica più adeguata da utilizzare deve essere lato RDBMS perchè l'obiettivo è valutare la reazione del database a fronte di due diverse tecniche di scrittura delle istruzioni SQL. Nel caso di molti utenti concorrenti l'utilizzo del trace delle singole sessioni risulta scomodo, è decisamente più conveniente usare una rilevazione a livello di intero database. Oracle offre uno strumento creato appositamente per verificare le prestazioni dell'intero database che si chiama statspack. La sua installazione richiede il lancio di uno script che crea installa le procedure di rilevazione del carico (per istruzioni dettagliate sull'installazione ed utilizzo di statspack si rimanda a [2]). L'uso di statspack avviene chiamando una stored procedure che "fotografa" lo stato del sistema; quando si hanno a disposizione due "fotografie" del sistema è possibile produrre un report (sotto forma di file di testo) che mostra il livello di prestazioni del database nell'intervallo temporale intercorso.
Sono state eseguite prove con un numero diverso di thread ognuno dei quali inseriva 100 righe nel database, la tabella seguente riporta i risultati salienti estratti dai report prodotti con statspack:

10 thread 50 thread 100 thread
CPU Time (in secondi) (BIND) 2 3 4
CPU Time (in secondi) (NOBIND) 4 8 15
Non Parse CPU% (BIND) 87 87 89
Non Parse CPU% (NOBIND) 69 35 28
Executions/second (BIND) 546 574 661
Executions/second (NOBIND) 454 580 448

I risultati confermano le ipotesi fatte, i prepared statement sono la base per rendere l'applicazione scalabile.
Bind variables e sicurezza
Le argomentazioni viste finora riguardano le prestazioni in ambito Oracle; esiste un argomento altrettanto valido per utilizzarle anche altri database: la sicurezza. Se si scrive il codice senza l'uso delle bind variables si corre il rischio di SQLInjection. Cosa è la SQLInjection? E' una tecnica tramite la quale è possibile eseguire un codice SQL diverso da quello pensato dal programmatore. Si consideri il seguente esempio di procedura di autenticazione basata su tabella relazionale:

create table userlogins (
username varchar2(30) not null,
password varchar2(30) not null
);
alter table userlogins add constraint userlogins_Pk primary key(username);

insert into userlogins values('user','password');
insert into userlogins values('user1','password1');
commit;

Se si usano gli statement per fare la select il codice la stringa sarebbe composta nel seguente modo

SQL="Select * from userlogins where username='" + userName + "' and " +
"password='" + password + "'";

Tutto funziona se gli utenti inseriscono nomi utenti o password tradizionali, ma cosa succede se si prova con la seguente password:

password' or 'a'='a

La select eseguita sul server diventa:

Select * from userlogins where username='username' and password='password' or 'a'='a'

e vengono tornate tutte le righe; con l'utilizzo delle bind variables tale exploit non sarebbe stato possibile in quanto il driver avrebbe gestito correttamente i caratteri ' con le opportune sequenze di escape.

Dalla pratica al mondo reale
Gli esempi riportati fino ad ora illustrano l'utilizzo delle bind variables in casi relativamente semplici, in cui i parametri e le tabelle da interrogare sono noti a tempo di compilazione; esistono casi in cui la selezione utente determina quali tabelle mettere in join e su quali colonne applicare i filtri. Come ci si comporta in tali casi? Si rinuncia alla scalabilità?
Di seguito viene proposta un soluzione relativamente semplice che permette di gestire tutti i casi in cui l'SQL è determinato dalle selzioni utente o si è deciso di riconvertire una applicazione che usa gli statement ad una che usa le bind variables.
La soluzione consiste nel creare la stringa utilizzando il carattere ? e nello tempo mantenere una lista dei parametri di cui effettuare il bind. Si consideri ad esempio

......
String SQL="Select campo1,campo2,campo3 from tabella1 where campo4='" + valoreUtente + "'";
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery(SQL);
......

potrebbe essere tradotta in:

ArrayList parameterList=new ArrayList();
String SQL="Select campo1,campo2,campo3 from tabella1 where campo4=" + "?";
parameterList.add(valoreUtente);

dove valoreUtente è una variabile del tipo corrispondente alla colonna del database su cui si applica la where; al momento dell'esecuzione si potrebbe scrivere il seguente codice:

PreparedStatement ps=connection.prepareStatement(SQL);
for(int i=0;i<parameterList.size();i++) {
Object parameter=parameterList.get(i);
if (parameter instanceOf String) {
ps.setString(i+1,(String)parameter);
} else if (parameter instanceOf Long) {
ps.setLong(i+1,((Long)parameter).longValue());
}//e così via per tutti i tipi che si desidera supportare......
}

Un ulteriore passo in avanti potrebbe essere fatto creando un metodo che prende in ingresso un PreparedStatement e una List ed esegue le setXXXX; in questo modosi porebbe evitare duplicazione di codice.

Conclusioni
I test effettuati sia in ambito mono che multi utente hanno confermato (utilizzando metriche sia lato client che lato server) che per ottenere applicazioni Oracle scalabili è necessario utilizzare i prepared statement. La tecnica, oltre a a garantire prestazioni superiori, fornisce anche un grado di sicurezza maggiore agli applicativi sviluppati in quanto evita in auotmatico tutti i problemi di SQL Injection. E' utile ricordare che e' possibile scaricare gratuitamente (previa registrazione) tutto il software e la relativa documenzaizone Oracle al sito http://www.oracle.com/technology/index.html e utilizzarlo per lo sviluppo delle proprie applicazioni in quanto le licenze si pagano solo quando si installa il database in ambienti di produzione.

Ambiente di esecuzione dei test
Tutti i test sono stati eseguiti su un Pentium M 1,5Ghz 1Gb RAM con disco da 4200 RPM. La versione di Oracle utilizzata è la 10.1.0.2; i driver JDBC utilizzati sono quelli thin versione 10.1.0.3.

Bibliografia
[1] Thomas Kyte - Effective Oracle by Design - Oracle Press, 2003
[2] Thomas Kyte - Expert One-on-One Oracle - Apress, 2003
[4] Donald Bales - Java Programming with Oracle JDBC - O'Reilly, 2001
[3] Steve Adams - Oracle8i Internal Services - O'Reilly, 1999
[5] Cary Millsap - Otpimising Oracle Performance - O'Reilly, 2003

Esempi
Scarica qui gli esempi

Giovanni Cuccu è laureato in ingegneria informatica a Bologna. Attualmente svolge attività di consulenza per lo svilppo di applicazioni web tramite Java, Oracle. Si interessa di applicazioni open source, usa Linux regolarmente ed è autore di un Oracle session profiler open source (scritto in Java) di prossima pubblicazione su sorceforge.