V$OPEN_CURSOR

martedì 10 maggio 2011 alle 10:41 | Pubblicato su Performance Tuning, Varie | 6 commenti
Tag:

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.

6 commenti »

RSS feed for comments on this post. TrackBack URI

  1. 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.”

  2. 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

  3. 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”.

  4. 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

  5. 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)

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


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: