Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 02nd, 2009
DECLARE
libcac NUMBER(10,2);
rowcac NUMBER(10,2);
bufcac NUMBER(10,2);
redlog NUMBER(10,2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;BEGINSELECTVALUEINTO redlog FROM v$sysstat
WHERE name ='redo log space requests';SELECT100*(SUM(pins)-SUM(reloads))/SUM(pins)INTO libcac FROM v$librarycache;SELECT100*(SUM(gets)-SUM(getmisses))/SUM(gets)INTO rowcac FROM v$rowcache;SELECT100*(cur.VALUE+ con.VALUE- phys.VALUE)/(cur.VALUE+ con.VALUE)INTO bufcac
FROM v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
WHERE cur.statistic# = ncu.statistic#
AND ncu.name ='db block gets'AND con.statistic# = nco.statistic#
AND nco.name ='consistent gets'AND phys.statistic# = nph.statistic#
AND nph.name ='physical reads';SELECTVALUEINTO spsize FROM v$parameter WHERE name ='shared_pool_size';SELECTVALUEINTO blkbuf FROM v$parameter WHERE name ='db_block_buffers';SELECTVALUEINTO logbuf FROM v$parameter WHERE name ='log_buffer';DBMS_OUTPUT.put_line('> SGA CACHE STATISTICS');DBMS_OUTPUT.put_line('> ********************');DBMS_OUTPUT.put_line('> SQL Cache Hit rate = '||libcac);DBMS_OUTPUT.put_line('> Dict Cache Hit rate = '||rowcac);DBMS_OUTPUT.put_line('> Buffer Cache Hit rate = '||bufcac);DBMS_OUTPUT.put_line('> Redo Log space requests = '||redlog);DBMS_OUTPUT.put_line('> ');DBMS_OUTPUT.put_line('> INIT.ORA SETTING');DBMS_OUTPUT.put_line('> ****************');DBMS_OUTPUT.put_line('> Shared Pool Size = '||spsize||' Bytes');DBMS_OUTPUT.put_line('> DB Block Buffer = '||blkbuf||' Blocks');DBMS_OUTPUT.put_line('> Log Buffer = '||logbuf||' Bytes');DBMS_OUTPUT.put_line('> ');IF
libcac <99THENDBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');ENDIF;IF
rowcac <85THENDBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');ENDIF;IF
bufcac <90THENDBMS_OUTPUT.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');ENDIF;IF
redlog >100THENDBMS_OUTPUT.put_line('*** HINT: Log Buffer value is rather low!');ENDIF;END;/