Script will list below information with comments
Dictionary Cache Hit Ratio
Library Cache Hit Ratio
DB Block Buffer Cache Hit Ratio
Latch Hit Ratio
Disk Sort Ratio
Rollback Segment Waits
Dispatcher Workload
SET SERVEROUTPUT ON
SET LINESIZE 280
SET FEEDBACK OFF
SELECT *
FROM v$database;
PROMPT
DECLARE
v_value NUMBER;
FUNCTION Format(p_value IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';
END;
BEGIN
-- --------------------------
-- Dictionary Cache Hit Ratio
-- --------------------------
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
INTO v_value
FROM v$rowcache;
DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));
IF v_value < 90 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
INTO v_value
FROM v$librarycache;
DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));
IF v_value < 99 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
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 phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));
IF v_value < 89 THEN
DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
INTO v_value
FROM v$latch;
DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));
IF v_value < 98 THEN DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%'); ELSE DBMS_Output.Put_Line('Value Acceptable.'); 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('Disk Sort Ratio : ' || Format(v_value)); IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ----------------------
-- Rollback Segment Waits
-- ----------------------
SELECT (Sum(waits) / Sum(gets)) * 100
INTO v_value
FROM v$rollstat;
DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------
-- Dispatcher Workload
-- -------------------
SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
INTO v_value
FROM v$dispatcher;
DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));
IF v_value > 50 THEN
DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
END;
/
