RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
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 [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
RE: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
select /*+ ordered */ s.username ,st.address ,st.sql_text from v$session s ,v$open_cursor oc ,v$sqltext st where s.sid = usid and s.saddr = oc.saddr and st.address = oc.address and st.hash_value = oc.hash_value order by st.address, st.piece / Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/07/2003 02:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR Yes, V$OPEN_CURSOR is instance-wide, but each row in the view sure looks like it can be attributed to a specific session via SADDR/ID/USERNAME. And the count of those rows for each session doesn't match the corresponding row/attribute in V$SESSSTAT. Erm, I'll leave off the product name here since this is the only DB problem I've seen with it, but I will say that it's a bug tracking package. Thanks, 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: 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).
RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
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).
Re: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
v$open_cursors is all open cursors in the instance v$sesstat is just open cursors in the session. what 3rd party product is that? is it cross platform? Ill make sure not to buy it. really bad code to get too many open cursors. From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/08/07 Thu PM 04:14:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR 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: [EMAIL PROTECTED] 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).
RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
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).
RE: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
Yes, V$OPEN_CURSOR is instance-wide, but each row in the view sure looks like it can be attributed to a specific session via SADDR/ID/USERNAME. And the count of those rows for each session doesn't match the corresponding row/attribute in V$SESSSTAT. Erm, I'll leave off the product name here since this is the only DB problem I've seen with it, but I will say that it's a bug tracking package. Thanks, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR v$open_cursors is all open cursors in the instance v$sesstat is just open cursors in the session. what 3rd party product is that? is it cross platform? Ill make sure not to buy it. really bad code to get too many open cursors. From: Jesse, Rich [EMAIL PROTECTED] Date: 2003/08/07 Thu PM 04:14:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR 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).
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: 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).
RE: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
There were discussions about this before. You can search the archive for it. v$open_cursor and the statistic opened cursor current doesn't always agree. v$open_cursor show cursors parsed and not closed, while the opened cursor current shows cursors opened and not closed. v$open_cursor doesn't track for unparsed dynamic cursors so it some times doesn't show all cursors that count toward the max open cursor parameter. Richard Ji -Original Message- Sent: Thursday, August 07, 2003 4:14 PM To: Multiple recipients of list ORACLE-L 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: Richard Ji 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).
Re: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
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
Re: V$SESSSTAT stat 3 vs V$OPEN_CURSOR
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
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).
