SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 80
SET LINES 132

REM
REM || Title : show_sga_stat.sql
REM ||
REM || Purpose : This script will Display database SGA statistics.
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

DECLARE
libcac NUMBER (10, 2);
rowcac NUMBER (10, 2);
bufcac NUMBER (10, 2);
redlog NUMBER (10, 2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;

BEGIN

SELECT VALUE
INTO redlog
FROM v$sysstat
WHERE name = 'redo log space requests';

SELECT 100 * (SUM (pins) - SUM (reloads)) / SUM (pins)
INTO libcac
FROM v$librarycache;

SELECT 100 * (SUM (gets) - SUM (getmisses)) / SUM (gets)
INTO rowcac
FROM v$rowcache;

SELECT 100 * (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';

SELECT VALUE
INTO spsize
FROM v$parameter
WHERE name = 'shared_pool_size';

SELECT VALUE
INTO blkbuf
FROM v$parameter
WHERE name = 'db_block_buffers';

SELECT VALUE
INTO 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 < 99
THEN
DBMS_OUTPUT.put_line ('HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;

IF rowcac < 85
THEN
DBMS_OUTPUT.put_line ('HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;

IF bufcac < 90
THEN
DBMS_OUTPUT.put_line ('HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;

IF redlog > 100
THEN
DBMS_OUTPUT.put_line ('HINT: Log Buffer value is rather low!');
END IF;
END;
/

SPOOL show_sga_stat.lis
/
SPOOL off