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