JL Computer Consultancy

Performance snapshots using PL/SQL

September 1998


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

Back to Main Index of Topics


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;
/

Back to Main Index of Topics