Oracle-L: RE: Database tracking

From: <Jared.Still_at_radisys.com>
Date: Wed, 15 Jan 2003 13:52:13 -0800
Message-ID: <F001.00530C29.20030115135213@fatcity.com>

The worlds smallest perfmon could be 11 bytes smaller if you changed 'while true loop' to 'loop'

Jared

"Post, Ethan" <Ethan.Post_at_ps.net>
Sent by: root_at_fatcity.com
01/15/2003 01:26 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Database tracking

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 <ORACLE-L_at_fatcity.com>

                      (DAASC)"                 cc:

                      <tterrian_at_daas.dl        Subject:  Database tracking

                      a.mil>

                      Sent by:

                      root_at_fatcity.com

 

 

                      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: Ethan.Post_at_ps.net

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: 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.net
--

Author:
INET: Jared.Still_at_radisys.com

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