Cursor statistics request
Hey DBAs for big databases, Would you mind running this simple query? I am building a case that we have an extremely unheathly application, and would like to (a) do a reality check, and (b) anonymously cite evidence from other sites. col name format a35 col min format 999 col max format 999 col avg format 9 col var format 9 select name, min(value) min, avg(value) avg, sqrt(stddev(value)) var, max(value) max from v$sesstat ss, v$statname sn where sn.statistic#=ss.statistic# and sn.statistic# in (2,3) group by name / When I run this on my troubled system, I get: NAME minavgvar max --- -- -- - opened cursors cumulative 0 46325561 3748748 opened cursors current 0 54 9 345 And this for a instance only up for about a week. Oh, speaking of which, please tell me how long your instance has been up, and a rough idea of average user load, during the core processing hours. Thanks! - Ross || -Original Message- || From: Steve Adams [mailto:[EMAIL PROTECTED]] || Sent: Tuesday, May 01, 2001 11:00 AM || To: Multiple recipients of list ORACLE-L || Subject: RE: Oracle What savepoints are active for a given session? || || || Hi Venkata, || || There is no V$ view or X$ table that contains this || information. The only || solution that occurs to me is to dump the savepoints to the || process trace file || with || || alter session set events 'immediate trace name savepoints'; || || and then use UTL_FILE to read and parse the trace file || information. Here is an || example of what you might get: || || SAVEPOINT FOR CURRENT PROCESS || -- ||flag: 0x3 ||name: S3 ||dba: 0x831408, sequence #: 964, record #: 12, savepoint #: 19082 ||status: VALID, next: 3822f60 ||name: S2 ||dba: 0x831408, sequence #: 964, record #: 11, savepoint #: 19046 ||status: VALID, next: 37f63ec ||name: S1 ||dba: 0x831408, sequence #: 964, record #: 10, savepoint #: 18602 ||status: VALID, next: 0 || || This process has three savepoints named S1, S2 and S3 respectively. || || @ Regards, || @ Steve Adams || @ http://www.ixora.com.au/ || @ http://www.christianity.net.au/ || || || -Original Message- || Sent: Tuesday, 1 May 2001 18:20 || To: Multiple recipients of list ORACLE-L || || || HI PLEASE SEND ME ANSWER || Question Title: Oracle What savepoints are active for a || given session? || || Detailed Question: Does anybody know IF and HOW can I get a list of || active savepoints for the current session? I need a native way, not || solutions based on additional application-level || housekeeping. Some query || on the V$ tables/views would be the kind of answer I'm looking for. || Details: Within one stored proc I'd like to obtain a list || (in any form) || of the savepoints issued currently in the current || transaction. Example: || Proc A issues savepoint svA; then proc B issues savepoint || svB; and then || proc C builds and uses a cursor having 'svA' and 'svB' as rows, or || something like that. Of course, there are no intervening commits or || rollbacks. || || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || -- || Author: Steve Adams || INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 || San Diego, California-- Public Internet access / || Mailing Lists || || 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.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Cursor statistics request
Under Oracle Financials: NAME minavgvar max --- -- -- opened cursors cumulative 0201 3222968 opened cursors current 0 31 6 335 Order entry system:NAME minavgvar max --- -- -- opened cursors cumulative 0302 4836013 opened cursors current 0 20 4 65 Datawarehouse:NAME minavgvar max --- -- -- opened cursors cumulative 0 27 7 597 opened cursors current 0 7 2 22 Info: Database up time 20 Hours(database bounced everyday for snapshot) Load : user sessions between 300 to 500 HTH, Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 03 May 2001 10:05:35 -0800 Hey DBAs for big databases, Would you mind running this simple query? I am building a case that we have an extremely unheathly application, and would like to (a) do a reality check, and (b) anonymously cite evidence from other sites. col name format a35 col min format 999 col max format 999 col avg format 9 col var format 9 select name, min(value) min, avg(value) avg, sqrt(stddev(value)) var, max(value) max from v$sesstat ss, v$statname sn where sn.statistic#=ss.statistic# and sn.statistic# in (2,3) group by name / When I run this on my troubled system, I get: NAME minavgvar max --- -- -- - opened cursors cumulative 0 46325561 3748748 opened cursors current 0 54 9 345 And this for a instance only up for about a week. Oh, speaking of which, please tell me how long your instance has been up, and a rough idea of average user load, during the core processing hours. Thanks! - Ross || -Original Message- || From: Steve Adams [mailto:[EMAIL PROTECTED]] || Sent: Tuesday, May 01, 2001 11:00 AM || To: Multiple recipients of list ORACLE-L || Subject: RE: Oracle What savepoints are active for a given session? || || || Hi Venkata, || || There is no V$ view or X$ table that contains this || information. The only || solution that occurs to me is to dump the savepoints to the || process trace file || with || || alter session set events 'immediate trace name savepoints'; || || and then use UTL_FILE to read and parse the trace file || information. Here is an || example of what you might get: || || SAVEPOINT FOR CURRENT PROCESS || -- ||flag: 0x3 ||name: S3 ||dba: 0x831408, sequence #: 964, record #: 12, savepoint #: 19082 ||status: VALID, next: 3822f60 ||name: S2 ||dba: 0x831408, sequence #: 964, record #: 11, savepoint #: 19046 ||status: VALID, next: 37f63ec ||name: S1 ||dba: 0x831408, sequence #: 964, record #: 10, savepoint #: 18602 ||status: VALID, next: 0 || || This process has three savepoints named S1, S2 and S3 respectively. || || @ Regards, || @ Steve Adams || @ http://www.ixora.com.au/ || @ http://www.christianity.net.au/ || || || -Original Message- || Sent: Tuesday, 1 May 2001 18:20 || To: Multiple recipients of list ORACLE-L || || || HI PLEASE SEND ME ANSWER || Question Title: Oracle What savepoints are active for a || given session? || || Detailed Question: Does anybody know IF and HOW can I get a list of || active savepoints for the current session? I need a native way, not || solutions based on additional application-level || housekeeping. Some query || on the V$ tables/views would be the kind of answer I'm looking for. || Details: Within one stored proc I'd like to obtain a list || (in any form) || of the savepoints issued currently in the current || transaction. Example: || Proc A issues savepoint svA; then proc B issues savepoint || svB; and then || proc C builds and uses a cursor having 'svA' and 'svB' as rows, or || something like that. Of course, there are no intervening commits or || rollbacks. || || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || -- || Author: Steve Adams || INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 || San Diego, California-- Public Internet access / || Mailing Lists || || 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
RE: Cursor statistics request
Ross, Around 500 users, uptime 26days; OLTP system NAME minavgvar max --- -- -- opened cursors cumulative 0 10611282 1107346 opened cursors current 0161 11 250 -Original Message- SNIP When I run this on my troubled system, I get: NAME minavgvar max --- -- -- - opened cursors cumulative 0 46325561 3748748 opened cursors current 0 54 9 345 And this for a instance only up for about a week. Oh, speaking of which, please tell me how long your instance has been up, and a rough idea of average user load, during the core processing hours. Thanks! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).