tune.sql

rem
rem Procedure           perform.sql
rem
rem Description         This PL/SQL script monitors a database.
rem                     The following are monitored :-
rem
rem                     Buffer Cache
rem                     Library Cache
rem                     Dictionary Cache
rem                     Rollback Segment Waits
rem                     Sorts to disk
rem                     Cursor Usage
rem                     Transactions
rem                     File I/O Rate
rem                     Number of Locks
rem                     Unarchived Logs
rem                     Redo Log Space Waits
rem                     Enqueue Waits
rem
rem
rem Argument(s)         Number of loops and interval between in seconds
rem                     (Suggested interval 900).
rem
rem Author              Duncan Berriman, 25/5/98
rem                     Duncan@dcl.co.uk
rem                     http://www.dcl.co.uk
set echo on;
set serveroutput on size 10000;
declare
  /* Fetched from database */
  v_fetch_consistent_gets number :=0;
  v_fetch_db_block_gets number :=0;
  v_fetch_physical_reads number :=0;
  v_fetch_rollback_gets number :=0;
  v_fetch_rollback_waits number :=0;
  v_fetch_sorts_disk number :=0;
  v_fetch_sorts_memory number :=0;
  v_fetch_redo_space_requests number :=0;
  v_fetch_enqueue_waits number :=0;
  v_fetch_library_pins number :=0;
  v_fetch_library_pinhits number :=0;
  v_fetch_dictionary_gets number :=0;
  v_fetch_dictionary_misses number :=0;
  v_fetch_total_io number :=0;
  /* parameters from INIT.ORA */
  v_open_cursors_parameter number;
  v_transactions_parameter number;
  /* Calculated values */
  v_logical_reads number;
  v_consistent_gets number;
  v_db_block_gets number;
  v_physical_reads number;
  v_rollback_gets number;
  v_rollback_waits number;
  v_sorts_disk number;
  v_sorts_memory number;
  v_redo_space_requests number;
  v_enqueue_waits number;
  v_library_pins number;
  v_library_pinhits number;
  v_dictionary_gets number;
  v_dictionary_misses number;
  v_total_io number;
  /* Fetched from database */
  v_open_cursors_current number;
  v_transactions number;
  v_unarchived_count number;
  v_total_locks number;
 /* Store last values for calculations */
 v_last_consistent_gets number;
 v_last_db_block_gets number;
 v_last_physical_reads number;
 v_last_rollback_gets number;
  v_last_rollback_waits number;
  v_last_sorts_disk number;
  v_last_sorts_memory number;
  v_last_redo_space_requests number;
  v_last_enqueue_waits number;
  v_last_library_pins number;
  v_last_library_pinhits number;
  v_last_dictionary_gets number;
  v_last_dictionary_misses number;
  v_last_total_io number;
  /* Ratio */
  v_buffer_cache_hit_ratio integer;

  v_rollback_wait_ratio integer;

  v_sorts_disk_ratio integer;

  v_open_cursors_ratio integer;

  v_library_pinhits_ratio integer;

  v_dictionary_cache_ratio integer;

  v_transactions_ratio integer;

  v_total_io_rate integer;



  /* General */



  v_counter integer;

  v_interval integer;

  v_date_time varchar2(15);



procedure db_output (message in varchar) is
  begin
    dbms_output.put_line(message);
end;

procedure get_param is
begin
    select value into v_open_cursors_parameter from v$parameter where name = 'open_cursors';
    select value into v_transactions_parameter from v$parameter where name = 'transactions';
end;

procedure get_stats is
 begin
   v_last_consistent_gets := v_fetch_consistent_gets;
   v_last_db_block_gets := v_fetch_db_block_gets;
    v_last_physical_reads := v_fetch_physical_reads;
    v_last_library_pins := v_fetch_library_pins;
    v_last_library_pinhits := v_fetch_library_pinhits;
    v_last_dictionary_gets := v_fetch_dictionary_gets;
    v_last_dictionary_misses := v_fetch_dictionary_misses;
    v_last_rollback_gets := v_fetch_rollback_gets;
    v_last_rollback_waits := v_fetch_rollback_waits;
    v_last_sorts_disk := v_fetch_sorts_disk;
    v_last_sorts_memory := v_fetch_sorts_memory;
    v_last_enqueue_waits := v_fetch_enqueue_waits;
    v_last_redo_space_requests := v_fetch_redo_space_requests;
    v_last_total_io := v_fetch_total_io;
 

    select value into v_fetch_consistent_gets from v$sysstat where name = 'consistent gets';
    select value into v_fetch_db_block_gets from v$sysstat where name = 'db block gets';
    select value into v_fetch_physical_reads from v$sysstat where name = 'physical reads';
    select sum(pinhits),sum(pins) into v_fetch_library_pinhits,v_fetch_library_pins from v$librarycache;
    select sum(gets),sum(getmisses) into v_fetch_dictionary_gets,v_fetch_dictionary_misses from v$rowcache;
    select sum(waits),sum(gets) into v_fetch_rollback_waits,v_fetch_rollback_gets from v$rollstat;
    select value into v_fetch_sorts_disk from v$sysstat where name = 'sorts (disk)';
    select value into v_fetch_sorts_memory from v$sysstat where name = 'sorts (memory)';
    select value into v_open_cursors_current from v$sysstat where name = 'opened cursors current';
    select value into v_fetch_redo_space_requests from v$sysstat where name = 'redo log space requests';
    select value into v_fetch_enqueue_waits from v$sysstat where name = 'enqueue waits';
    select sum(xacts) into v_transactions from v$rollstat;
    select sum(phyrds)+sum(phywrts) into v_fetch_total_io from v$filestat;
    select count(lockwait) into v_total_locks from v$session where lockwait is not null;
    select count(archived) into v_unarchived_count from v$log where archived = 'NO' and status not in ('INACTIVE','CURRENT');

  end;



begin

 get_param;    /* Get Fixed parameters */
 get_stats;            /* Get Initial Values of statistics */
  v_counter := &loops;
  v_interval := &interval;
 while v_counter > 0
  loop
    /* Sleep for more */
    v_date_time := to_char(sysdate,'dd-mon-yy hh24:mi');
    db_output('Sleeping at '||v_date_time||'...');
    v_counter := v_counter - 1;
    dbms_lock.sleep(v_interval);  
    /* Get statistics */
    get_stats;
    /* Check Buffer Cache Hit Ratio */
   v_consistent_gets := v_fetch_consistent_gets - v_last_consistent_gets;
    if v_consistent_gets < 0 
    then
     v_consistent_gets := v_fetch_consistent_gets;
    end if;
    v_db_block_gets := v_fetch_db_block_gets - v_last_db_block_gets;
   if v_db_block_gets < 0 
    then
      v_db_block_gets := v_fetch_db_block_gets;
    end if;
    v_physical_reads := v_fetch_physical_reads - v_last_physical_reads;
    if v_physical_reads < 0 
    then

      v_physical_reads := v_fetch_physical_reads;

    end if;
    v_logical_reads := v_consistent_gets + v_db_block_gets;

    if v_logical_reads < 1
    then
     v_logical_reads := 1;
    end if;



    v_buffer_cache_hit_ratio := (v_logical_reads*100)/(v_logical_reads + v_physical_reads);



    db_output('Buffer Cache Hit Ratio is '||to_char(v_buffer_cache_hit_ratio)||'
%');



    /* Check Library Cache */



    v_library_pinhits := v_fetch_library_pinhits - v_last_library_pinhits;

    if v_library_pinhits < 0
    then
      v_library_pinhits := v_fetch_library_pinhits;
    end if;

   

    v_library_pins := v_fetch_library_pins - v_last_library_pins;

    if v_library_pins < 0

    then
      v_library_pins := v_fetch_library_pins;
    end if;

    if v_library_pins < 1
    then
      v_library_pins := 1;
    end if;



    v_library_pinhits_ratio := ((v_library_pinhits * 100) / v_library_pins);



    db_output('Library Cache Hit Ratio is '||to_char(v_library_pinhits_ratio)||'%');



    /* Check Library Cache */



    v_dictionary_misses := v_fetch_dictionary_misses - v_last_dictionary_misses;

    if v_dictionary_misses < 0
    then
      v_dictionary_misses := v_fetch_dictionary_misses;
    end if;

      

    v_dictionary_gets := v_fetch_dictionary_gets - v_last_dictionary_gets;

    if v_dictionary_gets < 0
    then
      v_dictionary_gets := v_fetch_dictionary_gets;
    end if;

    if v_dictionary_gets < 1
    then
      v_dictionary_gets := 1;
    end if;



    v_dictionary_cache_ratio := ((v_dictionary_gets * 100) / (v_dictionary_misses + v_dictionary_gets));

    db_output('Dictionary Cache Hit Ratio is '||to_char(v_dictionary_cache_ratio)||'%');



    /* Check for Rollback segment waits */



    v_rollback_waits := v_fetch_rollback_waits - v_last_rollback_waits;

    if v_rollback_waits < 0 
    then
      v_rollback_waits := v_fetch_rollback_waits;
    end if;



    v_rollback_gets := v_fetch_rollback_gets - v_last_rollback_gets;

    if v_rollback_gets < 0 
    then
      v_rollback_gets := v_fetch_rollback_gets;
   end if;

    if v_rollback_gets < 1 
    then
     v_rollback_gets := 1;
    end if;



    v_rollback_wait_ratio := (v_rollback_waits * 100) / (v_rollback_gets);



    db_output('Rollback Segment Wait Ratio is '||to_char(v_rollback_wait_ratio)||'%');



    /* Check sorts to disk */



    v_sorts_disk := v_fetch_sorts_disk - v_last_sorts_disk;

    if v_sorts_disk < 0 
    then
      v_sorts_disk := v_fetch_sorts_disk;
    end if;



    v_sorts_memory := v_fetch_sorts_memory - v_last_sorts_memory;

    if v_sorts_memory < 0 
    then
      v_sorts_memory := v_fetch_sorts_memory;
    end if;

    if v_sorts_memory < 1 
    then
     v_sorts_memory := 1;
    end if;



    v_sorts_disk_ratio := (v_sorts_disk * 100) / (v_sorts_disk + v_sorts_memory);



    db_output('Sorts to Disk Ratio is '||to_char(v_sorts_disk_ratio)||'%');



    /* Check cursor usage */



    v_open_cursors_ratio := (v_open_cursors_current * 100) / (v_open_cursors_parameter);



    db_output('Cursor Usage Ratio is '||to_char(v_open_cursors_ratio)||'%'); 



    /* Check transaction usage */



    v_transactions_ratio := (v_transactions * 100) / (v_transactions_parameter);



    db_output('Transaction Usage Ratio is '||to_char(v_transactions_ratio)||'%'); 



    /* Check File IO Rate */



    v_total_io := v_fetch_total_io - v_last_total_io;

    if v_total_io < 0
    then
      v_total_io :=0;
    end if;
    v_total_io_rate := v_total_io / v_interval;



    db_output('File I/O Rate is '||to_char(v_total_io_rate)||' per second'); 



    /* Check number of locks */



    db_output('Number of users awaiting lock is '||to_char(v_total_locks));



    /* Check number of unarchived logs */



    db_output('Number of unarchived logs is '||to_char(v_unarchived_count));



    /* Check for redo log space waits */



    v_redo_space_requests := v_fetch_redo_space_requests - v_last_redo_space_requests;

    if v_redo_space_requests < 0 

    then

      v_redo_space_requests := v_fetch_redo_space_requests;

    end if;



    db_output('Redo Log Space Requests is '||to_char(v_redo_space_requests)); 



    /* Check for enqueue waits */



    v_enqueue_waits := v_fetch_enqueue_waits - v_last_enqueue_waits;

    if v_enqueue_waits < 0 

    then

      v_enqueue_waits := v_fetch_enqueue_waits;

    end if;



    db_output('Enqueue Waits is '||to_char(v_enqueue_waits)); 



  end loop;



end;

/