Contact Us  |  Ask for Pricing  |  Partners

The World's Fastest Storage®
 

superSSD > Education > Oracle Performance Center > Code List

Oracle Performance Center - Code List

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

Texas Memory Systems Sales - Call (713) 266-3200

Sign up for the RamSan Mailing List

Home  |  Company  |  Products  |  Support  |  Resources  |  Partners
 Contact Us  |  Legal  |   Library  |  Press  |  Success Stories  |  Privacy Policy

Copyright © 2006 Texas Memory Systems. All Rights Reserved.