139 lines
4.0 KiB
SQL
139 lines
4.0 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- File Name : https://oracle-base.com/dba/monitoring/tuning.sql
|
|
-- Author : Tim 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 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
|