Oracle Performance Indicator scripts

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 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: