RE: Database tracking

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Wed, 15 Jan 2003 13:26:19 -0800
Message-ID: <F001.00530B6B.20030115132619@fatcity.com>

• 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). Received on Wed Jan 15 2003 - 15:26:19 CST