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
