|
|
--******************************************************************************
-- Procedure : track_perf_info
-- Purpose : list in csv format all parameters from snapshots
--
-- Usage Notes Requires set serveroutput only
-- if you do not give the end_snap_id, it will default to
-- the max(snap_id) - 1
-- Also requires select privilege on the following tables
-- 1. dba_hist_snapshot
-- 2. dba_hist_sysstat
-- Version 1.0
-- Oracle Version 10.2
-- Date 19 Oct 2007
--
-- Disclaimer This source code is provided free by Texas Memory Systems. This
-- software is provide without support, and the user assumes all
-- risk in it's use. You may use, or modify this in any way that
-- you choose
--
--
--set linesize 1200
--set heading off
--set pagesize 0
--spool on
--spool h:\test\run7.csv (change this to your drive location
--******************************************************************************
create or replace procedure track_perf_info( start_snap_id in number,
end_snap_id in number)
as
temp_value2 number := 0;
temp_value1 number := 0;
decsec number := 0;
snap_id_start number := 0;
snap_id_end number := 0;
end_interval_time timestamp(3);
begin_interval_time timestamp(3);
begin
dbms_output.put('start snap,end snap,elapsed_sec,physical read bytes,physical read IO requests,');
dbms_output.put('physical read total IO requests,physical read total bytes,');
dbms_output.put('physical read total multi block requests,physical reads,');
dbms_output.put('physical reads cache,physical reads direct,physical write IO requests,');
dbms_output.put('physical write bytes,physical write total IO requests,');
dbms_output.put('physical write total bytes,physical write total multi block requests,');
dbms_output.put_line('physical writes,physical writes direct');
for b in start_snap_id .. end_snap_id loop
snap_id_start := b;
snap_id_end := snap_id_start + 1;
dbms_output.put(snap_id_start);
dbms_output.put(',');
dbms_output.put(snap_id_end);
dbms_output.put(',');
select end_interval_time into end_interval_time from dba_hist_snapshot where snap_id = snap_id_end;
select begin_interval_time into begin_interval_time from dba_hist_snapshot where snap_id = snap_id_end;
select dec_seconds(begin_interval_time,end_interval_time) into decsec from dual;
dbms_output.put(decsec);
dbms_output.put(',');
--
-- get and caluculate physical_read_bytes
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical read bytes' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical read bytes' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical read IO requests
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical read IO requests' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical read IO requests' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical read IO requests
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical read total IO requests' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical read total IO requests' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical_read_total_bytes
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical read total bytes' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical read total bytes' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical read total multi block requests
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical read total multi block requests' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical read total multi block requests' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical_reads
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical reads' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical reads' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical reads cache
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical reads cache' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical reads cache' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical reads direct
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical reads direct' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical reads direct' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical write IO requests
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical write IO requests' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical write IO requests' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical_write_bytes
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical write bytes' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical write bytes' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical write total IO requests
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical write total IO requests' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical write total IO requests' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical_write_total_bytes
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical write total bytes' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical write total bytes' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical write total multi block requests
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical write total multi block requests' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical write total multi block requests' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical_write
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical writes' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical writes' and snap_id = snap_id_end;
dbms_output.put( temp_value2 - temp_value1 );
dbms_output.put(',');
temp_value2 := 0;
temp_value1 := 0;
--
-- get and caluculate physical writes direct
--
select value into temp_value1 from dba_hist_sysstat where stat_name = 'physical writes direct' and snap_id = snap_id_start;
select value into temp_value2 from dba_hist_sysstat where stat_name = 'physical writes direct' and snap_id = snap_id_end;
dbms_output.put_line( temp_value2 - temp_value1 );
temp_value2 := 0;
temp_value1 := 0;
end loop;
end track_perf_info;
/ |
