Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR

RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR

From: Richard Ji <Richard.Ji_at_ztango.com>
Date: Thu, 07 Aug 2003 14:04:37 -0800
Message-ID: <F001.005C9860.20030807140437@fatcity.com>


I see you have found the answer yourself after I send out my e-mail. :) Note, there were inaccurate statistics about "opened cursors current" in some releases (sorry I forgot which 8i release it was) when PQ is in use.

Richard

-----Original Message-----
Sent: Thursday, August 07, 2003 5:40 PM
To: Multiple recipients of list ORACLE-L

I should have known to check Steve's site first (is he still lurking around here?).

One thing I noticed is that one of the cursors listed in V$OPEN_CURSOR is an INSERT into AUD$. This would account for the discrepancy and could match the description you provided.

So, in summary, the true number of open cursors is correct in V$SESSTAT and is *not necessarily* the count of rows in the corresponding V$OPEN_CURSOR view.

And my apologies to the sticklers of the list who have noticed my "V$SESSSTAT" and "V$OPEN_CURSORS"... <sigh>

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA


> -----Original Message-----
> From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee]
> Sent: Thursday, August 07, 2003 4:20 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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: Richard Ji
  INET: Richard.Ji_at_ztango.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).
Received on Thu Aug 07 2003 - 17:04:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US