| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cursor statistics request
Under Oracle Financials:
NAME min avg var max ----------------------------------- ---- ------ ------ -------- opened cursors cumulative 0 201 32 22968 opened cursors current 0 31 6 335 Order entry system:NAME min avg var
max
----------------------------------- ---- ------ ------ -------- opened cursors cumulative 0 302 48 36013 opened cursors current 0 20 4 65 Datawarehouse:NAME min avg varmax
----------------------------------- ---- ------ ------ -------- 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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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 9999999
col avg format 99999
col var format 99999
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 min avg var max ----------------------------------- ---- ------ ------ --------- opened cursors cumulative 0 46325 561 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!
|| -----Original Message-----
|| From: Steve Adams [mailto:steve.adams_at_ixora.com.au]
|| 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: steve.adams_at_ixora.com.au
||
|| 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: 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.com -- Author: Mohan, Ross INET: MohanR_at_STARS-SMI.com 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: 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). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com 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: 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 May 03 2001 - 12:56:44 CDT
 |
 |