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