Use this script to get a fast overview of the state of a troubled system
spool /tmp/snap;
prompt****************************************************
prompt Hit Ratio Section
prompt****************************************************
prompt
prompt =========================
prompt BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
--(sum(decode(name,'db block gets',value,0))+
--(sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT
RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > .1, i.e., more than 1% of the pins
prompt resulted in reloads, then increase the shared_pool_size in init.ora)
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
prompt
prompt
prompt =========================
prompt REDO LOG BUFFER
prompt =========================
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),
value
from v$sysstat where name = 'redo log space requests';
set heading on
prompt
prompt
prompt
column bytes format 999,999,999
select name, bytes from v$sgastat where name = 'free memory';
prompt
prompt****************************************************
prompt SQL Summary Section
prompt****************************************************
prompt
column "Tot SQL run since startup" format 999,999,999
column "SQL executing now" format 999,999,999
select sum(executions) "Tot SQL run since startup",
sum(users_executing) "SQL executing now"
from v$sqlarea;
prompt
prompt
prompt****************************************************
prompt Lock Section
prompt****************************************************
prompt
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
prompt =========================
prompt
select substr(username,1,12) "User",
substr(lock_type,1,18) "Lock Type",
substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers or other DDL
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,15) "Name",
substr(a.type,1,20) "Type",
substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt =========================
prompt DML LOCKS - These are table and row locks...
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,20) "Name",
substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt
prompt****************************************************
prompt Latch Section
prompt****************************************************
prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
prompt
column "miss_ratio" format .99
column "immediate_miss_ratio" format .99
select substr(l.name,1,30) name,
(misses/(gets+.001))*100 "miss_ratio",
(immediate_misses/(immediate_gets+.001))*100
"immediate_miss_ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
and (
(misses/(gets+.001))*100 > .2
or
(immediate_misses/(immediate_gets+.001))*100 > .2
)
order by l.name;
prompt
prompt
prompt****************************************************
prompt Rollback Segment Section
prompt****************************************************
prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
--column count format 999,999,999
select class, count
from v$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block')
group by class,count;
column "Tot # of Requests for Data" format 999,999,999
select sum(value) "Tot # of Requests for Data" from v$sys stat where
name in ('db block gets', 'consistent gets');
prompt
prompt =========================
prompt ROLLBACK SEGMENT CONTENTION
prompt =========================
prompt
prompt If any ratio is > .01 then more rollback segments are needed
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
prompt
prompt
set feedback on;
prompt****************************************************
prompt Session Event Section
prompt****************************************************
prompt if average-wait > 0 then contention exists
prompt
select substr(event,1,30) event,
total_waits, total_timeouts, average_wait
from v$session_event
where average_wait > 0 ;
--or total_timeouts > 0;
prompt
prompt
prompt****************************************************
prompt Queue Section
prompt****************************************************
prompt average wait for queues should be near zero ...
prompt
column "totalq" format 999,999,999
column "# queued" format 999,999,999
select paddr, type "Queue type", queued "# queued", wait, totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;
set feedback on;
prompt
prompt
--prompt****************************************************
--prompt Multi-threaded Server Section
--prompt****************************************************
--prompt
--prompt If the following number is > 1
--prompt then increase MTS_MAX_SERVERS parm in init.ora
--prompt
-- select decode( totalq, 0, 'No Requests',
-- wait/totalq || ' hundredths of seconds')
-- "Avg wait per request queue"
-- from v$queue
-- where type = 'COMMON';
--prompt
--prompt If the following number increases, consider adding dispatcher processes
--prompt
-- select decode( sum(totalq), 0, 'No Responses',
-- sum(wait)/sum(totalq) || ' hundredths of seconds')
-- "Avg wait per response queue"
-- from v$queue q, v$dispatcher d
-- where q.type = 'DISPATCHER'
-- and q.paddr = d.paddr;
--set feedback off;
--prompt
--prompt
--prompt =========================
--prompt DISPATCHER USAGE
--prompt =========================
--prompt (If Time Busy > 50, then change
MTS_MAX_DISPATCHERS in init.ora)
--column "Time Busy" format 999,999.999
--column busy format 999,999,999
--column idle format 999,999,999
--select name, status, idle, busy,
-- (busy/(busy+idle))*100 "Time Busy"
--from v$dispatcher;
--prompt
--prompt
--select count(*) "Shared Server Processes"
-- from v$shared_server
-- where status = 'QUIT';
--prompt
--prompt
--prompt high-water mark for the multi-threaded server
--prompt
--select * from v$mts;
--prompt
--prompt****************************************************
--prompt file i/o should be evenly distributed across drives.
--prompt
--select
--substr(a.file#,1,2) "#",
--substr(a.name,1,30) "Name",
--a.status,
--a.bytes,
--b.phyrds,
--b.phywrts
--from v$datafile a, v$filestat b
--where a.file# = b.file#;
--select substr(name,1,55) system_statistic, value
-- from v$sysstat
-- order by name;
spool off;
prompt****************************************************
prompt Hit Ratio Section
prompt****************************************************
prompt
prompt =========================
prompt BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
--(sum(decode(name,'db block gets',value,0))+
--(sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT
RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > .1, i.e., more than 1% of the pins
prompt resulted in reloads, then increase the shared_pool_size in init.ora)
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
prompt
prompt
prompt =========================
prompt REDO LOG BUFFER
prompt =========================
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),
value
from v$sysstat where name = 'redo log space requests';
set heading on
prompt
prompt
prompt
column bytes format 999,999,999
select name, bytes from v$sgastat where name = 'free memory';
prompt
prompt****************************************************
prompt SQL Summary Section
prompt****************************************************
prompt
column "Tot SQL run since startup" format 999,999,999
column "SQL executing now" format 999,999,999
select sum(executions) "Tot SQL run since startup",
sum(users_executing) "SQL executing now"
from v$sqlarea;
prompt
prompt
prompt****************************************************
prompt Lock Section
prompt****************************************************
prompt
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
prompt =========================
prompt
select substr(username,1,12) "User",
substr(lock_type,1,18) "Lock Type",
substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers or other DDL
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,15) "Name",
substr(a.type,1,20) "Type",
substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt =========================
prompt DML LOCKS - These are table and row locks...
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,20) "Name",
substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt
prompt****************************************************
prompt Latch Section
prompt****************************************************
prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
prompt
column "miss_ratio" format .99
column "immediate_miss_ratio" format .99
select substr(l.name,1,30) name,
(misses/(gets+.001))*100 "miss_ratio",
(immediate_misses/(immediate_gets+.001))*100
"immediate_miss_ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
and (
(misses/(gets+.001))*100 > .2
or
(immediate_misses/(immediate_gets+.001))*100 > .2
)
order by l.name;
prompt
prompt
prompt****************************************************
prompt Rollback Segment Section
prompt****************************************************
prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
--column count format 999,999,999
select class, count
from v$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block')
group by class,count;
column "Tot # of Requests for Data" format 999,999,999
select sum(value) "Tot # of Requests for Data" from v$sys stat where
name in ('db block gets', 'consistent gets');
prompt
prompt =========================
prompt ROLLBACK SEGMENT CONTENTION
prompt =========================
prompt
prompt If any ratio is > .01 then more rollback segments are needed
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
prompt
prompt
set feedback on;
prompt****************************************************
prompt Session Event Section
prompt****************************************************
prompt if average-wait > 0 then contention exists
prompt
select substr(event,1,30) event,
total_waits, total_timeouts, average_wait
from v$session_event
where average_wait > 0 ;
--or total_timeouts > 0;
prompt
prompt
prompt****************************************************
prompt Queue Section
prompt****************************************************
prompt average wait for queues should be near zero ...
prompt
column "totalq" format 999,999,999
column "# queued" format 999,999,999
select paddr, type "Queue type", queued "# queued", wait, totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;
set feedback on;
prompt
prompt
--prompt****************************************************
--prompt Multi-threaded Server Section
--prompt****************************************************
--prompt
--prompt If the following number is > 1
--prompt then increase MTS_MAX_SERVERS parm in init.ora
--prompt
-- select decode( totalq, 0, 'No Requests',
-- wait/totalq || ' hundredths of seconds')
-- "Avg wait per request queue"
-- from v$queue
-- where type = 'COMMON';
--prompt
--prompt If the following number increases, consider adding dispatcher processes
--prompt
-- select decode( sum(totalq), 0, 'No Responses',
-- sum(wait)/sum(totalq) || ' hundredths of seconds')
-- "Avg wait per response queue"
-- from v$queue q, v$dispatcher d
-- where q.type = 'DISPATCHER'
-- and q.paddr = d.paddr;
--set feedback off;
--prompt
--prompt
--prompt =========================
--prompt DISPATCHER USAGE
--prompt =========================
--prompt (If Time Busy > 50, then change
MTS_MAX_DISPATCHERS in init.ora)
--column "Time Busy" format 999,999.999
--column busy format 999,999,999
--column idle format 999,999,999
--select name, status, idle, busy,
-- (busy/(busy+idle))*100 "Time Busy"
--from v$dispatcher;
--prompt
--prompt
--select count(*) "Shared Server Processes"
-- from v$shared_server
-- where status = 'QUIT';
--prompt
--prompt
--prompt high-water mark for the multi-threaded server
--prompt
--select * from v$mts;
--prompt
--prompt****************************************************
--prompt file i/o should be evenly distributed across drives.
--prompt
--select
--substr(a.file#,1,2) "#",
--substr(a.name,1,30) "Name",
--a.status,
--a.bytes,
--b.phyrds,
--b.phywrts
--from v$datafile a, v$filestat b
--where a.file# = b.file#;
--select substr(name,1,55) system_statistic, value
-- from v$sysstat
-- order by name;
spool off;
No comments:
Post a Comment