-- worlds_smallest_perfmon
--
-- Monitors wait time and logs information to database alert logs.
--
-- p_interval = # of minutes to wait between checks
-- p_alert = # of seconds per minute spent in wait that triggers alert
--
-- This code is completely untested, use at your own risk. Run this for
-- a few hours to establish a baseline for performance and set you your
-- alert log monitor to capture the alert string. Consider adding or
-- removing events from those used to calculate the total wait time.
--
--
create or replace procedure worlds_smallest_perfmon (p_interval number,
p_alert number) is
l_new_total number(8,0) := 0;
l_old_total number(8,0) := 0;
l_diff number(8,0) := 0;
l_log varchar2(50);
begin
while true loop
select sum(time_waited/100) into l_new_total
from v$system_event
where event in
('db file scattered read',
'db file sequential read',
'log file sync',
'latch free',
'buffer busy waits',
'enqueue',
'log buffer space'
);
l_diff := (l_new_total - l_old_total)/p_interval;
if l_diff >= 0 and l_old_total > 0 then
if l_diff >= p_alert then
l_log := '*** PERFORMANCE ALERT WAITING ' || l_diff || '
s/min';
else
l_log := '*** WAITING ' || l_diff || ' s/min';
end if;
sys.dbms_system.ksdwrt(2,l_log);
end if;
l_old_total := l_new_total;
dbms_lock.sleep(p_interval*60);
end loop;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack);
end;
/
-----Original Message-----
Sent: Wednesday, January 15, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L
A more comprehensive solution would be statspack.
A simpler solution would be to get the sum of wait time (not counting the
idle ones) . it could provide you with some measure of database
performance... You need to arrive at a baseline wait time as being normal
for your database and any deviation from that could mean some change in
performance...
Babu
"Terrian, Tom
(Contractor) To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>
(DAASC)" cc:
<[EMAIL PROTECTED] Subject: Database tracking
a.mil>
Sent by:
[EMAIL PROTECTED]
01/15/2003 12:53
PM
Please respond to
ORACLE-L
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
