Mettere sotto TRACE una sessione / sql - TKPROF
Rientra nelle tecnince di SQL Tuning,
E' vantaggioso perchè individua tutte le sql statements eseguite tra lo start e la fine del trace.
E' un tool free ma occorre avere le grant di dba.
Il file TKPROF contiene queste info
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -----------------------------------------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6 0.38 0.38 0 8133 0 66
------- ------ -------- ---------- ---------- ---------- ---------- -----------------------------------------
total 8 0.39 0.39 0 8133 0 66
count = number of times OCI procedure was executed
- parse time --> se molto alto suggerisce di controllare il sistema piuttosto che la query
- execute time --> indica piuttosto il tempo di esecuzione di un update, etc che di una select di una query
- fetch time --> mostra quanti dati vengono lettti dal database. Più è alto e più le prestaioni peggiorano.
cpu = CPU Time cioè per quanto tempo è stata utilizzata la CPU durante l'esecuzione della quey, in secondi.
elapsed = Elapsed TIME è il tempo atteso dall'utente finché il database non ha gestito il risultato per l'utente.
Se la CPU Time < Elapsed Time ===> vuol dire che ci sono molti tempi di attesa nell'esecuzion della query.
disk = number of physical block reads from disk to buffer cache ==> più è alto e più le prestazioni peggiorano. Se disk = 0 vuol dire che legge i dati solo dalla buffer cache.
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. E' il numero di righe restituite.
Physical and Logica Read fisico vuol dire che leggi i dati dal disco mentre logical che leggi dal buffer cache.
WAIT Events non comprende la CPU Time
Misses in library cache during parse: 0 ==> vuol dire che l'execution plan già esiste nella Library Cache e pertanto si parla di Soft parse che è più veloce di un Hard parse. Se 1 vuole vuol dire che è un hard parse.
Parsing user id: 108 ==> è dell'id dell'utente ch esegue la query select user_id from dba_users where username='HR';
L'nformazione più importnate è "Misses in library cache during parse". Se ad esempio la nostra query usa Bind Variable, potrebbero vedere differenze tra la library cache misses e il count parse.
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- -----------------------------------------------------------------------------------------
107 107 107 TABLE ACCESS FULL EMPLOYEES (cr=14 pr=0 pw=0 time=211 us starts=1 cost=3 size=7383 card=107)
Row Source Operation == > vuole dire che èuna execution plan e non una explain plan.
Se i valori di Rows 1st, avg e max sono diversi vuol dire che le statistiche non sono aggiornate.
cr ==> conistent read, cioè letture logiche dal buffer cache.
pr ==> physical reads
pw ==> physical writes
time ==> è il tempo di esecuzione in microsecondi
starts ==> indica il numero di volte il comando (Table access full......) viene eseguito. Nel caso di una nested loop join, viene letta la seconda tabella per ogni riga della driving table quindi il valore di starts è maggiore di 1.
cost ==> costo dello step
size ==> numero di byte letti per questo step
card ==> cardinalità per questo step
************ 1 METODO **************
Fatto su un database 19c con pluggable SVIL01
1) Connettersi con l'utenza che deve eseguire la query
sqlplus frank/passwd@SVIL01
2) Aprire una nuova sessione e connettersi con sys al SVIL01 e intercettare la sessione avviata con la connessione precedente:
SQL > select sid,serial# , username, to_char(logon_time,'dd-mm-yyyy hh24:mi:ss'), status
from v$session where username ='FRANK'
order by logon_time desc;
Ad esempio sid=304 e serial#=30209
3) Lanciare la query da analizzare dalla sessione collegato come frank.
4) Eseguire dalla seconda sessione come sys
SQL> exec DBMS_System.set_ev(sid, serial#, event, level, name);SQL> exec DBMS_SYSTEM.set_ev(304, 30209, 10046, 12, '');
SQL> SELECT s.sid, s.serial#, p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.username = 'FRANK';
5) Collegarsi al server db per risalire al file di trace che è individuato dallo "spid" ricavato dalla query precedente.
SQL> show parameter user_dump_dest
SQL> select value from v$DIAG_INFO where name = 'Diag Trace';
6) Al termine della query dalla sessione con utente sys chiudere il trace
SQL> exec DBMS_SYSTEM.Set_Ev(304, 30209, 10046, 0, '');
7) Convertire il trace file in un formato più leggibile per l'uomo.
Creare il report con il seguente comando:
tkprof <trace_file_nm>.trc <output_rpt_nm>.txt
Per fare questo deve essere installato il tool TKPROF.
Creare il file che permette di identificare il trace
Per risalire al nome del file
Creare il report con il seguente comando:
tkprof <trace_file_nm>.trc <output_rpt_nm>.txt
- alter session set timed_statistics=true;
- exec dbms_session.session_trace_enable(waits => true, binds => false);
- select s.prod_id,p.prod_name,s.cust_id,c.cust_first_name
- from sh.sales s, sh.products p, sh.customers c
- where s.prod_id = p.prod_id
- and s.cust_id = c.cust_id
- and s.amount_sold > 1500;
- exec dbms_session.session_trace_disable;
