JL Computer Consultancy

Performance snapshots written to file

March 1999

This article is a follow-up to a previous is a follow-up to an article posted on this site a few months ago which demonstrated how to use PL/SQL tables to get rapid snapshots of current performance statistics.

There are four irritating features in the package supplied - first it was necessary to run one session per set of statistics required, second you had to remember to set serveroutput on for each session, third you had spool the output to a file, and finally you had to keep restarting the snapshots manually.

This article address all these features. First, the package allows you to choose which sets of stats you want to see from a single session, secondly you do not have to worry about serveroutput, thirdly the output always goes to a suitably named file (with a timestamp in the name), and finally you can request that the report recycles automatically.

This note is full text of the package itself, split into several sections, one for each of the v$ tables that can be monitored. There is a feature of PL/SQL which requires you to declare all cursors and types in a package before declaring any procedures. Consequently the code for each V$ view is handled in two sections - the first declares a cursor and typed record, the second handles the actual operation of the snapshot.

To allow files to be written, two things have to be true: the directory must be referenced in the init.ora file for the instance under the parameter utl_file_dir, (e.g. utl_file_dir=c:\temp\\ for NT, utl_file_dir=/tmp/ for Unix); the directory must be writable by the Oracle id.

At present I have only loaded 2 of the view handlers onto the site. More will follow shortly.

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.

Back to Main Index of Topics

rem   Script:     perfpack3.sql
rem   Author:     J P Lewis
rem   Dated:      22-Jan-1999
rem   Purpose:    Monitor several stats with a single call
rem   Usage:
rem         execute performance_snapshot.to_file(
rem              i_directory => {dir},
rem              i_time_lapse => {minutes},
rem              i_cycles => {count},
rem              i_want_sys_events => {TRUE/FALSE}
rem              i_want_file_stats => {TRUE/FALSE}
rem              i_want_session_io => {TRUE/FALSE}
rem              i_want_sys_stats  => {TRUE/FALSE}
rem              i_want_roll_stats => {TRUE/FALSE}
rem              i_want_wait_stats => {TRUE/FALSE}
rem              i_want_file_waits => {TRUE/FALSE}
rem         );
rem         execute performance_snapshot.to_file(
rem              i_directory => '/tmp/',
rem              i_time_lapse => 5,
rem              i_cycles => 12,
rem              i_want_sys_events => TRUE
rem         );
rem   Notes:
rem         The directory where the files will be written HAS to be
rem         listed under utl_file_dir in the init.ora file.
rem         The default action is to record one snapshot of 10 minutes
rem         and produce NO stats whatsoever.
rem         Useful only for a single instance, but could be modified
rem         by changes to cursor c1 for multi-instance.
rem         The package has to be created by SYS mainly because of 
rem         the references to the x$ objects in some procedures
create or replace package performance_snapshot as
procedure to_file (
      i_directory             in     varchar2, 
      i_time_lapse            in     number      default 10,
      i_cycles                in     number      default 1,
      i_want_sys_events       in     boolean     default FALSE,
      i_want_file_stats       in     boolean     default FALSE,
      i_want_session_io       in     boolean     default FALSE,
      i_want_sys_stats        in     boolean     default FALSE,
      i_want_roll_stats       in     boolean     default FALSE,
      i_want_wait_stats       in     boolean     default FALSE,
      i_want_file_waits       in     boolean     default FALSE
end performance_snapshot;
create or replace package body performance_snapshot as
procedure to_file (
      i_directory            in      varchar2,
      i_time_lapse           in      number      default 10,
      i_cycles               in      number      default 1,
      i_want_sys_events      in      boolean     default FALSE,
      i_want_file_stats      in      boolean     default FALSE,
      i_want_session_io      in      boolean     default FALSE,
      i_want_sys_stats       in      boolean     default FALSE,
      i_want_roll_stats      in      boolean     default FALSE,
      i_want_wait_stats      in      boolean     default FALSE,
      i_want_file_waits      in      boolean     default FALSE
) is
vcTimeStamp      varchar2(10);
--    Run any subscripts to generate internal procedures
      for v_cycles_done in 0..i_cycles loop
            if i_want_sys_events then
            end if;
            if i_want_sys_stats then
            end if;
            if v_cycles_done != i_cycles then
                  dbms_lock.sleep(60 * i_time_lapse);
            end if;
      end loop;
exception   -- to put out a warning about files
            or utl_file.invalid_mode
            or utl_file.invalid_filehandle
            or utl_file.invalid_operation
            or utl_file.read_error
            or utl_file.write_error
            or utl_file.internal_error
               dbms_output.put_line('File handling problem');
        when others then 
end to_file;
end performance_snapshot;
grant execute on performance_snapshot to public;
drop public synonym performance_snapshot;
create public synonym performance_snapshot for sys.performance_snapshot;

Sample of Execution and Output

               i_directory => 'c:\temp\',
               i_time_lapse => 1,
               i_cycles => 1,
               i_want_sys_events => true


System Events in 1 minutes to 22-Mar 23:03:28
Event                                             Waits        Csec    Avg Csec
-----                                             -----        ----    --------
pmon timer                                           20       6,009     300.450
rdbms ipc message                                    61      45,185     740.738
PL/SQL lock timer                                     1       6,000   6,000.000

Back to Main Index of Topics