6f429244e498457b8400e72e4af683b0 Alberto Blog: SESSION_CACHED_CURSORS

martedì 31 maggio 2011

SESSION_CACHED_CURSORS

Dopo aver raccolto i miei appunti sparsi in un pò di documenti vorrei riuscire a scrivere
un articolo sulla parametro di SESSION_CACHED_CURSORS.

Quando una applicazione chiude un cursore, Oracle imposta lo stato di questo cursore come "closeable". Tuttavia questo non sarà effettivamente chiuso finchè Oracle non avrà
bisogno di spazio per aprirne un altro.
Quindi sebbene un applicazione chiuda tutti i propri cursori bene, (fatto alquanto raro, dipende dallo sviluppatore) quest'ultimi rimarranno aperti consumando risorse.


Come punto di partenza per potere eseguire un'analisi eseguiamo uno Statepack del sistema, ci focalizziamo sul Execute to Parse %.
Se il numero di parse calls si avvicina al numero di execute calls, allora questa percentuale si avvicinerà allo 0.Se il numero di execute calls aumenterà, la percentuale tenderà ad avvicinarsi al 100, il che significa che uno statement SQL il DB lo carica una volta sola (parse) e l'esegue molte volte (executes), ossia la situazione ottimale.

Dobbiamo prendere questa percentuale come un indicatore, nel mio caso per esempio ho un Execute to Parse di 54,87 calcolato con la seguente formula:
100*(executions - parses) / executions ossia  100 * (371,14  - 167,48) / 371,14.

Vediamo come sono impostati alcuni parametri nel DB:

open_cursors=900
cursor_sharing=Exact
session_cached_cursors=20

Adesso lanciamo


select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
order by a.value desc;



VALUE                  USERNAME                       SID                    SERIAL#              
---------------------- ------------------------------ ---------------------- ----------------------
92                     XXXXXX                                   189                    47764                
90                     AAAAAA                                   243                    4188                  
82                     BBBBBBBBB                            196                    35102                
82                     CCCCCCCCC                            142                    41252                
81                     DDDDDDDDD                          257                    29099                
80                     EEEEEEEEE                               227                    2522                  
......

possiamo notare che il SID 189 ha 92 open_cursor, quindi ben sotto al 900 impostato.

Adesso verifichiamo se il parametro SESSION_CACHED_CURSORS potrà aiutarci.

select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;

SID=189



CACHE_HITS             ALL_PARSES             SESS_CUR_CACHE_NOT_USED
----------------------         ----------------------             -----------------------
5460                                  5680                                 220  


Il valore "session cursor cache hits" è il numero di volte che uno statemet SQL viene trovato in session cursor cache, quindi non ha bisogno di essere reparsed, adesso se raffrontiamo i valori "parse count (total)" meno "session cursor cache hits" otteniamo "parse count (total)" vedremo il numero di parse di cui abbiamo bisogno.

Procediamo con

select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count' ;
           
SID=189

CURR_CACHE MAX_CACHED USERNAME    SID       SERIAL#
--------------         --------------          --------------      ------            --------------
20                      20               XXX               189            47764


Se session cursor cache count ha raggiunto il valore massimo e session_cursor_cache_hits è più basso comparato con all parses, potrebbe nascere il sospetto che l'applicazione in questo caso riesegue gli stessi SQL in modo ripetitivo, potremo quindi scegliere di aumentare SESSION_CURSOR_CACHE per aiutarci con latch contention.
Tuttavia se l'applicazione non riesegue gli stessi SQL, quindi session_cursor_cache_hits  sarà più basso dei parses e il session cursor cache raggiunge il valore massimo, anche aumentando il tale valore non si otterranno i risultati sperati, questo significa che l'applicazione utilizzerà SQL non condivisi.

Ovviamente questo è un esempio della sessione con il sid 189.

In questo caso si potrebbe inizialmente impostare il valore session_cached_cursors a 100 per poter arrivare ad un buon compromesso.
Questo articolo ha lo scopo di analizzare e di gestire al meglio il parametro SESSION_CURSOR_CACHE tutti i commenti sono benvenuti.

2 commenti:

Roberto ha detto...

Alberto, ho visto che ti sei lanciato nell'avventura di un blog. Ti faccio innanzitutto i complimenti e anche gli auguri, cercherò di seguirti e di (spero) aggiungere qualche mio contributo agli argomenti in cui ti cimenterai.

Roberto

P.S. non ti sei tolto l'abitudine di scrivere pò invece di po', eh?

Alberto ha detto...

grazie 1000 Roberto, come vedi mi sono lanciato in questa avventura e come hai inoltre già scoperto le vecchie brutte, abitudini non si perdono mai....

Posta un commento