V$OPEN_CURSOR

Alcuni giorni abbiamo avuto un problema con l’errore oracle “ORA-01000: maximum open cursors exceeded” e quindi mi è stato chiesto di fare una prova e verificare lo statement legato al cursore che si ipotizzava una procedura non chiudesse. Raramente ho avuto problemi con questo errore, quando mi è capitato si è riscontrato che effettivamente mancaca una chiamata al metodo java di chiusura dello statement, che corrisponde alla chiusura del cursore Oracle.

Mi è venuta in mente all’uopo la vista di sistema V$OPEN_CURSOR che secondo la documentazione della versione 10gR2 mostra i cursori che ogni sessione ha “correntemente” (currently) analizzato e aperto (opened and parsed). Per evitare che alcune mie traduzioni possano fuorviare dove ho dubbi riporto anche la dicitura originale del manuale. In realtà credo che tale definizione sia fuorviante, perché tale vista mostra i cursori attualmente in cache, senza dare informazione alcuna sul fatto che siano aperti o meno.

Faccio un piccolo passo indietro, cos’è un cursore? Anni fa mi sono scritto una sorta di guida oracle personalizzata e in questa guida mi ricordavo di aver annotato anche queste informazioni, prelevate dal manuale “Concepts” secondo il quale sono collegamenti (handle) a aree di memoria private della sessione, le “private SQL Area” che a seconda della configurazione si trovano nella Shared Pool (server condivisi o shared server) o nella PGA (server dedicati o dedicated server). Il numero massimo di cursori che una sessione può aprire è limitato dall’impostazione del parametro OPEN_CURSORS che ha come valore di default 50 secondo la documentazione ma che io sui miei database (10.2.0.3 e 10.2.0.4) trovo impostati a 300 in quanto pare sia uno dei valore così impostati dal Database Configuration Assistant che solitamente uso per creare i database.

Interrogando la vista V$OPEN_CURSOR per un determinato SID si ottengono normalmente diversi record e ciò mi ha lasciato decisamente spiazzato. Uno dei motivi pare essere l’impostazione del parametro SESSION_CACHED_CURSORS che secondo la documentazione come default il valore ’0′ ma che sui miei database ha come default il valore 20. Ciò significa che sulla V$OPEN_CURSOR non vengono visualizzati solo i cursori aperti per ogni sessione, ma anche quelli in cache. L’unica informazione certa che si può avere sui cursori aperti per una sessione, anche secondo la nota del supporto oracle 743605.1 (occorre un accesso al supporto oracle per visualizzare il link) che conferma quanto ho scritto sopra, è il numero di cursori aperti (salvo bachi), ottenibile con la query:


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'
and a.sid=<SID>;

Anche in una discussione su Asktom mi sembra si affermasse ciò riportanto la stessa query.

Mi rimangono alcune perplessità, interrogando la V$OPEN_CURSOR per una singola sessione posso trovare un numero di record superiore al valore del parametro SESSION_CACHED_CURSOR, alcuni legati allo stesso SID ma a diverso SADDR, colonna i cui significato non è ben chiaro, anche se sospetto sia legata a chiamate ricorsive.

Like this:

Be the first to like this post.

6 commenti »

RSS feed dei commenti a questo articolo. TrackBack URI

Ciao Cristian,
sperando che possa essere di aiuto, è un po datata ma ti riporto cosa rispondeva Jonathan Lewis ad una mia domanda riguardo la vista in questione:

“Technically, v$open_cursor displays the existence
of a link between a session (v$session) and a child
cursor (v$sql). This is actually x$kgllk – the library
cache locks.

The presence of an x$kgllk keeps part of the child
cursor information in memory, but doesn’t force all
of it to stay in memory – in particular, the execution
plan can be flushed from memory if memory is short.

There is another structure (x$kglpn) which pins
the child cursor and all its subheaps whilst the
cursor is actually executing.

Try reviewing v$open_cursor in this light, and you
come to the conclusion that there is no good name
for the view – it is the set of cursors which have been
parsed, may or may not be needed again but have been
locked in memory, and may or may not have their full
complement of sub-heaps so that they can be re-executed
again without being reparsed.

It would be quite nice if Oracle could let us see in v$open_cursor
the cursors which are open because they have been explicitly
held by the front end – but I think there may be a mismatch
between some calls the client code makes and the hidden OCI
library makes that confuse the issue. For example, the client
manages to report a parse call when it re-executes a cursor
that has been held by the session_cursor_cache – I don’t think
(but I’m not 100% sure) that there is any difference in the activity
at the server between the use of a deliberately held cursor and
a cursor held in the cache – but the front end code must make
a call that increments the parse count which is outside the control
of the OCI code that ‘knows’ it’s not going to make a parse call.”

Comment by Alessandro— mercoledì 11 maggio 2011 #

Replica
Ciao Cristian,
Ottime info, stavo scrivendo un post anch’io proprio su questo argomento, ma poi mi sono fermato perchè ho alcune perplessità in merito.
I record che potresti trovare in “più” dovrebbero essere cursori non rilasciati da Oracle benchè considerati chiusi gestiti proprio con il SESSION_CACHED_CURSORS. Infatti Oracle non rilascia il cursore anche quando è chiuso.
Ci sarebbe un sistema per poter eseguire una sorta di Tuning di questo parametro per sessione ovviamente in modo tale da dare al SESSION_CACHED_CURSORS un valore vicino alla realtà.

Alberto

Comment by Alberto— mercoledì 11 maggio 2011 #

Replica
Secondo le mie conoscenze, in V$OPEN_CURSOR vi sono, se session_cached_cursor > 0, tanto i cursori aperti che quelli chiusi di recente e memorizzati nella Session Cursor Cache. L’opinione che mi ero fatto a suo tempo (e che è almeno in parte confermata dalla risposta di J. Lewis ad Alessandro) è che dal punto di vista delle performances, non vi siano differenze tra utilizzare un cursore aperto o farlo risorgere dalla Session Cursor Cache, e non c’è alcuna diffenza nemmeno nella gestione dell’ageing dei cursori (Shared SQL Areas) in Library Cache: i parent cursors open o cached-close sono sempre protetti, mentre i child cursors sono passibili di estromissione LRU in entrambi i casi.
Io direi che i cursori chiusi cached sono “praticamente ancora aperti”.

Comment by Roberto— mercoledì 11 maggio 2011 #

Replica
sono d’accordo con te quando dici che i cursori chiusi sotto la session_cached_cursors sono “praticamente ancora aperti”, o meglio la ri-apertura di essi dovrebbe essere molto piu rapida rispetto al normale, insomma il soft “soft parse”

altro contributo dal solito Tom Kyte:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1041031921901

Comment by Alessandro— mercoledì 11 maggio 2011 #

Replica
Riguardo la questione SADDR differenti per uno stesso SID in V$OPEN_CURSOR, ho provato a fare qualche test e non me lo spiego.
Innanzitutto credo che valga sempre V$SESSION.SADDR = V$OPEN_CURSOR.SADDR, e almeno apparentemente sembra che SADDR sia direttamente legato al numero del SID, quindi è sempre lo stesso per uno stesso SID.
(Questo se si rimane all’interno dello stesso server anche dopo riavvi dell’istanza e/o del server. Ho notato invece che se si cambia server per uguali SID cambia la prima parte di SADDR e la coda resta uguale, probabilmente essendo un indirizzo fisico è legato ad altri fattori)

Comment by Roberto— mercoledì 11 maggio 2011 #

Replica
[...] che la mia risposta ai commenti del post precedente diventava pericolosamente lunga ho deciso di dedicare un post [...]

Pingback da Ancora sulla V$OPEN_CURSOR « Oracle and other— mercoledì 11 maggio 2011 #

Replica