PERFORMANCE E TUNING

INDICE

Obbiettivi

1 2 3 5

Principali metodi di accesso ai dati Ottimizzazione delle query Tracciamento dello statement Sql Trace Autotrace Appendice 7 12 12 13

Quali viste da interrogare

I Parte

ObbiettiviEffettuare il tuning degli statement SQL significa attuare le strategie necessarie per: accedere ai dati il pi velocemente possibile non aspettare le risorse accedere il numero minimo di blocchi accedere ai blocchi nella cache condividere il codice Questi obbiettivi possono essere raggiunti effettuando il tuning dello statement. Perch il tuning sia efficace comunque importante che lamministratore del database abbia anticipatamente provveduto a creare il db nel miglior modo possibile rispetto al tipo di attivit che vi si dovr svolgere. I database dovranno infatti essere correttamente configurati in funzione del tipo di attivit : transazionale (OLTP) o batch (DSS). E sempre compito dei dba assicurarsi che vengano create tablespace distinti per tabelle e indici, che i rollback segment siano creati e dimensionati correttamente, che la buffer cache e la shared pool area siano correttamente dimensionate. Per effettuare il tuning degli statement, pu essere utile rivedere come Oracle lavora per rispondere ad una richiesta di dati.

Come Oracle processa gli statement SQLEsistono tre fasi distinte nel processamento degli statement: parsing, executing, fetching

ParsingIn questa fase vengono effettuati i seguenti controlli: Controllo sintattico: lo statement deve essere conforme alle regole dellSQL

Controllo semantico: tutti i riferimenti agli oggetti del database devono essere validi Controllo di sicurezza: lutente deve avere i privilegi necessari ad eseguire lo statement Determinazione del piano di esecuzione: scelta degli step successivi necessari per la restituzione dei dati richiesti La fase di parsing notevolmente dispendiosa, e quindi per ottimizzare laccesso al db risulta fondamentale ridurre il parsing non necessario. A tal fine Oracle memorizza in cache, nella shared pool area, gli statement pi recentemente eseguiti insieme al loro piano di accesso. La memorizzazione avviene tramite lapplicazione di un algoritmo di hash allo statement; ad una successiva esecuzione del medesimo statement la funzione di hash applicata allo statement restituisce un valore gi presente in memoria, e dunque la query viene eseguita senza rifare il parse. Per poter sfruttare questa capacit di Oracle, importante scrivere query identiche esattamente allo stesso modo, facendo attenzione a spazi, ecc. Un suggerimento utile pu essere quello di salvare sempre le query da eseguire pi volte in un file. Un altro importante accorgimento lutilizzo delle bind variable nel caso in cui lo stesso statement venga eseguito pi volte, assegnando valori diversi alle variabili. Se ad esempio si vuole sapere il nome di un impiegato di cui si conosce la matricola, e questo per tutti gli impiegati, notevolmente vantaggioso scrivere la seguente query: SELECT firstname, surname FROM emp WHERE emp_id = :empid; dove empid la bind variable; in questo modo dello statement viene effettuato il parse una sola volta, e se anche un altro utente deve effettuare la stessa query, la trover gi in memoria.

ExecutingNel caso di operazioni DML, lesecuzione consiste nelleffettuare le modifiche, che diventeranno definitive dopo la COMMIT. Nel caso di SELECT, lesecuzione consiste nella lettura del cursore per loperazione di fetch.

FetchingQuesta fase restituisce leffettivo risultato della query. Se in una sola volta vengono restituite pi righe, si parla di array fetch. SQL*Plus esegue automaticamente larray fetch, in altri ambienti necessario, e consigliato, richiederlo in maniera esplicita.

Principali metodi di accesso ai datiConsideriamo per semplicit laccesso ai dati di una sola tabella. Oracle esegue una delle seguenti operazioni, per tornare i dati allutente: Lettura di tutta la tabella fino all HIGH WATER MARK, che il blocco pi alto in cui siano stati scritti dati della tabella. Non necessariamente tutti i blocchi fino allhigh water mark sono riempiti; in particolare nelle tabelle che sono soggette a molte cancellazioni, ci saranno molti buchi, e gli ultimi blocchi potrebbero non contenere dati. Pur tuttavia, Oracle legge tutti i blocchi fino a questo limite superiore. Questo accesso detto full table scan. Lettura dei dati tramite il ROWID. Il rowid una pseudo colonna, contenente lindirizzo fisico della riga. Tipicamente laccesso via ROWID si ha quando si accede ad una tabella tramite lindice, che ad ogni valore distinto restituisce gli indirizzi delle righe relative.

Accesso per indice. Oracle legge lindice e per i valori cercati restituisce le righe relative, che vengono accedute tramite il rowid. Accesso hash. Oracle applica una funzione di hash alla colonna su cui si hanno delle condizioni selettive, e ritorna tutte le righe che corrispondono allo stesso valore della funzione di hash. Considerando invece di interrogare due o pi tabelle in relazione tra loro, ovvero due o pi tabelle in join, esistono tre possibili metodi distinti per effettuare il join. Sort merge join. Consiste nellordinare le colonne in join separatamente per ciascuna delle tabelle (sort), e poi nel fare un merge delle tabelle. Se si hanno n tabelle, questo processo viene iterato n-1 volte, facendo join merge di 2 tabelle (o result set), alla volta. Questo metodo non richiede lesistenza di indici, e viene usato particolarmente quando le tabelle sono abbastanza grandi e di dimensioni paragonabili tra loro. Nested loop join. Supponiamo per semplicit di avere solo 2 tabelle. Viene effettuato un full table scan di una delle due tabelle, solitamente la pi piccola delle due o quella che non ha un indice sulla colonna di join. Per ciascuna riga restituita dalla prima operazione, si cerca la riga corrispondente sulla seconda tabella; il secondo accesso performante se il match si pu effettuare a colpo sicuro, ovvero tipicamente se si fa un accesso via indice. Il nested loop consigliabile se una delle tabelle considerevolmente pi piccola dellaltra. Hash join. Viene costruita una tabella di hash per la tabella pi grande. Quindi viene fatto lo scan della tabella pi piccola, per trovare le righe che hanno valori corrispondenti nella tabella di hash. Questo tipo di approccio porta ottimi risultati se la tabella di hash pu essere contenuta completamente in memoria, e se le due tabelle hanno dimensioni diverse Si deve considerare che se nella query vi sono operazioni di ordinamento o funzioni di gruppo, Oracle dovr effettuare delle operazioni di sort sul result set. Le operazioni di sort vengono effettuate in memoria se c sufficiente spazio (nellarea la cui dimensione definita da SORT_AREA_SIZE), altrimenti vengono effettuate su disco (temporary segment). Le operazioni di sort possono essere molto costose in questo secondo caso.

Ottimizzazione delle queryPer quasi tutte le query esiste pi di un modo per Oracle di restituire i dati richiesti. Nella fase di parse, tra un numero n di possibili strade, Oracle sceglie quella che ritiene essere la migliore per ritornare allutente i dati richiesti. La scelta del percorso ottimale da seguire viene chiamata Ottimizzazione della query. Oracle possiede un ottimizzatore, che pu lavorare in due modi distinti: A regole; il metodo di ottimizzazione pi vecchio. Lottimizzatore lavora seguendo una serie di regole prefissate , e non utilizza informazioni quali la quantit di dati da trattare e la loro distribuzione statistica. La regola principale, che pu risultare particolarmente dannosa in alcuni frangenti, riguarda la modalit di scelta delluso o non uso di un indice nellaccesso a una tabella: se esiste un indice, lo usa sempre se non esiste un indice, fa una full table scan della tabella A costi; nella scelta del metodo di accesso, valutato tra un numero n di accessi possibili, tiene conto di informazione statistiche dei volumi e della distribuzione dei dati, sia per quanto riguarda le tabelle, che per gli indici. Assegna ad ogni accesso valutato un costo, e sceglie il percorso con il costo minore. Maggiore il costo dello statement SQL, maggiore il numero di possibili percorsi valutati. Per ottenere le statistiche bisogna analizzare le tabelle. Il comando il seguente: ANALYZE TABLE table_name COMPUTE STATISTICS | ESTIMATE STATISTICS (SAMPLE n ROWS | n PERCENT) Es. : analyze table emp estimate statistics 25 percent;

Le due opzioni COMPUTE | ESTIMATE permettono di scegliere se leggere tutta la tabella per collezionare le statistiche, oppure solo un numero prescelto di righe o una percentuale di tabella. Le statistiche collezionate vengono scritte nelle tabelle di dizionario, accedute da Oracle nella fase di scelta del piano di accesso. Le principali statistiche riguardano: per una tabella, il numero di righe, il numero di blocchi usati e vuoti, la lunghezza media della riga, la quantit media di spazio usata in ogni blocco per un indice, il numero di chiavi distinte, il numero di foglie morte dellalbero, la profondit dellalbero. Per ottenere queste informazioni dal dizionario dei dati si possono eseguire le queries: SELECT table_name, owner, num_rows, avg_row_len, chain_cnt, blocks, empty_blocks, last_analyzed FROM DBA_TABLES WHERE table_name=MIA_TABELLA; SELECT index_name, blevel, leaf_blocks, distinct_keys, status, sample_size, last_analyzed FROM dba_indexes WHERE index_name=MIO_INDICE;

Come impostare lottimizzatoreEsistono quattro possibili impostazioni per lottimizzatore: RULE : CHOOSE: ottimizzatore a regole

a costi se le tabelle coinvolte sono state analizzate, a regole altrimenti ALL_ROWS: usa lottimizzatore a costi e sceglie il piano di accesso che ha il costo minore per ottenere tutte le righe restituite dalla query FIRST_ROWS : usa lottimizzatore a costi e sceglie il piano di accesso che ha il costo minore per ottenere la prima delle righe ritornate dalla query. Lottimizzatore, a livello di database, pu essere impostato nel parameter file. Ad esempio nel file init.ora pu essere specificato : OPTIMIZER_MODE = ALL_ROWS A livello sessione, si pu impostare lottimizzatore tramite il comando: ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS Lottimizzatore pu essere influenzato ad usare un preciso piano di accesso ai dati tramite luso degli hints. Gli hints sono pseudocommenti che suggeriscono ad Oracle di considerare, tra i vari piani di accesso possibili, anche quello suggerito dallutente. Se Oracle valuta che il piano di accesso suggerito sia buono, questo verr scelto per lesecuzione della query. Gli hints devono seguire immediatamente listruzione SELECT ed essere compresi tra i limitatori : /*+ */ Un esempio di hint il seguente: SELECT /*+ ALL_ROWS */ Firstname, surname FROM emp; Elenco e descrizione di alcuni degli hints pi comunemente usati: ottimizzatore /*+ ALL_ROWS */ /*+ CHOOSE */ /*+ FIRST_ROWS */ Chiede allottimizzatore a costi di scegliere il cammino con il costo minimo per restituire tutto il result set Chiede allottimizzatore di comportarsi a regole o a costi in funzione dellesistenza o meno delle statistiche Chiede allottimizzatore a costi di scegliere il cammino con il costo minimo per la prima riga del result set

/*+ RULE */ Metodi di accesso /*+ FULL (table)*/ /*+ HASH (table) */ /*+ INDEX (table index)*/ /*+ INDEX_FFS (table index)*/ /*+ ROWID (table)*/ Join /* ORDERED */ /*+USE_HASH(table)*/ /*+USE_MERGE (table)*/ /*+ USE_NL(table) */

Sceglie lottimizzatore a regole Full table scan Hash scan Index scan per la tabella specificata Fast full scan dellindice invece che full table scan Table scan per rowid

Le tabelle vengono messe in join nellordine esatto in cui sono scritte nella clausola FROM Mette in join la tabella specificata tramite un hash join Mette in join la tabella specificata tramite un merge join Mette in join la tabella specificata tramite un nested loop. La tabella specificata e la seconda nellordine di join, e ha usualmente un indice sulla colonna di join I dati sono semplicemente appesi alla tabella (in un INSERT) oltre high water mark Sulla tabella vengono attivati n processi paralleli

Esecuzione Parallela /*+ APPEND */ /*+ PARALLEL (table, n) */

Tracciamento dello statementEsistono pi modi per effettuare il tracciamento dello statement SQL, a livelli diversi di profondit. In prima battuta si pu effettuare una verifica del piano di accesso seguito da Oracle per restituire i dati richiesti : explain plan. A livello pi approfondito, che riveli anche lutilizzo delle risorse del sistema, si pu effettuare un tracciamento della sessione abilitando lopzione di sql_trace. Il file di output prodotto nella directory definita dalla variabile USER_DUM_DEST deve viene interpretato e reso leggibile tramite il programma tkprof. Infine si pu rendere effettiva per la propria sessione di SQL*Plus lautotrace, che fornisce informazioni sul piano di accesso e sullutilizzo delle risorse di sistema, ma non fornisce informazioni a riguardo delle righe restituite in ciascuno dei singoli step eseguiti da Oracle per restituire il result set richiesto. Explain plan Sintassi: EXPLAIN PLAN SET STATEMENT_ID = mio_statement FOR SELECT ......; dove SELECT ... la query di cui si vuole verificare il piano di accesso. Questa istruzione fa s che vengano inserite nella tabella PLAN_TABLE una o pi righe per ogni step dellesecuzione. Una lettura di tutte le righe relative a mio_statement della plan_table, con una opportuna formattazione delloutput, permette di leggere nellordine esatto i diversi tipi di accesso che Oracle eseguir per ciascuno step, al fine di fornire il result set richiesto. La plan_table pu essere creata con lo script utlxplan.sql che si trova nella directory rdbms/admin. I campi pi significativi sono: STATEMENT_ID ID PARENT_ID identificativo dello statement; nellesempio mio statement identificativo univoco per lo step parent step, ovvero step che viene processato dopo lo step corrente POSITION se 2 step hanno lo stesso parent, lo step con position minore viene seguito prima

OPERATION tipo di operazione, ad esempio table access, sort OPTIONS ulteriori informazioni relative alloperazione, ad esempio full per un table scan, oppure by rowid COST costo relativo delloperazione CARDINALITY numero di righe che loptimizer si aspetta che siano ritornate dallo step Esempio di query e lettura del suo piano di esecuzione dalla plan table EXPLAIN PLAN SET STATEMENT_ID = 'mio_stat' for SELECT /*+ ORDERED INDEX(S SI_HLCODE) (PARALLEL(s,4) USE_MERGE (s r) USE_HASH (sc ms) */ s.rowid,s.sim_id sim_sim_id,s.card_type,s.hlcode,s.status, r.sim_id roaming_sim_id,r.last,r.roaming_id, sc.class_id,ms.sim_id mssim_id FROM sim s,roaming_status r,score_cust sc,msisdn ms WHERE s.sim_id=r.sim_id(+) AND r.last(+)='Y' AND r.roaming_id(+)=3 AND s.sim_id=ms.sim_id(+) AND ms.status(+)='a' AND s.customer_id=sc.customer_id(+) AND (s.hlcode BETWEEN 1 AND 6) / lettura della plan table

select lpad(' ',2*(level-1))||operation||' ' ||options||' ' ||object_name||' ' ||decode(id,0,'(Costo Tot = '||position||')'||' '||optimizer) ||'('||CARDINALITY||')' "Query Plan" from plan_table start with id=0 and statement_id = 'mio_stat' connect by prior id=parent_id and statement_id = 'mio_stat' / Risultato Query Plan -------------------------------------------------------------SELECT STATEMENT (Costo Tot = 341906) CHOOSE(131566) MERGE JOIN OUTER (131566) SORT JOIN (4190859) TABLE ACCESS FULL MSISDN (4190859) SORT JOIN (199169) MERGE JOIN OUTER (199169) SORT JOIN (243090) MERGE JOIN OUTER (243090) SORT JOIN (1875513) TABLE ACCESS FULL SIM (1875513) SORT JOIN (243090) TABLE ACCESS FULL ROAMING_STATUS (243090) SORT JOIN (10182951) TABLE ACCESS FULL SCORE_CUST (10182951) Interpretazione:

r1 r2 r3 r4 r5 r6 r7 r8 r9 r10 r11 r12 r13 r14

(gli indicatori r1, ....r14 servono alla spiegazione) si legge a partire dagli statement pi indentati. Nellesempio, Oracle esegue le operazioni: accesso full alla tabella SIM e ordinamento relativo (r10 e r9) accesso full alla tabella ROAMING_STATUS e ordinamento relativo (r12 e r11) merge join (outer) tra le due (r8) ordinamento del risultato delle precedenti operazioni (r7) ------ RESULT SET 1 accesso full alla tabella SCORE CUST e relativo ordinamento (r13 e r14) merge join (outer) tra RESULT SET 1 e SCORE CUST (r6) ordinamento del risultato delle precedenti operazioni (r5)------ RESULT SET 2 accesso full alla tabella MSISDN e relativo ordinamento (r4 e r3)

merge join (outer) tra RESULT SET 2 e MSISDN (r2)

II Parte

Sql TraceUn ulteriore metodo di indagine per il tuning dello statement consiste nel porre una sessione sotto trace per ottenere informazioni relative non solo al piano di accesso delle queries, ma anche a come le risorse vengono utilizzate per ottenere le informazioni richieste. Questo metodo consiste di due fasi distinte: fase 1 - la sessione viene messa sotto trace tramite il comando ALTER SESSION SET SQL_TRACE TRUE fase 2 - il comado TKPROF interpreta il file di output prodotto Come mettere una sessione sotto trace Prerequisito per mettere sotto trace una sessione che lopzione TIMED_STATISTICS sia posta uguale a TRUE. Ci sono tre modi per mettere sotto trace una sessione: Dallinterno della sessione si esegue il comando ALTER SESSION SET SQL_TRACE TRUE; allinterno di un blocco PL/SQL si esegue il comando DBMS_SESSION.SET SQL_TRACE(true); da unaltra sessione, si esegue il comando DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true); Il file di trace prodotto si trova nella directory definita dal parametro di configurazione USER_DUMP_DEST. Per leggere il nome della directory si esegue il comando Select value from v$parameter where name=user_dump_dest; Dopo aver rimesso SQL_TRACE a FALSE, si deve intrepretare il file di output prodotto da Oracle, tramite lutility tkprof. La sintassi per lanciare tkprof : tkprof trace_file output_file explain=user/pwd sort=(sort options) trace_file il file prodotto in user_dump_dest; output_file il file prodotto in uscita da tkprof, in formato leggibile; explain=user/pwd specifica la connessione che verr utilizzata per generare i piani di accesso; se non viene specificato, non possibile visualizzare i piani di accesso sort=(sort_options) permette di mostrare gli statement in ordine discendente rispetto alla chiave di sort prescelta. Unaltra importante opzione sys=no, che omette dal tracciamento gli statement di SYS, tra cui gli statement ricorsivi, ovvero generati da Oracle stesso per rispondere alle richieste dellutente. Gli statement ricorsivi sono infatti poco interessanti se si vogliono considerare le performance di una sessione. Esempio di una parte di file di output e relativa interpretazione > > > > > > > TKPROF: Release 8.1.6.0.0 - Production on Fri Jun 2 16:53:00 2000 (c) Copyright 1999 Oracle Corporation. Trace file: ora_26102.trc Sort options: default All rights reserved.

> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

************************************************************************** ****** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ************************************************************************** ****** select P_ARBBED_RECORD1 ,P_ARBBED_RECORD2 from P_ARBBED where ((((P_ARBBED_CICLO=:b0 and P_ARBBED_IDRANGE=:b1) and P_ARBBED_IDACC=:b2) and P_ARBBED_IDARBORNO=:b3) and P_ARBBED_IDARBORRESETS= :b4) order by P_ARBBED_PROGRREC call count rows ------- --------------Parse 0 0 Execute 163 0 Fetch 1275 206583 ------- --------------total 1438 206583 cpu elapsed disk query current

-------- ---------- ---------- ---------- ---------0.00 0.10 10.54 0.00 0.00 61.11 0 0 19286 0 0 19540 0 0 2

-------- ---------- ---------- ---------- ---------10.64 61.11 19286 19540 2

Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 19 (PBS) Rows ------0 0 0 0 0 Execution Plan --------------------------------------------------SELECT STATEMENT GOAL: CHOOSE SORT (ORDER BY) PARTITION RANGE (SINGLE) PARTITION:KEYKEY TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'P_ARBBED' PARTITION:KEYKEY INDEX (RANGE SCAN) OF 'IDX_ARBBED_TEST2' (NON-UNIQUE) PARTITION:KEYKEY

************************************************************************** ****** select p_cfctr_idctr into :b0 from p_cfctr where p_cfctr_idcf=:b1 call rows count cpu elapsed disk query current

> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

------- --------------Parse 0 0 Execute 164 0 Fetch 164 164 ------- --------------total 328 164

-------- ---------- ---------- ---------- ---------0.00 0.02 0.02 0.00 0.02 0.01 0 0 0 0 0 494 0 0 0

-------- ---------- ---------- ---------- ---------0.04 0.03 0 494 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 19 (PBS) error during parse of EXPLAIN PLAN statement ORA-00942: table or view does not exist parse error offset: 105 ************************************************************************** ****** select P_MTP_IDTMTP ,P_MTP_IDTCCR ,P_MTP_CARDNUM ,DECODE(P_MTP_EXPDT,null , null ,TO_CHAR(P_MTP_EXPDT,:b0)) ,P_MTP_BANK ,P_MTP_AGENCY ,P_MTP_ABI , P_MTP_CAB ,P_MTP_CIN ,P_MTP_CONTOCORR into :b1:b2,:b3:b4,:b5:b6,:b7:b8, :b9:b10,:b11:b12,:b13:b14,:b15:b16,:b17:b18,:b19:b20 from P_MTP where P_MTP_IDBILL=:b21 call count rows ------- --------------Parse 0 0 Execute 164 0 Fetch 164 164 ------- --------------total 328 164 cpu elapsed disk query current

-------- ---------- ---------- ---------- ---------0.00 0.00 0.01 0.00 0.01 0.01 0 0 2 0 0 658 0 0 0

-------- ---------- ---------- ---------- ---------0.01 0.02 2 658 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 19 (PBS) Rows ------0 0 0 Execution Plan --------------------------------------------------SELECT STATEMENT GOAL: CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'P_MTP' INDEX (RANGE SCAN) OF 'INDICE_P_MTP1' (NON-UNIQUE)

************************************************************************** ******

> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

select p_changemtp_idtmtp ,p_changemtp_flagcntr into :b0,:b1:b2 from p_changemtp where p_changemtp_idctr=:b3 call count rows ------- --------------Parse 0 0 Execute 164 0 Fetch 164 0 ------- --------------total 328 0 cpu elapsed disk query current

-------- ---------- ---------- ---------- ---------0.00 0.01 0.01 0.00 0.01 0.01 0 0 0 0 0 498 0 0 0

-------- ---------- ---------- ---------- ---------0.02 0.02 0 498 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 19 (PBS) error during parse of EXPLAIN PLAN statement ORA-00942: table or view does not exist parse error offset: 139 ************************************************************************** ****** select P_CTR_IDSERV ,P_CTR_IDDESTIN ,P_CTR_FLDETT ,P_CTR_FLAGFIRSTBILL , P_CTR_FLAGCHANGEMTP ,P_CTR_IDTPROT ,P_CTR_CONTLASTNAME ,P_CTR_CONTFIRSTNAME ,P_CTR_IDTRM ,P_CTR_COMMESSA ,P_CTR_PROGETTO ,P_CTR_STATUS , TO_CHAR(C_CTR_1FATT,:b0) ,P_CTR_DET_SUPP ,P_CTR_DET_EMAIL ,P_CTR_DET_PLAT , P_CTR_DET_FILETYP ,P_CTR_TIPOCTR ,TO_CHAR(P_CTR_DATEREGIS,:b0) , TO_CHAR(P_CTR_DATECREAT,:b0) ,P_CTR_IDARBORNOLOTT ,P_CTR_IDARBORRESETSLOTT , P_CTR_IDTESE ,P_CTR_ESENZNUM ,TO_CHAR(P_CTR_ESENZDATE,:b0) ,P_CTR_IDASS , P_CTR_FLAG_CONTRACT_675_SIGN ,P_CTR_FLAG_RID_SIGN ,P_CTR_FLAG_CREDIT_CARD , P_CTR_IDLIST ,P_CTR_FLAGMIGRATO ,TO_CHAR(P_CTR_SERVLISTVAL,:b0) , P_CTR_IDHOLD ,P_CTR_IDTCV into :b5:b6,:b7:b8,:b9:b10,:b11:b12,:b13:b14, :b15:b16,:b17:b18,:b19:b20,:b21:b22,:b23:b24,:b25:b26,:b27:b28,:b29:b30, :b31:b32,:b33:b34,:b35:b36,:b37:b38,:b39:b40,:b41:b42,:b43:b44,:b45:b46, :b47:b48,:b49:b50,:b51:b52,:b53:b54,:b55:b56,:b57:b58,:b59:b60,:b61:b62, :b63:b64,:b65:b66,:b67:b68,:b69:b70,:b71:b72 from P_CTR where P_CTR_IDCTR=:b73 call count rows ------- --------------Parse 0 cpu elapsed disk query current

-------- ---------- ---------- ---------- ---------0.00 0.00 0 0 0

> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

0 Execute 164 0 Fetch 164 164 ------- --------------total 328 164

0.02 0.08

0.04 0.47

0 68

0 698

0 0

-------- ---------- ---------- ---------- ---------0.10 0.51 68 698 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 19 (PBS) Rows Execution Plan ------- --------------------------------------------------0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS (BY INDEX ROWID) OF 'P_CTR' 0 INDEX (UNIQUE SCAN) OF 'PK_P_CTR' (UNIQUE) ************************************************************************** ****** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count rows ------- --------------Parse 165 0 Execute 17906 397029 Fetch 9281 211883 ------- --------------total 27352 608912 cpu elapsed disk query current

-------- ---------- ---------- ---------- ---------0.13 44.21 166.39 0.14 113.21 599.74 0 628 954613 0 54800 990187 0 453209 15256

-------- ---------- ---------- ---------- ---------210.73 713.09 955241 1044987 468465

Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count rows ------- --------------Parse 74 0 Execute 74 47 Fetch 118 93 ------- --------------total 266 140 cpu elapsed disk query current

-------- ---------- ---------- ---------- ---------0.02 0.05 0.01 0.03 0.03 0.03 0 1 0 0 130 281 0 91 10

-------- ---------- ---------- ---------- ---------0.08 0.09 1 411 101

> > > > > > > > > > > > > > > > > > > > > > >

Misses in library cache during parse: 0 226 user SQL statements in session. 74 internal SQL statements in session. 300 SQL statements in session. 53 statements EXPLAINed in this session. ************************************************************************** ****** Trace file: ora_26102.trc Trace file compatibility: 8.00.04 Sort options: default 1 226 74 300 75 53 session in tracefile. user SQL statements in trace file. internal SQL statements in trace file. SQL statements in trace file. unique SQL statements in trace file. SQL statements EXPLAINed using schema: SYS.prof$plan_table Default table was used. Table was created. Table was dropped. lines in trace file.

29495

Per ogni statement SQL viene riportato: 1. lo statement stesso 2. le statistiche relative a ciascuna categoria delle chiamate oracle; le chiamate sono tre: parse, execute, fetch. Per ciascuna di queste, vengono fornite le seguenti indicazioni: count = numero di volte che ciascun tipo di chiamata viene eseguito cpu = tempo di CPU richiesto elapsed = tempo trascorso disk = numero di letture su disco richieste query = numero di letture in memoria per letture consistenti current = numero di letture in memoria per modificare i blocchi esistenti. La somma di query e current costituisce le letture logiche rows = numero di righe processate Per fare una valutazione accurata degli statement utile eseguire alcuni confronti tra i valori restituiti da tkprof. Letture logiche (totali) rispetto alle righe processate (totali) (query+current)/rows Questo rapporto fornisce una indicazione grossolana del costo della query. Una query molto costosa se si devono leggere parecchi blocchi per avere solo poche righe restituite. Parse count rispetto a execute count. Si deve ridurre il pi possibile il numero di parse, utilizzando per quanto possibile le bind variable e statement sempre uguali se si esegue piu volte la stessa query.

Numero di righe fetched rispetto al numero di fetch. Questo rapporto da una misura dellutilizzo dellarray fetch. Letture su disco (totali) rispetto alle letture logiche(totali). Uno degli obbiettivi primi e infatti quello di ridurre le letture su disco a favore delle letture fatte in memoria. Ancora pi precisamente si pu calcolare la percentuale di hit ratio (1 - disk /( query + current) )*100 . Tanto pi questa percentuale si avvicina a 100, tanto meglio si sfruttano le risorse, leggendo il pi possibile nella cache e il meno possibile su disco.

Tkprof uno strumento molto utile per effettuare il tuning, ma richiede un certo dispendio di tempo e la possibilit di accedere al server oracle. Unaltra utilit che fornisce il piano di esecuzione delle queries e anche le statistiche di esecuzione, e che risulta di piu semplice utilizzo, AUTOTRACE.

AutotraceDallinterno di una sessione di SQL*Plus si pu lanciare il comando con le seguenti opzioni: SET AUTOTRACE ON OFF ON EXPLAIN ON STATISTICS TRACEONLY

ON mostra il piano di esecuzione e le statistiche, dopo aver eseguito la query OFF esegue solo la query ON EXPLAIN dopo lesecuzione della query, mostra il piano di accesso ON STATISTICS dopo lesecuzione della query, mostra le statistiche di utilizzo delle risorse del sistema TRACEONLY non mostra il risultato della query, e mostra il piano di accesso e le statistiche Rispetto a tkprof, lautotrace e uno strumento che permette di monitorare pi velocemente lo statement, ma non in grado di fornire informazioni a riguardo del numero di righe ritornate da ciascuno step. In particolare pu essere molto utile usare la modalit TRACEONLY, dal momento che evita di eseguire la query.

AppendiceIn appendice vengono fornite queries varie che interrogano le viste del dizionario per ottenere informazioni relativamente alle sessioni e alle interrogazioni che vi vengono effettuate. E solo un breve campionario delle innumerevoli informazioni che si possono trarre dalle viste di catalogo sul funzionamento del sistema.

Quali viste da interrogareLe viste statiche da cui ottenere informazioni riguardo alle tabelle, le colonne, gli indici, gli oggetti in genere del db, lo spazio libero, i segmenti, gli extents sono:DBA_(ALL_/USER_)TABLES DBA_(ALL_/USER_)TAB_COLUMNS DBA_(ALL_/USER_)INDEXES DBA_(ALL_/USER_)IND_COLUMNS DBA_(ALL_/USER_)CONSTRAINTS DBA_(ALL_/USER_)OBJECTS DBA_(ALL_/USER_)FREE_SPACE DBA_(ALL_/USER_)SEGMENTS DBA_(ALL_/USER_)EXTENTS Alcune delle viste dinamiche da cui ottenere informazioni riguardo alle sessioni, gli statement attivi, lutilizzo delle risorse , i lock sono:

V$SESSION V$SESSTAT

V$PROCESS V$SQLAREA V$OPEN_CURSOR V$LOCK V$ROLLSTAT

COME OTTENERE L'IDENTIFICATIVO DI SESSIONE DI UN UTENTE select sid a from v$process p, v$session s where p.addr = s.paddr and s.username = upper('&username');

LOGICAL READS, LETTURE SU DISCO, HIT RATIO

column phys column gets column con_gets column hitratio

format 999,999,999 format 999,999,999 format 999,999,999 format 9.999

heading 'Physical Reads' heading ' DB Block Gets' heading 'Consistent Gets' heading ' Hit Ratio '

select

sum(decode(name,'physical reads',value,0))phys, sum(decode(name,'db block gets',value,0)) gets, sum(decode(name,'consistent gets', value,0)) con_gets, (1 - (sum(decode(name,'physical reads',value,0)) / (sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))))) hitratio

from

v$sysstat;

MONITORAGGIO DEGLI STATEMENTS DEGLI UTENTI

select

s.sid, s.username, s.status, disk_reads, buffer_gets, executions, loads, sql_text

from where and

v$sqlarea q, v$session s q.address = s.sql_address s.username = UPPER('&1')

order by sid;

SESSIONI BLOCCANTI La query mostra un record che contiene la sessione che tiene i lock, loggetto bloccato, e le sessioni che fanno richiesta delloggetto in lock. Lock holder = ORACLE username concatenated with the session identifier of the session maintaining the lock on the object. Object = The name of the locked object. Type = Resource type. The following lock types are obtained by user applications. Any process which is blocking others is likely to be holding one of the locks listed below: RW - Row wait enqueue lock TM - DML enqueue lock TX - Transaction enqueue lock UL - User supplied lock Lock mode = Lock mode held [one of the following values] select distinct sh.username || '(' || sh.sid || ')' "Lock holder", ao.object_name "Object", l.type, decode(lmode, 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') "Lock mode", sr.username || '(' || sr.sid || ')' "Object requester" from v$session sh, all_objects ao, v$lock l, v$session sr where l.id1 = ao.object_id and sh.sid = l.sid and sh.lockwait is null and sr.lockwait is not null and l.type = 'TM' order by 1, 2, 3;

Le transazioni acquisiscono dei lock negli header dei segmenti di rollback. interrogando v$lock, v$rollname, v$rollstat, v$session si ottiene lelenco di tutte le sessioni che hanno transazioni attive : select R.NAME rr, nvl(S.USERNAME, 'no transaction') us, S.osuser os, S.terminal te, RS.EXTENTS, RS.RSSIZE, RS.WRITES, RS.XACTS, RS.GETS, RS.WAITS, RS.OPTSIZE, RS.HWMSIZE, RS.SHRINKS, RS.WRAPS, RS.EXTENDS V$LOCK V$SESSION L, S,

from

where

V$ROLLNAME R, V$ROLLSTAT RS L.SID= S.SID(+) and trunc(L.ID1/65536)= R.USN and R.USN = RS.USN and L.TYPE = 'TX' and L.LMODE = 6 order by R.NAME;

INFORMAZIONI DA V$SESSION ----------------------------------------------select from sid, username, program, osuser, process, machine, terminal, type v$session;

-----------------------------------------------

Come mettere in join alcune delle viste piu utili per il monitoraggio:

JOIN : V$SESSION E V$OPEN_CURSOR -----------------------------------------------

select from where

a.sid, a.username, b.sql_text v$session a, v$open_cursor b a.saddr = b.saddr;

----------------------------------------------JOIN : V$SESSION E V$STATNAME, V$SESSTAT ----------------------------------------------select from where and and order by a.sid, a.username, b.name, c.value v$session a, v$statname b, v$sesstat c a.sid = c.sid b.statistic# = c.statistic# a.username = upper('&username') a.sid, a.username, b.name;

-----------------------------------------------