| 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: Number of SQL stmts.
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):
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
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: ORACLE-L_at_fatcity.com
Cc: Terrian, Tom
Tom,
How do you plan to use this ratio?
Jared
"Terrian, Tom" <tterrian_at_daas.dla.mil>
Sent by: root_at_fatcity.com
08/20/2002 10:37 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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: tterrian_at_daas.dla.mil 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 Tue Aug 20 2002 - 13:09:58 CDT
 |
 |