Il tuning dell'RDBMS Oracle richiede esperienza e conoscenze specifiche. In questo breve documento vengono riportati i principali elementi per il tuning di Oracle utilizzando una serie di statistiche prestazionali.
Le statistiche utilizzate come guida nel documento sono ottenute con lo script SQL perf.sql contenuto nel set di utility sqlutil. Tuttavia le statistiche e l'esame dei parametri e' di interesse generale per amministratori e programmatori in ambiente Oracle.
Controllo delle prestazioni e tuning del sistema
Il tuning dell'RDBMS Oracle e' solo uno degli elementi da considerare in un sistema informativo completo.
Tra gli elementi da analizzare con attenzione ricordiamo:
Per una trattazione piu' completa di questi argomenti fare riferimento al documento Ottimizzazione e tuning.
In questo breve documento saranno esaminati solo aspetti relativi al tuning dell'RDBMS Oracle.
Per ottenere maggiori prestazioni nell'accesso ai dati l'RDBMS Oracle cerca di mantenere in memoria (nella System Global Area: SGA) i blocchi disco maggiormente acceduti. In tal modo sucessive letture dallo stesso blocco dati vengono effettuate in memoria anziche' richiedere un accesso a disco.
La statistica Hit ratio buffer cache riporta la percentuale di accessi avvenuti in memoria rispetto al numero totale di accessi.
A) Hit ratio buffer cache (>80%): 93.3%
Il valore ottimale di tale statistica dipende tal tipo di sistema. Per DSS o applicazioni di Dataware House e' opportuno un valore >80%; per sistemi OLTP e' tipico un valore >95%; con i benchmark si cercano valori >99%.
Per effettuare il tuning della buffer cache e' possibile agire sul parametro DB_BLOCK_BUFFERS nel file initX.ora.
L'aumento prestazionale ottenibile con una corretta configurazione della buffer cache e' notevole. L'impatto e' evidente soprattutto nel caso di utilizzo di dischi in raw device. Infatti in tal caso non e' presente alcuna ulteriore cache da parte del sistema operativo ospite.
L'impatto in termini di occupazione di memoria del sistema e' notevole (con 10.000 buffer di 2KB si ha un'occupazione di 20MB). Deve essere quindi posta attenzione in modo da non generare paginazione o swapping sul sistema.
Oracle mantiene in memoria un'ampia serie di informazioni necessarie al funzionamento delle applicazioni. Tra queste aree trovano spazio gli statement SQL in formato gia' compilato (library cache) e le informazioni del Data Dictionary (dictionary cache). Quando queste informazioni debbono essere ricavate da disco si hanno rallentamenti ed un utilizzo maggiore dei dischi e della CPU.
Vi sono diverse statistiche che controllano tale aspetto:
B1) Misses library cache (<1%): .562%
B1.1) Detailed misses library cache (SQL AREA-824145): .565%
B1.2) Detailed misses library cache (TABLE/PROCEDURE-340755): .545%
B1.3) Detailed misses library cache (BODY-123): 21.138%
B1.4) Detailed misses library cache (TRIGGER-3504): .542%
B1.5) Detailed misses library cache (INDEX-21): 0%
B1.6) Detailed misses library cache (CLUSTER-15): 0%
B1.7) Detailed misses library cache (OBJECT-0): 0%
B1.8) Detailed misses library cache (PIPE-0): 0%
B2) Misses dictionary cache (<10%): 8.261%
Se le statistiche riportano valori piu' alti di quelli suggeriti (1% per la library cache, 10% per la dictionary cache) si puo' agire sul parametro SHARED_POOL_SIZE del file initX.ora aumentandolo adeguatamente.
Un installazione minimale di Oracle richiede uno shared_pool_size di 3.500.000; se si utilizzano in modo pesante integrity constraint, trigger e stored procedures lo shared_pool_size ottimale puo' superare i 20.000.000.
Anche in questo caso l'impatto sulla memoria del sistema e' notevole: attenzione alla paginazione.
L'accesso completo a tutti i dati contenuti in una tabella (ovvero quando non si accede attraverso un indice) viene denominato scansione sequenziale. L'accesso all'intero contenuto di una tabella e' particolarmente pesante e deve essere evitato se non necessario.
Per controllare il numero di scansioni sequenziali effettuate e' disponibile la seguente statistica:
H1) Short tables full scans: 797
H2) Long tables full scans: 84
Se il numero di scansioni sequenziali e' elevato o cresce velocemente e' necessario controllare la presenza degli indici e soprattutto (99% dei casi) gli statement SQL utilizzati nelle applicazioni.
Per ottenere le maggiori prestazioni nell'accesso ai dati e' necessario allocare gli spazi in modo contiguo. Quando si creano frammentazioni le prestazioni diminuiscono.
Per ricostruire in modo contiguo gli oggetti si utilizzano i comandi di export/import o, per gli indici, gli statement drop/create.
La seguente statistica riporta gli eventuali oggetti che presentano una frammentazione:
TABLESPACE OWNER SEGMENT TYPE BYTES EXTENTS ---------- --------- --------------------- -------- --------------- ------- IDX OPS$SMART PK_UX_STAT_LOG INDEX 84449280 9 USERS SCOTT EMP TABLE 3225600 9
Oracle mantiene sui segmenti di rollback le versioni precedenti di ogni transazione in corso. Sui rollback possono presentarsi problemi di conflitti e frammentazione.
Dal punto di vista di definizione e' importante definire il numero di rollback e la loro dimensione.
Su sistemi OLTP in genere si definisce un rollback segment ogni 4 utenti. La dimensione viene scelta piu' piccola possibile purche' non si generino extents.
Su sistemi con batch pesanti si utilizzano pochi rollback (spesso uno solo) di notevoli dimensioni. Rollback di notevoli dimensioni diminuiscono la probabilita' del fatidico ORA-1555 snapshot too old.
Spesso entrambe le situazioni convivono sullo stesso DB in orari differenti (eg. fase on-line e batch) oppure le applicazioni utilizzano in maniera esplicita segmenti di rollback con lo statement set transaction use rollback segment ROLLX.
Le seguenti statistiche controllano le contese presenti sui segmenti di rollback:
C1) System undo header frequence (<1%): 0%
C2) System undo block frequence (<1%): 0%
C3) Undo header frequence (<1%): 0%
C4) Undo block frequence (<1%): 0%
Se le statistiche riportano valori piu' elevati di quelli suggeriti (>1%) e' opportuno creare un numero maggiore di rollback segment.
Ogni modifica che deve avvenire su un datafile viene posta sui file di redo log.
Ad occuparsi delle scritture sui redo log e' un processo (o un thread su NT) apposito il LOG WRITER. Quando un redo log file e' pieno Oracle effettua un checkpoint sul database sincronizzando i datafile e quindi passa al redo log sucessivo. Se e' attivo il log archiving il redo log liberati vengono salvati nella directory specificata (cfr. Utilizzo della modalita' di log archiving in Oracle).
I checkpoint vengono inoltre effettuati ad intervalli di tempo regolari (LOG_CHECKPOINT_TIMEOUT) e/o al raggiungimento di un certo numero di scritture di blocchi (LOG_CHECKPOINT_INTERVAL).
Le frequenze di switch tra redo log possono essere ricavate dai file di ALERT del database.
Per ottenere le migliori prestazioni, in particolare con sistemi che effettuano transazioni particolarmente pesanti:
I vantaggi prestazionali ottenuti possono essere notevoli.
Per un ulteriore tuning sui redo log si possono utilizzare le segeutni statistiche:
D) Redo log space req. (near 0): 12
E1) Hit ratio redo alloc (<1%): .008%
E2) Hit ratio immediate redo alloc (<1%): NA
E3) Hit ratio redo copy (<1%): NA
E4) Hit ratio immediate redo copy (<1%): 0%
Se il valore delle richieste di spazio (redo log space req.) non e' vicino allo 0 puo' essere aumentata la dimensione del buffer in memoria. Il parametro che ne definisce la dimensione e' LOG_BUFFER contenuto nel file initX.ora. Il valore di default presente dopo l'installazione di Oracle e' generalmente insufficiente se sul sistema sono presenti attivita' batch (anche limitate).
Le altre statistiche riportano le contese sui latch dei redo log. Tali contese si presentano raramente con sistemi con una sola CPU. In caso di sistemi con piu' CPU puo' essere utile aumentare il numero di latch con il parametro LOG_SIMULTANEOUS_COPIES contenuto nel file initX.ora. E' raro dover ricorrere a tale settaggio (ed ad alcuni altri qui non descritti in dettaglio).
Ecco un esempio di configurazione "generosa":
Le operazioni ordinamento avvengono con
molta frequenza sui DB relazionali. Possono essere richiamati in modo esplicito
con la clausola ORDER BY dello statement SELECT o in modo implicito in parecchi
altri casi: clausola GROUP BY, utilizzo di algoritmi di sort da parte dell'ottimizzatore,
creazione di indici, ... Oracle cerca di effettuare tutti gli
ordinamenti in memoria, se la dimensione della struttura da ordinare e' troppo
elevata per l'area definita per i sort, l'ordinamento viene effettuato su disco. La statistica che rileva il numero di
sort effettuati e' la seguente: G1) Sorts in memory: 53132 G2) Sorts on disk: 43 Se il numero di ordinamenti effettuati
su disco e' elevato e' possibile agire sul parametro SORT_AREA_SIZE
del file initX.ora aumentandolo adeguatamente. Tale parametro
agisce sulle singole aree utente (PGA). Se la dimensione delle tabelle da ordinare
e' molto elevata e' comunque impossibile effettuare tutte le operazioni di sort
in memoria. E' pertanto necessario definire i corretti parametri di storage
ai tablespace utilizzati come aree temporanee. Di notevole aiuto, per la gestione dei
segmenti temporanei e la creazione di indici, sono alcune novita' introdotte
nella versione 7.3 dell'RDBMS (cfr. Novita' presenti in Oracle 7.3). Molte applicazioni che operano su Oracle
effettuano un pesante accesso ai dati. Per ottenere migliori prestazioni e'
spesso necessario agire quindi sugli accessi ai dischi. La prima indicazione generale e' quella
di ripartire il carico tra dischi differenti in modo da consentire al sistema
di operare in parallelo su piu' dischi ed evitare seek. In generale andrebbero
posti su dischi differenti: i datafile contententi le tabelle, i datafile contenti
gli indici, i file di redo log, i datafile contenti i rollback segment, i datafile
contententi i temporary segment, gli archived redo log, i programmi eseguiti,
eventuali file di swap o di paginazione, ... L'ordine che e' stato dato e' indicativo
dell'impatto prestazionale, anche se questo dipende dal tipo di applicazioni
(eg. per la creazione di un indice ha rilevanza il posizionamento dei datafile
temporanei). E' opportuno controllare carico presente
sui vari datafile con la seguente statistica: Se il carico non e' correttamente distribuito
possono essere riorganizzati gli spazi (sia logicamente allocando oggetti su
tablespace differenti, sia fisicamente utilizzando datafile su supporti fisici
diversi). L'utilizzo dello striping fisico sui
dischi offre notevoli vantaggi prestazionali con Oracle (cfr. Utilizzo
di un Volume Manager su Unix: note pratiche). Sui datafile maggiormente
acceduti, su cui il carico non possa essere ulteriormente spezzato su piu' dispositivi,
lo striping e' essere la soluzione piu' vantaggiosa. Utilizzo dei dischi per datafile I diversi tipi di datafile hanno utilizzi
differenti. Quando l'utilizzo rilevato dalle statistiche si discosta da quello
previsto e' opportuno effettuare ulteriori indagini. I datafile delle tabelle vengono utilizzati
soprattutto in lettura (su un DSS solo in lettura). Sono i datafile di gran
lunga piu' utilizzati. I datafile degli indici sono utilizzati
in maniera analoga ai data file delle tabelle, hanno un numero di scritture
generalmente inferiore ed un numero di scritture superiore (basta pensare all'inserimento
di un nuovo record: una scrittura sulla tabella; diverse scritture per tutti
gli indici presenti sulla tabella nelle foglie e, eventualmente, nell'albero
B-TREE). I datafile temporanei sono utilizzati
soprattutto in scrittura (per creare i temporanei necessari agli ordinamenti). I datafile dei rollback sono utilizzati
soprattutto in scrittura (poiche' Oracle aggiorna ogni volta i segmenti di rollback
con i blocchi modificati). Il datafile system e' utilizzato soprattutto
al bootstrap del sistema se non sono utilizzati pesantemente statement DDL. Ogni DB ha un differente utilizzo in termini
di numero di utenti, connessioni, transazioni, ... E' importante monitorare
tali eventi del sistema per comprendere la tipologia di utilizzo presente e
rilevare eventuali variazioni o anomalie. I1) Logon: 799 I2) Commit: 2014 I3) Rollback: 933 Vi sono alcune statistiche del sistema
operativo ospite che indicano il tipo di carico presente sul sistema in generale.
Tali statistiche sono di notevole interesse per un corretto tuning e dimensionamento
dell'RDBMS Oracle. L'esame di tali statistiche e' al di fuori degli obiettivi
di questo documento. Possono essere fornite tuttavia una serie di indicazioni
per i casi piu' comuni. Utilizzo eccessivo di memoria Nel caso di utilizzo eccessivo di memoria
il sistema viene impegnato nella paginazione (ne conseguono anche un utilizzo
maggiore di disco e CPU). Le prestazioni del sistema in generale sono pessime. In questo caso e' necessario limitare
al massimo l'occupazione di memoria agendo in diminuzione sui parametri DB_BLOCK_BUFFERS
e SHARED_POOL_SIZE che hanno il maggior
impatto in termini di memoria. Nel caso di un grande numero di utenti
collegati puo' essere utile attivare la modalita' multi threaded server. Utilizzo eccessivo di CPU Nel caso di un utilizzo eccessivo di
CPU vi sono poche possibilita' di azione; di frequente e' necessario migliorare
le applicazioni o cambiare sistema. Un utilizzo eccessivo di CPU si ha quando
la parametro SHARED_POOL_SIZE non e' sufficientemente ampio
e vengono effettuate troppe ricompilazioni di statement SQL. Un utilizzo inferiore di CPU si ha settando
il parametro SPIN_COUNT a zero (e' il numero di cicli di busy
waiting che si effettuano prima di addormentarsi su un latch). Utilizzo eccessivo dei dischi Quando l'utilizzo dei dischi e' eccessivo
deve essere determinato quali sono i dischi su cui vi e' un sovraccarico. Da questo punto di vista le statistiche
dell'RDBMS sono molto precise ed utili.Tra i vari parametri da aumentare vi
e' il DB_BLOCK_BUFFERS. Di frequente un utilizzo eccessivo dei
dischi e' sintomo di statement SQL non correttamente impostate e che non utilizzano
indici. Va tutto bene Va tutto bene sul sistema operativo,
perche' preoccuparsi quindi? Naturalmente ci si preoccupa se, nonostante
il sistema operativo riporti che ma macchina non e' impegnata, le prestazione
dell'RDBMS non sono buone. In questo caso debbono essere controllati
eventuali conflitti sulle risorse, timeout di sistema o lock sui dati. (NdE Questo documento e' molto vecchio ma comunque
ancora valido per la maggior parte dei contenuti. I programmi di raccolta di statistiche
piu' aggiornati si trovano ora su
SourceForge).
Le statistiche descritte possono essere
ottenute in pochi secondi utilizzando lo script perf.sql
oppure utilizzando quando descritto nei vari manuali Oracle. Il contenuto dello script perf.sql
e' facilmente leggibile a chiunque abbia famigliarita' con l'SQL. E' cosi' possibile
risalire alle selezioni utilizzate per ottenere le varie statistiche riportate. Questo documento e' riferito alla versione
2.2 dello script perf.sql contenuto in sqlutil. Il software e'
liberamente utilizzabile. Tutte le statistiche descritte sono significative
solo se estratte dopo che l'RDBMS Oracle ha operato per un ampio lasso di tempo
con un carico reale. Per ottenere maggiori informazioni sulla
configurazione del database Oracle si puo' utilizzare lo script det.sql
sempre contenuto in sqlutil. Il database utilizzato quale esempio
e' un RDBMS Oracle 7.2.3, analoghe informazioni si ottengono da database Oracle
7.X. Il sistema operativo ospite e' un sistema SUN Solaris 2.5.1, tutti gli
elementi riportati sono tuttavia generali e valgono identicamente anche per
installazioni su altri sistemi operativi (eg. Microsoft NT, ...). Come riferimento riportiamo l'output completo
dello script perf.sql: Testo: Statistiche prestazionali di Oracle
ALTER DATABASE
ADD LOGFILE ('/data/BENCH10G/log_1/redologBENCH10G_BIG1.rdo') SIZE 512M;
ALTER DATABASE
ADD LOGFILE ('/data/BENCH10G/log_1/redologBENCH10G_BIG2.rdo') SIZE 512M;
ALTER DATABASE
ADD LOGFILE ('/data/BENCH10G/log_1/redologBENCH10G_BIG3.rdo') SIZE 512M;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 1;
alter system set log_checkpoint_timeout=0 scope=both;
alter system set log_buffer=104857600 scope=spfile;
alter system set db_cache_size=503316480 scope=spfile;
File name BYTES Letture Scritture
---------------------------------------- ---------- ---------- ----------
/dev/rdsk/c1t1d0s6 1992294400 213135 13703
/dev/rdsk/c1t2d0s6 513802240 50152 9084
/usr1/oracle/dbs/systa.dbf 52428800 50428 2565
/usr1/oracle/dbs/toola.dbf 15728640 574 0
PERF.SQL v.2.2
STATISTICHE
--------------------------------------------------------------------------------
Data sistema: 1997-may-30 16:33
A) Hit ratio buffer cache (>80%): 93.3%
B1) Misses library cache (<1%): .562%
B1.1) Detailed misses library cache (SQL AREA-824145): .565%
B1.2) Detailed misses library cache (TABLE/PROCEDURE-340755): .545%
B1.3) Detailed misses library cache (BODY-123): 21.138%
B1.4) Detailed misses library cache (TRIGGER-3504): .542%
B1.5) Detailed misses library cache (INDEX-21): 0%
B1.6) Detailed misses library cache (CLUSTER-15): 0%
B1.7) Detailed misses library cache (OBJECT-0): 0%
B1.8) Detailed misses library cache (PIPE-0): 0%
B2) Misses dictionary cache (<10%): 8.261%
C1) System undo header frequence (<1%): 0%
C2) System undo block frequence (<1%): 0%
C3) Undo header frequence (<1%): 0%
C4) Undo block frequence (<1%): 0%
D) Redo log space req. (near 0): 12
E1) Hit ratio redo alloc (<1%): .008%
E2) Hit ratio immediate redo alloc (<1%): NA
E3) Hit ratio redo copy (<1%): NA
E4) Hit ratio immediate redo copy (<1%): 0%
F) Free list contention (<1%): 0%
G1) Sorts in memory: 53132
G2) Sorts on disk: 43
H1) Short tables full scans: 76097
H2) Long tables full scans: 4984
I1) Logon: 799
I2) Commit: 2014
I3) Rollback: 933
File name BYTES Letture Scritture
---------------------------------------- ---------- ---------- ----------
/dev/rdsk/c1t1d0s6 1992294400 213135 13703
/usr1/oracle/dbs/systa.dbf 52428800 50428 2565
/usr1/oracle/dbs/toola.dbf 15728640 574 0
/dev/rdsk/c1t2d0s6 513802240 52 9084
TABLESPACE OWNER SEGMENT TYPE BYTES EXTENTS
---------- --------- -------------------- -------- --------------- -------
IDX OPS$SMART PK_UX_STAT_LOG INDEX 84449280 9
USERS SCOTT EMP TABLE 3225600 9
Data: 28 Maggio 1997
Versione: 1.3.2 - 14 Febbraio 2011
Autore: mail@meo.bogliolo.name