|
|
--------------------------------------------------------------------------------
-- PROCEDURE GET_A_STAT
--
-- Purpose list values between AWR snapshots for a range of snapshots
-- Limitations AWR support only
-- 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
--------------------------------------------------------------------------------
create or replace procedure get_a_stat (start_snap_id in number,
stat_name_in in varchar2,
end_snap_id in number default 0)
as
temp_value2 number := 0;
temp_value1 number := 0;
max_end_snap number := 0;
snap_id_end number := 0;
decsec number := 0;
begin_interval_time_end timestamp(3);
end_interval_time_end timestamp(3);
begin
--
-- no need to require input of end snapshot
-- look it up if the default of zero
--
if end_snap_id is null or end_snap_id = 0 then
select max(snap_id)- 1
into max_end_snap
from dba_hist_snapshot;
else
max_end_snap := end_snap_id;
end if;
--
-- loop through for all requested snap shots
--
for b in start_snap_id .. max_end_snap loop
snap_id_end := b + 1;
dbms_output.put(snap_id_end);
dbms_output.put(',');
select end_interval_time
into end_interval_time_end
from dba_hist_snapshot
where snap_id = snap_id_end;
select begin_interval_time
into begin_interval_time_end
from dba_hist_snapshot
where snap_id = snap_id_end;
dbms_output.put(begin_interval_time_end);
dbms_output.put(',');
--
-- caluclate in seconds the elapsed time between snap shots
-- dec_seconds function required
--
select dec_seconds(begin_interval_time_end,end_interval_time_end)
into decsec
from dual;
dbms_output.put(decsec);
dbms_output.put(',');
dbms_output.put(stat_name_in);
dbms_output.put(',');
--
-- get and caluculate stat_values
--
select value
into temp_value1
from dba_hist_sysstat
where stat_name = stat_name_in
and snap_id = b;
select value
into temp_value2
from dba_hist_sysstat
where stat_name = stat_name_in
and snap_id = snap_id_end;
dbms_output.put_line( temp_value2 - temp_value1 );
end loop;
end get_a_stat;
/ |
