dba tuning script

-- ------------------------------------------------------------------------------------- File Name    : http://www.oracle-base.com/dba/monitoring/tuning.sql-- Author       : DR Timothy S Hall-- Description  : Displays several performance indicators and comments on the value.-- Requirements : Access to the V$ views.-- Call Syntax  : @tuning-- Last Modified: 15/07/2000-- -----------------------------------------------------------------------------------SET SERVEROUTPUT ONSET LINESIZE 1000SET FEEDBACK OFFSELECT *FROM   v$database;PROMPTDECLARE  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;/PROMPTSET FEEDBACK ON