Re: V$SESSSTAT stat 3 vs V$OPEN_CURSOR

Hi!

This is troubleshooting, not performance tuning ;) As far as Ixora states, v$sesstat "opened cursors current" shows PL/SQL cached cursors as well, even when they are explicitly closed (assuming that session_cached_cursors is set).
But v$open_cursor doesn't show the number of cursor structures in UGA, but it shows you (some kind of count) of parse locks which get created in SGA for a SQL statement or PL/SQL block.
They provide a link from schema objects to shared SQL area, so the cursors can be invalidated when doing DDL on referenced objects. They are called breakable parse locks in Concepts Manual. As the name says "breakable" - even though you got a lock on schema object, you still can alter it, the lock is just "broken", the cursor is invalidated (but remains cached in UGA) and has to be reloaded on next execution. Here we can have a situation, when as result of DDL, we don't have any breakable parse locks on SGA anymore, but we still have open cursors in UGA. Thus the difference between v$sesstat and v$open_cursor.

(this story is based on Concepts manual and Ixora)

But for ORA-1000 errors, you should maybe set event for ORA-1000 and dump errorstack. Or sample v$sesstat quite often (once per few seconds), to catch the point where number of open cursors jumps to ceiling (as I understand, it's fairly low normally).

And for TAR's - I recommend you to do few Oracle Applications upgrades, especially to fresh versions of major new releases, such 11.5.1 or 2, then you start even seeing severity 1 TARs in your dreams ;)

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 07, 2003 11:14 PM

> No takers on this from the perf tuning gurus? Please don't make me open a
> TAR. Think of the children...
>
> Rich
>
> Rich Jesse System/Database Administrator
> rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
>
>
> > -----Original Message-----
> > From: Jesse, Rich
> > Sent: Thursday, July 24, 2003 3:39 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
> >
> >
> > Hey all,
> >
> > We recently had a problem with a 3rd-party app getting
> > "ORA-1000 max open
> > cursors exceeded" on their 8.1.7.4 DB. Since OPEN_CURSORS is
> > set to 500 in
> > the init.ora -- should be more than generous for a tiny app
> > on a tiny DB --
> > we started looking into how many cursors the app actually has
> > open at any
> > given time.
> >
> > While investigating this, I see that the number of rows in
> > V$OPEN_CURSORS
> > isn't consistent with the value of stat 3 ("opened cursors
> > current") of
> > V$SESSSTAT. Of the two processes I looked at, each had a
> > V$SESSSTAT value
> > of "3" for stat 3, while the first had a single entry in
> > V$OPEN_CURSOR and
> > the second had four.
> >
> > Should these values match? I looked on Metalink but was
> > unable to find any
> > reference to the relationship between these, other than a
> > forum article
> > where the OraSupport person was extraordinarily unhelpful.
> >
> > TIA,
> > Rich
> >
> > Rich Jesse System/Database Administrator
> > rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).