Ryan, Yes it is, as long you limit the results based on SID. The same goes for v$open_cursor.
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 08, 2003 1:54 AM > really? I thought v$sesstat was session based statistics? > > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > 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 > > [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > > > > > > > -----Original Message----- > > > From: Tanel Poder [mailto:[EMAIL PROTECTED] > > > 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" <[EMAIL PROTECTED]> > > > 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 > > > > [EMAIL PROTECTED] 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 > > > > > [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jesse, Rich > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
