Good question Jared. I am currently storing data on my production databases in
a home grown metadata database (similar to the command center database that
Kevin Loney wrote about). That database stores information from each of the
production databases v$sysstat and v$system_event tables. Based on that
information, I am able to produce the following chart (and others):
=============================
3. Database WAIT Information:
=============================
Last Hour:
----------
. Wait | CentiSecs per
Database Seconds Executions | Execution
=========================================|=============
CC2P 26 3,519 | .75
TRP1PD 41 10,291 | .39
GARVESTP 6 495 | 1.15
HNVP 6 109 | 5.92
MDGD 96 8,168 | 1.17
ZMARSP 20 883 | 2.28
FOTSP 2,491 867,101 | .29
WPASAP 11 2,319 | .45
GEMP 44 14,607 | .3
AANFXPD 58 535 | 10.77
KARP 52 9,571 | .54
.
History (per hour):
----------
. Wait | CentiSecs
Database Seconds Executions | Execution
=========================================|=============
CC2P 79 9,134 | .87
TRP1PD 248 49,826 | .5
GARVESTP 9 880 | 1.01
HNVP 21 28,984 | .07
MDGD 480 523,740 | .09
ZMARSP 343 5,910 | 5.81
FOTSP 1,189 410,088 | .29
WPASAP 19 1,658 | 1.17
GEMP 23 33,993 | .07
AANFXPD 269 1,827 | 14.73
KARP 136 25,047 | .54
.
By comparison of the History waits per hour vs. the waits from the last hour, I
can identify current problems. Again, I produce several other reports to
identify exactly what each database is waiting on. But, in the report above, I
am getting the EXECUTION numbers from "execution count" in v$sysstat which
appears to include the recursive SQL statements. Any way to get the actual
number of SQL statements issued to make the above report more meaningful?
Tom
-----Original Message-----
Sent: Tuesday, August 20, 2002 12:44 PM
To: [EMAIL PROTECTED]
Cc: Terrian, Tom
Tom,
How do you plan to use this ratio?
Jared
"Terrian, Tom" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/20/2002 10:37 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Number of SQL stmts.
Thanks Naveen. That makes sense. My dilemma is that I am trying to get a
ratio between the number of waits in v$system_event and the number of sql
statements issued. So, does anyone know if the TOTAL_WAITS field in the
V$SYSTEM_EVENT table includes wait generated by recursive SQL statements?
If it doesn't, does anyone know how to get the total number of SQL
statements (non-recursive) issued against a database?
Thanks,
Tom
-----Original Message-----
Sent: Tuesday, August 20, 2002 1:05 PM
To: Multiple recipients of list ORACLE-L
I think it also counts the recursive SQL statements issued -----Original
Message-----
Sent: Tuesday, August 20, 2002 9:08 PM
To: Multiple recipients of list ORACLE-L
All,
I am trying to determine the number of sql statements issued against my
databases. Does anyone know if the "execution count" in v$sysstat is the
number of sql statements issued against the database? I know that this is
a cumulative number from when the database was last bounce, but it still
seems very high. Does this really show me the number of statements issued
since the last bounce?
Thanks,
Tom
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Terrian, Tom
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).
