Contact Us  |  Ask for Pricing  |  Partners

The World's Fastest Storage®
 

superSSD > Education > Oracle Performance Center > Code List

Oracle Performance Center - Code List

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

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.