JL Computer Consultancy

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.

rem

rem   Script:     perfpack3.sql

rem   Author:     J P Lewis

rem   Dated:      22-Jan-1999

rem   Purpose:    Monitor several stats with a single call

rem

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

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

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

rem         The default action is to record one snapshot of 10 minutes

rem         and produce NO stats whatsoever.

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

--

@loop_evt.dec

@loop_sys.dec

@loop_evt.prc

@loop_sys.prc

begin

      for v_cycles_done in 0..i_cycles loop

            select

                  to_char(sysdate,'mmddhh24miss') 

            into

                 vcTimestamp 

            from

                 dual;

            if i_want_sys_events then

                  system_events(i_directory,vcTimeStamp,v_cycles_done);

            end if;

            if i_want_sys_stats then

                  system_stats(i_directory,vcTimeStamp,v_cycles_done);

            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

      when

               utl_file.invalid_path

            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

        then

               dbms_output.put_line('File handling problem');

                dbms_output.put_line(sqlcode);

        when others then 

               raise;

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

begin

        performance_snapshot.to_file(

               i_directory => 'c:\temp\',

               i_time_lapse => 1,

               i_cycles => 1,

               i_want_sys_events => true

        );

end;

/

----------------------------------------------

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