set veri off
set echo off
set feedback off
set pagesize 100
set linesize 150
clear columns;
column output_name new_value output_name noprint;
select ''||host_name||'_'||instance_name||'_monitoring_'||to_char(sysdate, 'YYYYMMDD')||'_ORIGINAL.html' output_name
from v$instance;
/* instance Infomation */
set markup html on spool on
spool &output_name
prompt Oracle Instance Information
prompt Purpose - 대상 데이터베이스 인스턴스의 기본정보를 확인한다.
set pages 40
set line 132
col host format a20
select name "DB Name", instance_number "Inst ID", instance_name "Inst Name",
host_name "Host", version "Version", sysdate "점검일",
parallel "Parallel", log_mode "Log Mode", archiver "Archiver"
from v$instance, v$database
/
prompt Current Session Count
prompt Purpose - 대상 데이타베이스에 접속되어 있는 모든 Session과 현재 작업중인 Session의 수를 확인한다.
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
count(*) "Total Sessions",
count(decode(status, 'ACTIVE',1) ) "Active Sessions"
from v$session
/
prompt BUFFER CACHE TUNING
select 1 - (phy.value - lob.value - dir.value)
/ ses.value "CACHE HIT RATIO"
from v$sysstat ses, v$sysstat lob,
v$sysstat dir, v$sysstat phy
where ses.name = 'session logical reads'
and dir.name = 'physical reads direct'
and lob.name = 'physical reads direct (lob)'
and phy.name = 'physical reads';
prompt LIBRARY CACHE TUNING
select to_char(trunc(sum(reloads)/sum(pins)*100,5) , 99.99999 ) ||
'% (less than 1%)' "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt DICTIONARY CACHE TUNING
select trunc(sum(getmisses)/sum(gets)*100,5)||'% (LESS THAN 9.8%)'
"DATA DICTIONARY MISS RATIO "
from v$rowcache;
prompt Shared Pool의 사용 상태
PROMPT * Shared Pool Free Memory - Percentage of free space in the SGA shared pool area.
select round((sum(decode(name, 'free memory', bytes, 0)) / sum(bytes)) * 100,2)
from v$sgastat;
set serveroutput on
col messes format 999,999
col gets format 999,999,999
prompt INTERNAL SORT AND EXTERNAL SORT
COLUMN name FORMAT A20
SELECT name, value
FROM V$SYSSTAT
WHERE name IN ('sorts (memory)', 'sorts (disk)');
prompt Sum of max(peak) pga, uga session memory
prompt
select 'Max(peak) PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,
sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';
show parameter pga
prompt DATA FILES PHYSICAL READ, PHYSICAL WRITE NUMBER
COLUMN tablespace_name FORMAT A20
COLUMN name FORMAT A30
SELECT tablespace_name, name, phyrds, phywrts
FROM V$DATAFILE df, V$FILESTAT fs, dba_data_files fl
WHERE df.file# = fs.file#
AND df.file# = fl.file_id;
prompt Tablespace extent or segment management
SELECT tablespace_name, extent_management, allocation_type, segment_space_management
FROM dba_tablespaces;
prompt REDO LOG FILES WAIT RATIO
select name, value
from v$sysstat
where name in ('redo buffer allocation retries', 'redo entries');
select r.value "Retried", e.value "Entries",
r.value / e.value * 100 "Percentage"
from v$sysstat r, v$sysstat e
where r.name = 'redo buffer allocation retries'
and e.name = 'redo entries';
prompt Wait Event
col event format a20
select event, total_timeouts, average_wait from v$system_event where rownum < 6 order by 2;
select /*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */
s.sid SID, s.username, s.program, p.spid "OS-Pid",w.seconds_in_wait as "W_time(Sec)",
decode(w.wait_time,0,'Wai-ting', 'Waited') Status, w.ename event,
-- p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','||
-- p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",
q.sql_text
from ( select a.*, decode(a.event,'latch free', 'latch free (' ||b.name||')',
'row cache lock', 'row cache lock (' || c.parameter || ')',
'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||
chr(bitand(p1,16711680)/65535)||':'||
decode(bitand(p1,65535), 1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',
a.event ) ename
from v$session_wait a, v$latchname b, v$rowcache c
where a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = 'PARENT'
and a.event not in ('rdbms ipc message','smon timer','pmon timer','slave wait','pipe get','null event',
'SQL*Net message from client', 'SQL*Net message to client','PX Idle Wait',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */
'lock manager wait for remote message', 'single-task message')
) w, v$session s, v$process p, v$sql q
where w.sid = s.sid and s.paddr = p.addr
and s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+)
order by w.ename;
prompt Invalid Object Count
prompt Purpose - 오브젝트의 유형별로 Invalid상태인 오브젝트의 수를 점검한다.
select /*+ ORDERED_PREDICATES */
object_type "Object Type",
count(*) "Invalid Count"
from dba_objects
where status = 'INVALID'
group by object_type
/
prompt Resource Limit
prompt Purpose - 데이타베이스의 시스템리소스의 할당내역과 소모현황을 확인한다.
select RESOURCE_NAME "Resource Name",
CURRENT_UTILIZATION "Current Util",
MAX_UTILIZATION "Max Util",
INITIAL_ALLOCATION "Initial Alloc",
LIMIT_VALUE "Limit"
from v$resource_limit
/
prompt Redo Buffer Contention
prompt Purpose - Redo Log Buffer에 대한 경합상태를 확인한다.
select round(sum(decode(name, 'redo buffer allocation retries', value))/sum(decode(name, 'redo entries', value)),5)
"Redo Buffer Space Wait(%%)",
sum(decode(name, 'redo log space requests', value)) "Redo Space Reqeusts"
from v$sysstat
/
prompt Redo Log Switch Time Interval
prompt Purpose - Redo Log Switch의 Time Interval을 확인한다.
select /*+ use_hash(a b) */ a.thread#, to_char(a.first_time,'YYYY/MM/DD HH24') "Time",
round(avg(trunc((a.first_time - b.first_time)*24*60,3)),2) "Interval(Min)"
from
(select thread#, sequence#, first_time from v$loghist where first_time > sysdate -3) a,
(select thread#, sequence#, first_time from v$loghist where first_time > sysdate -3) b
where a.thread# = b.thread#
and a.sequence# = b.sequence# + 1
group by a.thread#, to_char(a.first_time,'YYYY/MM/DD HH24')
/
prompt Objects With Too Many Extents
prompt Purpose - 데이타베이스 내에 Extent가 많이 발생한 Object을 확인한다.
col owner format a10
col "Segment" format a30
col "Tablespace" format a20
select owner,
segment_name "Segment",
segment_type "Type",
tablespace_name "Tablespace",
round(bytes/1024/1024,2) "Size(MB)",
extents "Extents",
round(initial_extent/1024/1024,2) "Initial(MB)",
round(next_extent/1024/1024,2) "Next(MB)"
from dba_segments
where owner != 'SYS'
and extents > 50
order by extents desc
/
prompt Tablespace Usage and Fragmentation
prompt Purpose - 테이블 스페이스의 사용현황과 Fragmentation을 점검한다
select df.tablespace_name "Tablespace",
round(df.TBS_byte /1048576,2) "Total(MB)",
round((df.TBS_byte - fs.Free_byte)/1048576,2) "Used(MB)",
round(fs.Free_byte /1048576,2) "Free(MB)",
round((fs.Free_byte/df.TBS_byte) *100,0) "Free(%%)",
fs.pieces "Pieces",
round(fs.Max_free /1048576,2) "MaxFree(MB)"
from ( select tablespace_name, sum(bytes) TBS_byte
from dba_data_files group by tablespace_name ) df,
( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces
from dba_free_space group by tablespace_name ) fs,
( select tablespace_name, initial_extent, next_extent
from dba_tablespaces ) db
where df.tablespace_name = db.tablespace_name
and df.tablespace_name = fs.tablespace_name(+)
order by 5
/
prompt Rollback Segment Hit Ratio
prompt Purpose - Rollback Segment의 Hit Ratio를 확인한다
select name "Rollback Segment",
rssize "Rsize", writes "Writes", xacts "Xacts", status "Status",
waits "Waits", gets "Gets",
decode(gets,0,-1, trunc((1-(waits/gets))*100, 5)) "Hit ratio"
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
order by waits/gets desc
/
prompt SQLs by cached time
prompt Purpose - 캐쉬기간에 따른 SQL 갯수와 평균 실행 횟수를 파악한다.
select substr(cache_time,3) "Cached time", count(*) "SQL Count", round(avg(exec)) "Avg Exec Count"
from
( select decode(sign(1-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'1 00 ~ 01 (min)',
decode(sign(5-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'2 01 ~ 05 (min)',
decode(sign(10-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'3 05 ~ 10 (min)',
decode(sign(30-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'4 10 ~ 30 (min)',
decode(sign(60-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'5 30 ~ 60 (min)',
decode(sign(60*2-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'6 01 ~ 02 (hour)',
decode(sign(60*12-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'7 02 ~ 12 (hour)',
decode(sign(60*24-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'8 12 ~ 24 (hour)','9 24 ~ (hour)')))))))) cache_time
, executions exec
from v$sql
where parsing_user_id > 0 and child_number = 0
)
group by cache_time
/
prompt Chained Row Ratio
prompt Purpose - Chained Row를 Access한 비율을 확인한다.
SELECT sum(decode(name,'table fetch continued row',value,0)) "Chained row Access",
sum(decode(name,'table fetch by rowid',value,0)) "Index Access",
sum(decode(name,'table scan rows gotten',value,0)) "Table Scan Access",
round(sum(decode(name,'table fetch continued row',value,0)) /
(sum(decode(name,'table fetch by rowid',value,0)) + sum(decode(name,'table scan rows gotten',value,0))) * 100,5) "Ratio"
FROM v$sysstat
/
prompt Costomer Confirmation
col Company format a20
col Department format a20
col Respon. format a10
col Sign format a10
select null "Company", null "Department", null "Respon.", null "Name", null "Sign" from dual;
spool off
exit
