Tuning script for Oracle Database 1

SET SERVEROUTPUT ON SET LINESIZE 1000 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; / PROMPT SET FEEDBACK ON