New Feature 2-June-1999: I have rewritten the page - the code below is a sample of how the package works showing just one of the many snapshots available. Since 18th May I have also added tablespaces I/O, and an option for reporting file names with the file I/O. If you want the whole package it's here, size ca. 30K.
This article is a follow-up to an article written for Relate (the magazine of the UK Oracle User Group) in Feburary 1999. The article describes how to use a PL/SQL package to allow all users access to quick performance snapshots that can be dumped to screen using the dbms_output package. The introduction and the article itself are on this site with details of how you might use it.
This note is a single procedure extracted from the package itself. A version which demonstrates how to use utl_file to send results to files is also available on this site, but this is currently a very restricted version demonstrating the utl_file method but containing only a couple of the snapshots.
The code works equally well on 7.3.3 and 8.0, but some features that could be reported from 8.0 have been ignored to allow for maximum compatibility.
Note in particular that this code will not work with earlier versions of 7 because of the use of indexed functions on PL/SQL tables and the use of tables of records, which have only become available in recent versions.
New Feature 18-May-1999: By inserting a minor change to each procedure, the package is now capable of reporting the statistcs since startup time. To invoke this option use a 'wait time' of zero.
Addition- dated 5-May-1999 I have just added a 'Latches' procedure to this package. I have also noted that the X$ object I use in tracking wait stats has changed definition from v7 to v8, so I have added an extra (commented) cursor to allow for this.
Addition- dated 18-Apr-1999 I have just added a 'session events' procedure to this package. Be warned however that the size of the PL/SQL table generated to track this view is large - the number or rows is 155 * the sessions parameter from your init.ora file, so do not use the script casually, and do not leave the session live after running this option
rem
rem Script: perfpack.sql
rem Author: J P Lewis
rem Dated: 23-Sep-1998
rem
rem Usage:
rem execute performance_snapshot.{task}({time_in_seconds});
rem execute performance_snapshot.system_events(15);
rem execute performance_snapshot.system_events;
rem
rem performance_snapshot.system_events
rem performance_snapshot.session_events
rem performance_snapshot.ts_stats
rem performance_snapshot.file_stats
rem performance_snapshot.filename_stats
rem performance_snapshot.session_io
rem performance_snapshot.system_stats
rem performance_snapshot.roll_stats
rem performance_snapshot.wait_stats
rem performance_snapshot.file_waits
rem performance_snapshot.latches
rem
rem Notes:
rem When run from SQL*plus, you must first issue:
rem set serveroutput on
rem
rem Useful only for a single instance, but could be modified
rem by changes to cursor c1 for multi-instance.
rem
rem The package has to be created by SYS mainly because of
rem the references to the x$ objects in some procedures
rem
create or replace package performance_snapshot as procedure system_events (i_period in number default 10); end; /
create or replace package body performance_snapshot as
procedure system_events (i_period in number default 10) is cursor c1 is select d.indx indx, d.kslednam event, s.ksleswts total_waits, s.kslestim time_waited from x$kslei s, x$ksled d where s.indx = d.indx order by d.indx ; type w_type is table of c1%rowtype index by binary_integer; w_list w_type;
begin
if (i_period != 0) then
for r in c1 loop
w_list(r.indx).event := r.event;
w_list(r.indx).total_waits := r.total_waits;
w_list(r.indx).time_waited := r.time_waited;
end loop;
dbms_lock.sleep (i_period);
end if;
dbms_output.put_line('---------------------------------');
dbms_output.put_line('System Events - ' ||
to_char(sysdate,'dd-Mon hh24:mi:ss')
);
dbms_output.put_line('Interval: ' || i_period || ' seconds');
dbms_output.put_line('---------------------------------');
dbms_output.put_line(
rpad('Event',43) ||
lpad('Waits',12) ||
lpad('Csec',12) ||
lpad('Avg Csec',12)
);
dbms_output.put_line(
rpad('-----',43) ||
lpad('-----',12) ||
lpad('----',12) ||
lpad('--------',12)
);
for r in c1 loop
if (not w_list.exists(r.indx)) then
w_list(r.indx).total_waits := 0;
w_list(r.indx).time_waited := 0;
end if;
if (
(w_list(r.indx).total_waits != r.total_waits)
or (w_list(r.indx).time_waited != r.time_waited)
) then
dbms_output.put(rpad( substr(r.event,1,43),43));
dbms_output.put(to_char(
r.total_waits - w_list(r.indx).total_waits,
'999,999,990')
);
dbms_output.put(to_char(
r.time_waited - w_list(r.indx).time_waited,
'999,999,990'));
dbms_output.put_line(to_char(
(r.time_waited - w_list(r.indx).time_waited)/
greatest(
r.total_waits - w_list(r.indx).total_waits,
1
),
'999,999.990'));
end if;
end loop;
end system_events;
end performance_snapshot; /