Procedure perform.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;
/