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: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Thu, 07 Aug 2003 15:24:27 -0800
Message-ID: <F001.005C9877.20030807152427@fatcity.com>


Ryan,

Yes it is, as long you limit the results based on SID. The same goes for v$open_cursor.

Tanel.

> really? I thought v$sesstat was session based statistics?
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, August 07, 2003 5:39 PM
>
>
> > 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: Ryan
> INET: rgaffuri_at_cox.net
>
> 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).
Received on Thu Aug 07 2003 - 18:24:27 CDT

Original text of this message

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