Hello, this script might give you a little bit of insight on how is your database performing.
DECLARE
v_value NUMBER;
-- outout setting
v_ok VARCHAR2(50) := '<< Value seems to be OK >>';
v_rpad_size NUMBER := 35;
v_rpad_char CHAR(1) := ' ';
v_delimiter VARCHAR2(3) := ':';
FUNCTION format_value(p_value IN NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN lpad(to_char(round(p_value, 2), '990.00') || '%', 8, ' ') || ' ';
END;
FUNCTION hint_return(p_value IN VARCHAR2, p_direction IN VARCHAR2, p_limit IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN '<< Increase '||p_value||' to bring value '||p_direction||' '||p_limit||' >>';
END;
BEGIN
/* Dictionary Cache Hit Ratio */
SELECT (1 - (SUM(getmisses) / (SUM(gets) + SUM(getmisses)))) * 100
INTO v_value
FROM v$rowcache;
dbms_output.put(rpad('Dictionary Cache Hit Ratio',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value < 90 THEN dbms_output.put_line(hint_return('SHARED_POOL_SIZE parameter','above','90%'));
ELSE dbms_output.put_line(v_ok);
END IF;
/* Library Cache Hit Ratio */
SELECT (1 - (SUM(reloads) / (SUM(pins) + SUM(reloads)))) * 100
INTO v_value
FROM v$librarycache;
dbms_output.put(rpad('Library Cache Hit Ratio',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value < 99 THEN dbms_output.put_line(hint_return('SHARED_POOL_SIZE parameter','above','99%'));
ELSE dbms_output.put_line(v_ok);
END IF;
/* DB Block Buffer Cache Hit Ratio */
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
INTO v_value
FROM v$sysstat phys
,v$sysstat db
,v$sysstat cons
WHERE 1=1
AND phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
dbms_output.put(rpad('DB Block Buffer Cache Hit Ratio',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value < 89 THEN dbms_output.put_line(hint_return('DB_BLOCK_BUFFERS parameter','above','89%'));
ELSE dbms_output.put_line(v_ok);
END IF;
/* Latch Hit Ratio */
SELECT (1 - (SUM(misses) / SUM(gets))) * 100
INTO v_value
FROM v$latch;
dbms_output.put(rpad('Latch Hit Ratio',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value < 98 THEN dbms_output.put_line(hint_return('number of latches','above','98%'));
ELSE dbms_output.put_line(v_ok);
END IF;
/* Disk Sort Ratio */
SELECT (disk.value / mem.value) * 100
INTO v_value
FROM v$sysstat disk
,v$sysstat mem
WHERE disk.name = 'sorts (disk)'
AND mem.name = 'sorts (memory)';
dbms_output.put(rpad('Disk Sort Ratio',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value > 5 THEN dbms_output.put_line(hint_return('SORT_AREA_SIZE parameter','below','5%'));
ELSE dbms_output.put_line(v_ok);
END IF;
/* Rollback Segment Waits */
SELECT (SUM(waits) / SUM(gets)) * 100
INTO v_value
FROM v$rollstat;
dbms_output.put(rpad('Rollback Segment Waits',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value > 5 THEN dbms_output.put_line(hint_return('number of Rollback Segments','below','5%'));
ELSE dbms_output.put_line(v_ok);
END IF;
/* Dispatcher Workload */
SELECT nvl((SUM(busy) / (SUM(busy) + SUM(idle))) * 100, 0)
INTO v_value
FROM v$dispatcher;
dbms_output.put(rpad('Dispatcher Workload',v_rpad_size,v_rpad_char)||v_delimiter||format_value(v_value));
IF v_value > 50 THEN dbms_output.put_line(hint_return('MTS_DISPATCHERS','below','50%'));
ELSE dbms_output.put_line(v_ok);
END IF;
END;
/