RE: Cursor statistics request

Hi Ross,

NAME                                 min    avg    var      max
----------------------------------- ---- ------ ------ --------
opened cursors cumulative              0   5352    222   622970
opened cursors current                 0     26      5      217

Stats: This core Apps 10.7 database has been up since 18th March 01. There are between 200 to 300 Apps processes hanging around at any time.

John Kanagaraj

>-----Original Message-----
>From: Mohammad Rafiq [mailto:rafiq9857_at_hotmail.com]
>Sent: Thursday, May 03, 2001 11:32 AM
>To: Multiple recipients of list ORACLE-L
>Subject: 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
> var
>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: 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!
>
>- Ross
>
>
>
>|| -----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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).