The following script is a master oracle database performance monitoring script for checking the
- DATABASE INFORMATION
- INSTANCE INFORMATION
- Total Space,Used Space, Free Space, % used space, % free space in all the tablespace
- Dictionary Cache Hit Ratio
- Library Cache Hit Ratio
- DB Block Buffer Cache Hit Ratio
- Latch Hit Ratio
- Disk Sort Ratio
- Rollback Segment Waits
- Dispatcher Workload
Write the following codes in a shell scripts name “performance_ratio_of_tablespace.sh”.
++++++++++++++++++++++++++++++++++++++++++++++++++++
#-- -----------------------------------------------------------------------------------
#-- Filename : performance_ratio_of_tablespace.sh
#-- Author : oracle-latest-technology.com
#-- Description : Displays several performance indicators and comments on the value.
#-- It also backs up the control file <backup location>/bkpctrlfile.ctl
#-- with current date in the same directory.
#-- Requirements : Access to the V$ views.
#-- Last Modified: 19/12/2011
#-- -----------------------------------------------------------------------------------
dt=`date +"%d.%m.%y"`
. <DB Home>/<SID>_<Domain>.env
RETVAL=`sqlplus -s <<!
conn / as sysdba
ALTER DATABASE BACKUP CONTROLFILE TO '<backup location>/bkpctrlfile.ctl';
SET SERVEROUTPUT ON
SET LINESIZE 125
SET PAGESIZE 1000
SET FEEDBACK OFF
col PLATFORM_NAME format a25
SPOOL <log file location>/prformnc_ratio_tbspc_details_$dt.txt
PROMPT DATABASE INFORMATION
SELECT NAME, LOG_MODE, PROTECTION_MODE, PROTECTION_LEVEL, PLATFORM_NAME
FROM V\\$DATABASE;
PROMPT
PROMPT INSTANCE INFORMATION
column "Host Name" format a20
column "Instance Name" format a20
column "Global Name" format a25
column "Startup Time" format a20
column "Restricted Mode" format a10
SELECT SUBSTR(I.HOST_NAME, 1, 13) "Host Name",
SUBSTR(I.INSTANCE_NAME, 1, 8) "Instance Name",
SUBSTR(E.GLOBAL_NAME, 1, 21) "Global Name",
TO_CHAR(I.STARTUP_TIME, 'DD-MON-YY HH:MI:SS') "Startup Time",
SUBSTR(DECODE(I.LOGINS, 'RESTRICTED', 'YES', 'NO'), 1, 3) "Restricted Mode"
FROM V\\$INSTANCE I, GLOBAL_NAME E;
col "Tablespace Name" for a30
col "Total Space" for 9999999999.99
col "Used Space" for 9999999999.99
col "Free Space" for 9999999999.99
col "% used" for 999.99
col "% Free" for 999.99
break on report
compute sum lab "TOTAL" of "Total Space" on REPORT
compute sum of "USED SPACE" on report
compute sum of "FREE SPACE" on REPORT
select a.tablespace_name "Tablespace Name",
sum(b.Total_space) "Total Space",
SUM(b.total_space - a.free_space) "Used Space",
SUM(a.Free_space) "Free Space",
round((sum(b.Total_space) - SUM(a.Free_space))/sum(b.Total_space) * 100, 2) "% used",
round(SUM(a.Free_space) / sum(b.Total_space) * 100, 2) "% free"
from (select tablespace_name,
file_id,
sum(bytes) / 1024 / 1024 as Free_Space
from Dba_Free_Space
group by tablespace_name, file_id) a,
(select tablespace_name,
file_id,
file_name,
sum(bytes) / 1024 / 1024 as Total_Space
from dba_data_files
group by tablespace_name, file_id, file_name) b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
group by a.tablespace_name
order by 6;
PROMPT OBJECTS UNABLE TO EXTEND
col "Object Type" for a20
col "Schema" for a15
col "Segment Name" for a30
col "Next Extent" for 9999999999
col "Tablespace" for a20
SELECT T.SEGMENT_TYPE "Object Type",
SUBSTR(T.OWNER, 1, 12) "Schema",
SUBSTR(T.SEGMENT_NAME, 1, 30) "Segment Name",
T.NEXT_EXTENT "Next Extent",
T.TABLESPACE_NAME "Tablespace"
FROM SYS.DBA_SEGMENTS T
WHERE T.NEXT_EXTENT >
(SELECT MAX(BYTES)
FROM DBA_FREE_SPACE F
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME);
PROMPT OBJECTS WHICH ARE NEARING TO MAXEXTENTS
col "Segment" for a30
col "Segment Type" for a30
col "Tablespace" for a25
col "No. of Extents" for 999999999999
col "Max Extents" for 999999999999
SELECT SEGMENT_NAME "Segment",
SEGMENT_TYPE "Segment Type",
TABLESPACE_NAME "Tablespace",
EXTENTS "No. of Extents",
MAX_EXTENTS "Max Extents"
FROM DBA_SEGMENTS
WHERE MAX_EXTENTS - 10 < EXTENTS
AND MAX_EXTENTS != 0
ORDER BY TABLESPACE_NAME;
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;
/
SPOOL OFF
EXIT
!`
+++++++++++++++++++++++++++++++++++++++++++++++++++++
The output of the above code segment will look like the following.
+++++++++++++++++++++++++++++++++++++++++++++++++++++
DATABASE INFORMATION
NAME LOG_MODE PROTECTION_MODE PROTECTION_LEVEL PLATFORM_NAME
--------- ------------ -------------------- -------------------- -------------------------
XXXX NOARCHIVELOG MAXIMUM PERFORMANCE UNPROTECTED Solaris[tm] OE (64-bit)
INSTANCE INFORMATION
Host Name Instance Name Global Name Startup Time Restricted
-------------------- -------------------- ------------------------- -------------------- ----------
xxxxxxxxxx XXXXXX xxxx.xxxxxxxx.xxxx. 16-DEC-11 11:24:52 NO
Tablespace Name Total Space Used Space Free Space % used % free
------------------------------ -------------- -------------- -------------- ------- -------
APPS_UNDOTS1 1374.00 1346.00 28.00 97.96 2.04
APPS_TS_SUMMARY 1000.00 889.63 110.38 88.96 11.04
APPS_TS_TX_IDX 7419.00 6092.88 1326.13 82.13 17.87
SYSTEM 12692.00 9893.19 2798.81 77.95 22.05
APPS_TS_INTERFACE 1100.00 788.25 311.75 71.66 28.34
APPS_TS_NOLOGGING 60.00 38.88 21.13 64.79 35.21
SYSAUX 1783.94 1111.94 672.00 62.33 37.67
APPS_TS_TX_DATA 4096.00 2308.75 1787.25 56.37 43.63
CTXD 40.00 20.88 19.13 52.19 47.81
APPS_TS_SEED 3000.00 1357.88 1642.13 45.26 54.74
APPS_TS_MEDIA 2592.00 1167.38 1424.63 45.04 54.96
OLAP 100.00 30.75 69.25 30.75 69.25
APPS_TS_QUEUES 1000.00 250.63 749.38 25.06 74.94
ODM 100.00 14.50 85.50 14.50 85.50
APPS_TS_ARCHIVE 1024.00 30.38 993.63 2.97 97.03
PORTAL 100.00 1.38 98.63 1.38 98.63
OWAPUB 10.00 .13 9.88 1.25 98.75
INTERIM 200.00 .13 199.88 .06 99.94
APPS_TS_TOOLS 500.00 .13 499.88 .03 99.98
-------------- -------------- --------------
TOTAL 38190.94 25343.63 12847.31
OBJECTS UNABLE TO EXTEND
OBJECTS WHICH ARE NEARING TO MAXEXTENTS
Dictionary Cache Hit Ratio : 99.54% Value Acceptable.
Library Cache Hit Ratio : 99.95% Value Acceptable.
DB Block Buffer Cache Hit Ratio : 98.67% Value Acceptable.
Latch Hit Ratio : 99.99% Value acceptable.
Disk Sort Ratio : 0.00% Value Acceptable.
Rollback Segment Waits : 0.01% Value acceptable.
Dispatcher Workload : 0.00% Value acceptable.
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Other Health Check Scripts for DBA and Apps DBA
No comments :
Post a Comment