6) # "GENERAL TUNING VIEW"

6) # "GENERAL TUNING VIEW"

echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} GENERAL TUNING VIEW ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|' SERVEROUTPUT ON LINESIZE 1000 FEEDBACK OFF colsep '|'
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;
/
SET FEEDBACK ON
prompt ##############################################################
Prompt # Waits by Class                                             #
prompt ##############################################################
col waits for a50
SELECT 'Waits by Class | ' || wait_class as Waits, time_waited FROM v\$system_wait_class WHERE wait_class != 'Idle' ORDER BY time_waited DESC;
prompt ##############################################################
Prompt # Wait Class Breakdown                                       #
prompt ##############################################################
col waits for a50
SELECT 'Wait Class Breakdown | ' || wait_class as Waits, ROUND(aas, 2) FROM (SELECT n.wait_class, m.time_waited/m.INTSIZE_CSEC AAS FROM v\$waitclassmetric m, v\$system_wait_class n WHERE m.wait_class_id = n.wait_class_id AND n.wait_class != 'Idle'
UNION ALL
SELECT 'CPU', value/100 AAS FROM v\$sysmetric WHERE metric_name = 'CPU Usage Per Sec' AND group_id = 2);
quit;
EOF