SET FEEDBACK OFF LINES 200
Col X noprint
Col NAMESPACE Format A20 Heading "NAMESPACE"
Col GETHITRATIO Format A11 Heading "GETHITRATIO"
Col RP Format A12 Heading "RELOADS/PINS"
Col PINS Format 999G999G999 Heading "PINS"
Col RELOADS Format 999G999 Heading "RELOADS"
Col RCM Format A15 Heading "ROWCACHE MISSES"
Col RCSM Format A20 Heading "ROWCACHE SCAN MISSES"
Col FLUSHES Format A7 Heading "FLUSHES"
Col NAME Format A7 Heading "Buffer|Pool"
Col PHYSICAL_WRITES Format 99G999G999 Heading "Physical|Writes"
Col DB_BLOCK_CHANGE Format 999G999G999 Heading "Buffers|Changed"
Col PHYSICAL_READS Format 999G999G999G999G999 Heading "Physical|Reads"
Col LOGICAL_READS Format 9G999G999G999G999G999 Heading "Logical|Reads"
Col DB_BLOCK_GETS Format 999G999G999 Heading "Buffer|Gets"
Col CONSISTENT_GETS Format 9g999G999G999 Heading "Consistent|Gets"
Col RATIO Format A18 Heading "Buffer|Hit|Ratio" just r
Col WRITE_COMPLETE_WAIT Format 9G999G999 Heading "Write|Complete|Waits"
Col FREE_BUFFER_WAIT Format 9G999G999 Heading "Free|Buffer|Waits"
Col BUFFER_BUSY_WAIT Format 9G999G999 Heading "Buffer|Busy|Waits"
Col DB_HIT Format A24 Heading "Db Cache|Hit Ratio"
Col ENTRIES Format 999G999G999 Heading "Entries"
Col REQUESTS Format 9G999 Heading "Requests"
Col REDO_HIT Format A13 Heading "Redo Log|Hit Ratio" just r
Col MV Format 99G999G999 Heading "Sorts|In Memory"
Col DV Format 999999 Heading "Sorts|In Disk"
Col SORT_HIT Format 990D00 Heading "Sort|Hit Ratio"
Col TYPE Format A17 Heading "Process|Queue"
Col QTOTAL Format 99g999G999G999 Heading "Enqueued|Packets"
Col QWAIT Format 99g999G999G999 Heading "Wait|Time(ms)"
Col AVGRES Format 9990D000 Heading "Agv Wait|Time(ms)"
Col QIDLE Format 99g999G999G999 Heading "Idle|Time(s)"
Col QBUSY Format 99g999G999G999 Heading "Busy|Time(s)"
Col PCT_BUSY Format 9990D00 Heading "Pct|Busy"
Col QTD_PRC Format 9999 Heading "Running|Processes"
Col MC Format 9999 Heading "Max|Circuits"
Col MS Format 9999 Heading "Max|S.Sessions"
Col SH Format 9999 Heading "Max|S.Servers"
Col MSS Format 9999 Heading "Limite|S.Servers"
Col SS Format 9999 Heading "Started|S.Servers"
Col ST Format 9999 Heading "Terminated|S.Servers"
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
col st1 format a14 heading "Startup Time"
col st2 format a14 heading "System Date"
col st3 format a12 heading "Running Time"
col st4 format a12 heading "Running Secs"
SELECT
to_char( startup_time, 'dd/mm/yy hh24"h"mi' ) st1,
to_char( sysdate, 'dd/mm/yy hh24"h"mi' ) st2,
lpad( to_char( trunc(sysdate,'YEAR') + (sysdate-startup_time-1),
decode( trunc( sysdate-startup_time, 0 ), 0, 'fm" 0d "hh24"h"mi', 'fm""dd"d "hh24"h"mi' ) ), 12, ' ' ) st3,
to_char( (sysdate-startup_time)*24*60*60, '999g999g990' ) st4
FROM v$instance;
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
PROMPT SHARED POOL
PROMPT Referência: Library Cache Hit Ratio >= 90% | Reloads/Pins < 1%
PROMPT Referência: RowCache Miss < 15%
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
SELECT 1 X, NAMESPACE, TO_CHAR(GETHITRATIO*100, '999990.00')||'%' GETHITRATIO,
TO_CHAR( DECODE(PINS, 0, DECODE(RELOADS,0,0,1), RELOADS/PINS)*100, '99990.0000')||'%' RP,
RELOADS, PINS, INVALIDATIONS
FROM V$LIBRARYCACHE
UNION ALL
SELECT 3, 'GERAL', TO_CHAR(AVG(GETHITRATIO*100), '999990.00')||'%' GETHITRATIO,
TO_CHAR( AVG(DECODE(PINS, 0, DECODE(RELOADS,0,0,1), RELOADS/PINS)*100), '99990.0000')||'%' "RELOADS/PINS",
SUM(RELOADS), SUM(PINS), SUM(INVALIDATIONS)
FROM V$LIBRARYCACHE
GROUP BY 'GERAL'
ORDER BY X, GETHITRATIO DESC;
SELECT TO_CHAR( SUM(GETMISSES)/DECODE(SUM(GETS),0,1,SUM(GETS))*100, '9999999990.00')||'%' RCM,
TO_CHAR( SUM(SCANMISSES)/DECODE(SUM(SCANS),0,1,SUM(SCANS))*100, '999999999999990.00')||'%' RCSM,
TO_CHAR( COUNT(FLUSHES), '999990' ) FLUSHES
FROM V$ROWCACHE;
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
PROMPT DATABASE BUFFER CACHE
PROMPT Referência: DbCache Hit Ratio > 90%
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
SELECT FIS.VALUE PHYSICAL_READS, (CUR.VALUE+CON.VALUE) LOGICAL_READS,
TO_CHAR( (1-(FIS.VALUE/(CUR.VALUE+CON.VALUE)))*100, '990.00')||'%' RATIO
FROM V$SYSSTAT FIS, V$SYSSTAT CON, V$SYSSTAT CUR
WHERE FIS.NAME = 'physical reads'
AND CON.NAME = 'consistent gets'
AND CUR.NAME = 'db block gets';
SELECT NAME, PHYSICAL_WRITES, DB_BLOCK_CHANGE, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS
,LPAD(ROUND((1-(PHYSICAL_READS/DECODE(DB_BLOCK_GETS+CONSISTENT_GETS,0,1,DB_BLOCK_GETS+CONSISTENT_GETS)))*100, 2), 7, ' ' )||'%' RATIO
,WRITE_COMPLETE_WAIT, FREE_BUFFER_WAIT, BUFFER_BUSY_WAIT
FROM V$BUFFER_POOL_STATISTICS
UNION ALL
SELECT 'TOTAL', SUM(PHYSICAL_WRITES), SUM(DB_BLOCK_CHANGE), SUM(PHYSICAL_READS),
SUM(DB_BLOCK_GETS), SUM(CONSISTENT_GETS)
,LPAD(ROUND(AVG(1-(PHYSICAL_READS/DECODE(DB_BLOCK_GETS+CONSISTENT_GETS,0,1,DB_BLOCK_GETS+CONSISTENT_GETS)))*100, 2), 7, ' ' )||'%'
,SUM(WRITE_COMPLETE_WAIT), SUM(FREE_BUFFER_WAIT), SUM(BUFFER_BUSY_WAIT)
FROM V$BUFFER_POOL_STATISTICS;
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
PROMPT REDO LOG BUFFER
PROMPT Referência: Redo Hit Ratio > 99%
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
SELECT ENT.VALUE ENTRIES, REQ.VALUE REQUESTS,
TO_CHAR( (1-(REQ.VALUE/(REQ.VALUE+ENT.VALUE)))*100, '99999990.00' )||'%' REDO_HIT
FROM V$SYSSTAT ENT, V$SYSSTAT REQ
WHERE REQ.NAME = 'redo log space requests'
AND ENT.NAME = 'redo entries';
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
PROMPT SORT PERFORMANCE
PROMPT Referência: Sorts in Memory > 95%
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
SELECT mem.VALUE MV, dsk.VALUE DV, ROUND((1-(dsk.VALUE/(mem.VALUE+dsk.VALUE)))*100,2) SORT_HIT
FROM V$SYSSTAT mem, V$SYSSTAT dsk
WHERE mem.NAME = 'sorts (memory)'
and dsk.NAME = 'sorts (disk)'
/
SET TERMOUT OFF
Col ttime NEW_VALUE ttime
SELECT to_char( sysdate, 'dd/mm hh24:mi:ss' ) ttime FROM dual;
SET TERMOUT ON
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
PROMPT SHARED SERVER MODEL (&ttime.)
PROMPT Referência: AvgWaitTime 0,10ms ~ 0,30ms | BusyTime < 50%
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
SELECT
PS.TYPE TYPE
,QS.QTOTAL QTOTAL
,QS.QWAIT QWAIT
,ROUND( DECODE(QS.QWAIT, 0, 0, QS.QWAIT/QS.QTOTAL ), 2 ) AVGRES
,PS.IDLE QIDLE
, PS.BUSY QBUSY
, ROUND( PS.BUSY/(PS.BUSY+PS.IDLE) * 100, 2 ) PCT_BUSY
, PS.PROCESS QTD_PRC
FROM
(
SELECT
'SHARED SERVER' TYPE
,SUM(Q.TOTALQ) QTOTAL
,SUM(Q.WAIT)*10 QWAIT
FROM V$QUEUE Q
WHERE Q.TYPE = 'COMMON'
UNION ALL
SELECT
DECODE( D.CONF_INDX, 0, 'DISPATCHER 1521', 1, 'DISPATCHER 1523', 2, 'DISPATCHER 1525', 'DISPATCHER OTHER' ) TYPE
,SUM(Q.TOTALQ) QTOTAL
,SUM(Q.WAIT)*10 QWAIT
FROM V$QUEUE Q, V$DISPATCHER D
WHERE Q.TYPE = 'DISPATCHER'
AND Q.PADDR = D.PADDR
AND D.ACCEPT = 'YES'
GROUP BY DECODE( D.CONF_INDX, 0, 'DISPATCHER 1521', 1, 'DISPATCHER 1523', 2, 'DISPATCHER 1525', 'DISPATCHER OTHER' )
) QS,
(
SELECT 'DISPATCHER 1521' TYPE, TRUNC(SUM(BUSY)/100) BUSY, TRUNC(SUM(IDLE)/100) IDLE, COUNT(*) PROCESS
FROM V$DISPATCHER WHERE CONF_INDX = 0 AND ACCEPT = 'YES'
UNION ALL
SELECT 'DISPATCHER 1523' TYPE, TRUNC(SUM(BUSY)/100) BUSY, TRUNC(SUM(IDLE)/100) IDLE, COUNT(*) PROCESS
FROM V$DISPATCHER WHERE CONF_INDX = 1 AND ACCEPT = 'YES'
UNION ALL
SELECT 'DISPATCHER 1525' TYPE, TRUNC(SUM(BUSY)/100) BUSY, TRUNC(SUM(IDLE)/100) IDLE, COUNT(*) PROCESS
FROM V$DISPATCHER WHERE CONF_INDX = 2 AND ACCEPT = 'YES'
UNION ALL
SELECT 'DISPATCHER OTHER' TYPE, TRUNC(SUM(BUSY)/100) BUSY, TRUNC(SUM(IDLE)/100) IDLE, COUNT(*) PROCESS
FROM V$DISPATCHER WHERE CONF_INDX > 2 AND ACCEPT = 'YES'
UNION ALL
SELECT 'SHARED SERVER', TRUNC(SUM(BUSY)/100), TRUNC(SUM(IDLE)/100), COUNT(*) PROCESS
FROM V$SHARED_SERVER WHERE STATUS <> 'QUIT'
) PS
WHERE QS.TYPE = PS.TYPE
/
SELECT
maximum_connections mc
,maximum_sessions ms
,servers_highwater sh
,to_number((SELECT value FROM v$parameter WHERE name = 'mts_max_servers' )) mss
,servers_started ss
,servers_terminated st
FROM V$MTS
PROMPT
PROMPT -------------------------------------------------------------------------------------------------------------------------------------
PROMPT
SET FEEDBACK ON
UNDEFINE ttime
COL NAME CLEAR
