|
현재 Close 안된cursor가 ACCESS하고 있는 오브젝트의 정보를 얻고자 하는 경우
현재 사용되고 있는 오브젝트를 알고자 할 경우 |
select sid,
owner,
object,
type
from v$access
order by 1, 2, 3, 4; |
|
데이터 딕셔너리의 상세 현황을 보고자 할 경우 |
select cache#,
type,
subordinate#,
parameter,
count,
usage,
fixed,
gets,
getmisses "Get Misses",
scans,
scanmisses "Scan Misses",
scancompletes "Scan Completes",
modifications,
flushes
from v$rowcache; |
|
데이터 딕셔너리의 요약 현황을 보고자 할 경우 |
select sum(count) Count,
sum(usage) Usage,
sum(fixed) Fixed,
sum(gets) Gets,
sum(getmisses) "Get Misses",
sum(scans) Scans,
sum(scanmisses) "Scan Misses",
sum(scancompletes) "Scan Completes",
sum(modifications) Modifications,
sum(flushes) Flushes,
from v$rowcache;
select round(sum(gets)/(sum(gets)+sum(getmisses)) * 100,2)
from v$rowcache; |
|
DB block buffer에서 읽혀진 횟수를 보고자 할 경우 |
# Returns a count of gets in the db block buffer.
select sum(value)
from v$sysstat
where name in ('db block gets', 'consistent gets'); |
|
DB block buffer의 활용도를 보고자 하는 경우 |
select round((1-(pr.value/(bg.value+cg.value)))*100,2)
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'; |
|
DB block buffer의 전반적인 레포팅 |
column phys_read heading "Physical|Reads" format 99999999990
column block_get heading "Block|Gets" format 99999999990
column consi_get heading "Consistent|Gets" format 99999999990
column bchr heading "BCHR" format 999.90
select pr.value phys_read, bg.value block_get, cg.value consi_get,
(1 - ( pr.value/(bg.value+cg.value) ) ) * 100 bchr
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'; |
|
DB block 의 사용현황을 요약하고자 할 경우 |
select decode(state, 0, 'Free', 1, 'Read and Modified', 2, 'Read and Not Modified', 3, 'Currently Being Read', 'Other' ),count(*)
from x$bh
group by decode(state, 0, 'Free', 1, 'Read and Modified', 2, 'Read and Not Modified', 3, 'Currently Being Read', 'Other' ); |
|
디스크로부터 가장 많이 읽혀지는 sql문장을 알고 싶은 경우 |
select sql_text
from v$sqlarea, v$session
where address = sql_address
and username is not null
and disk_reads/executions =
(select max(disk_reads/executions)
from v$sqlarea, v$session
where address = sql_address
and username is not null
and executions > 0); |
|
버퍼에서 가장 많이 읽혀지는 sql문장을 알고 싶은 경우 |
select sql_text
from v$sqlarea, v$session
where address = sql_address
and username is not null
and buffer_gets/executions = (select max(buffer_gets/executions)
from v$sqlarea, v$session
where address = sql_address
and username is not null); |
|
EXTENT 현황을 알고 싶은 경우 |
select owner, segment_name, segment_type, count(*) numext,
round(sum(bytes)/1024/1024,1) MB
from sys.dba_extents
where owner not in ('SYS','SYSTEM')
group by segment_name, segment_type
order by segment_type, round(sum(bytes)/1024/1024,1) desc, segment_name; |
|
Extent 가 가장 많이 일어난 횟수 |
select max(extent_id) + 1
from sys.dba_extents
where owner not in ('SYS','SYSTEM'); |
|
Extent 가 가장 많이 일어난 세그먼트 |
select owner, segment_name
from sys.dba_extents
where owner not in ('SYS','SYSTEM')
and extent_id =
(select max(extent_id)
from sys.dba_extents
where owner not in ('SYS','SYSTEM')); |
|
데이터 파일별 Access 유형별 횟수 |
select name,
phyrds "Total Reads",
phywrts "Total Writes",
phyblkrd "Blocks Read",
phyblkwrt "Blocks Written"
from v$datafile d, v$filestat s
where d.file# = s.file#
order by d.file#; |
|
Free list wait 일어난 비율 : 낮을수록 좋음 |
select round((sum(decode(w.class, 'free list',count, 0))
/ (sum(decode(name,'db block gets', value, 0))
+ sum(decode(name,'consistent gets', value, 0))))
* 100,2)
from v$waitstat w, v$sysstat; |
|
SYSTEM 테이블스페이스 내 인덱스 생성현황 |
select count(*)
from sys.dba_indexes i
where i.tablespace_name = 'SYSTEM'
and i.owner not in ('SYS','SYSTEM'); |
|
Network 부하 (bytes) |
select sum(value)
from v$sysstat
where name like 'bytes%SQL*Net%'; |
|
데이터 파일로부터 physical I/O 횟수 |
select sum(phyrds) + sum(phywrts) "Total I/O"
from v$filestat; |
|
I/O 의 종합 현황 |
select sum(decode(name,'db block changes', value,0)) "Block Changes",
(sum(decode(name, 'db block gets', value,0))
+ sum(decode(name, 'consistent gets', value,0))) "Buffer Gets",
sum(decode(name,'physical reads', value, 0)) "Physical Reads",
(sum(decode(name, 'db block gets', value,0))
+ sum(decode(name, 'consistent gets', value,0)))
/ sum(decode(name,'physical reads', value, 0)) "Gets / Reads"
from v$sysstat; |
|
Latch로 인한 경합률 : 0에 가까울수록 좋음 |
select round(greatest(
(sum(decode(ln.name, 'cache buffers lru chain', misses,0))
/ greatest(sum(decode(ln.name, 'cache buffers lru chain', gets,0)),1)),
(sum(decode(ln.name, 'enqueues', misses,0))
/ greatest(sum(decode(ln.name, 'enqueues', gets,0)),1)),
(sum(decode(ln.name, 'redo allocation', misses,0))
/ greatest(sum(decode(ln.name, 'redo allocation', gets,0)),1)),
(sum(decode(ln.name, 'redo copy', misses,0))
/ greatest(sum(decode(ln.name, 'redo copy', gets,0)),1)))
* 100,2)
from v$latch l, v$latchname ln
where l.latch# = ln.latch#; |
|
Latch 상세 현황 |
select ln.name,
lh.pid,
l.immediate_gets,
l.immediate_misses,
l.gets,
l.misses,
l.sleeps
from v$latch l, v$latchholder lh, v$latchname ln
where l.latch# = ln.latch#
and l.addr = lh.laddr(+)
order by l.level#, l.latch#; |
|
Latch 효율성 평가 : 100에 가까울수록 좋음 |
select round(((sum(l.immediate_gets) + sum(l.misses) + sum(l.gets))
/ (sum(l.immediate_gets) + sum(l.immediate_misses) + sum(l.gets) + sum(l.misses))) * 100,2)
from v$latch l; |
|
Latch 종합 현황 |
select sum(l.immediate_gets),
sum(l.immediate_misses),
sum(l.gets),
sum(l.misses),
sum(l.sleeps)
from v$latch l, v$latchholder lh, v$latchname ln
where l.latch# = ln.latch#
and l.addr=lh.laddr(+); |
|
Library cache 효율성 : 100에 가까울수록 좋음 |
select round(sum(pinhits)/sum(pins) * 100,2)
from v$librarycache; |
|
Library cache 상세 현황 |
select namespace name,
gets,
gethits,
round(gethitratio*100,2) "GetHit Percentage",
pins,
pinhits,
round(pinhitratio*100,2) "PinHit Percentage",
reloads,
invalidations
from v$librarycache
order by 1; |
|
메모리 allocate 현황 |
select sum(value)
from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and name = 'session uga memory';
select sum(value)
from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and name = 'session uga memory max'; |
|
Open transaction 횟수 |
select sum(xacts) from v$rollstat; |
|
Parse 효율성 |
select round(sum(decode(name, 'opened cursors cumulative', value, 0))
/ sum(decode(name,'parse count', value,0)) * 100, 2)
from v$sysstat; |
|
Parse 현황 |
select ptc.value "Parse Time CPU",
pte.value "Parse Time Elapsed",
pc.value "Parse Count"
from v$sysstat ptc, v$sysstat pte, v$sysstat pc
where ptc.statistic#=96
and pte.statistic#=97
and pc.statistic#=98; |
|
Physical Read 횟수 |
select sum(value)
from v$sysstat
where name = 'physical reads'; |
|
Recursive call 횟수 |
select value from v$sysstat where name = 'recursive calls'; |
|
Redo Log latch 경합 |
select round(greatest(
(sum(decode(ln.name, 'redo copy', misses,0))
/ greatest(sum(decode(ln.name, 'redo copy', gets,0)),1)),
(sum(decode(ln.name, 'redo allocation', misses,0))
/ greatest(sum(decode(ln.name, 'redo allocation', gets,0)),1)),
(sum(decode(ln.name, 'redo copy', immediate_misses,0))
/ greatest(sum(decode(ln.name, 'redo copy', immediate_gets,0))
+ sum(decode(ln.name, 'redo copy', immediate_misses,0)),1)),
(sum(decode(ln.name, 'redo allocation', immediate_misses,0))
/ greatest(sum(decode(ln.name, 'redo allocation', immediate_gets,0))
+ sum(decode(ln.name, 'redo allocation', immediate_misses,0)),1)))
* 100,2)
from v$latch l, v$latchname ln
where l.latch# = ln.latch#;
|
|
리두로그 정보 |
select value from v$sysstat where name = 'redo log space waittime';
select sum(decode(name,'redo blocks written', value,0)) "Block Writes",
sum(decode(name,'redo entries', value, 0)) "Entries",
sum(decode(name,'redo size', value, 0)) "Size",
sum(decode(name,'redo log space requests', value, 0)) "Space Requests",
sum(decode(name,'redo synch writes', value,0)) "Synch Writes",
sum(decode(name,'redo writes', value,0)) "Writes"
from v$sysstat; |
|
Library Cache 활용도
|
select round((1 - (sum(reloads) / sum(pins))) * 100, 2)
from v$librarycache; |
|
롤백세그먼트 경합률 |
select round(sum(waits)/sum(gets),2) from v$rollstat; |
|
롤백세그먼트 현황 |
select n.usn,
n.name,
s.username Name,
s.osuser,
rs.extents,
rs.wraps,
rs.rssize "Size (Bytes)"
from v$rollname n, v$rollstat rs, v$session s, v$transaction t
where t.addr = s.taddr(+)
and rs.usn(+) = n.usn
and t.xidusn(+) = n.usn
and rs.status = 'ONLINE'
order by n.usn; |
|
SGA Free Space 현황 |
select sum(decode(name, 'free memory', bytes, 0))
from v$sgastat;
select round((sum(decode(name, 'free memory', bytes, 0))
/ sum(bytes)) * 100,0)
from v$sgastat; |
|
SGA Size |
select sum(value) from v$sga; |
|
Shared Pool의 reload 횟수 및 비율 |
select sum(reloads)
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE', 'BODY','TRIGGER');
select round(sum(reloads)
/ sum(pins) * 100,2)
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER'); |
|
Sort_Area 효율성 |
select round((sum(decode(name, 'sorts (memory)', value, 0))
/ (sum(decode(name, 'sorts (memory)', value, 0))
+ sum(decode(name, 'sorts (disk)', value, 0))))
* 100,2)
from v$sysstat; |
|
소트 현황 |
select username Name,
osuser,
sd.value "Disk Sorts",
sm.value "Memory Sorts",
sr.value "Rows Sorted"
from v$session s, v$sesstat sd, v$sesstat sm, v$sesstat sr
where s.sid = sd.sid
and s.sid = sm.sid
and s.sid = sr.sid
and sd.statistic# = 101
and sm.statistic# =100
and sr.statistic# =102
and s.type != 'BACKGROUND'; |
|
소트 건수 |
select sum(value) from v$sysstat where statistic#=102); |
|
SQL AREA 활용현황 |
select username,
sql_text,
sorts,
disk_reads Reads,
buffer_gets Gets
from v$sqlarea s,
sys.dba_users u
where s.parsing_user_id = u.user_id
and users_executing > 0
order by 1; |
|
V$SYSSTAT를 이용한 테이블 스캔현황 |
select value
from v$sysstat
where name = 'table scans (long tables)';
select value
from v$sysstat
where name = 'table scans (short tables)';
select s.value + l.value
from v$sysstat s, v$sysstat l
where s.name = 'table scans (short tables)'
and l.name = 'table scans (long tables)';
|
|
SYSTEM 테이블스페이스에 생성된 테이블 수 |
select count(*)
from sys.dba_tables t
where t.tablespace_name = 'SYSTEM'
and t.owner not in ('SYS','SYSTEM'); |
|
Next Extent가 Free 영역보다 큰 경우 |
select s.segment_name "Segment Name",
s.tablespace_name "Tablespace Name",
s.next_extent "Next Extent",
f.free_bytes "Free Bytes"
from dba_segments s,
(select tablespace_name,
sum(bytes) free_bytes
from dba_free_space
group by tablespace_name) f
where f.tablespace_name = s.tablespace_name
and s.next_extent > f.free_bytes; |
|
테이블 스페이스 FREE SPACE 현황 |
select tablespace_name Name,
sum(bytes) Bytes,
sum(blocks) Blocks,
count(*) “Number of Files”
from sys.dba_free_space
group by tablespace_name; |
|
테이블스페이스 조각
(Fragmentation) 현황
|
select f.tablespace_name "Tablespace Name",
file_name "File Name",
block_id, "Block Id"
f.blocks "Number of Blocks",
f.bytes "Number of Bytes"
from dba_free_space f, dba_data_files d
where f.file_id = d.file_id
order by f.tablespace_name ASC, file_name ASC, f.blocks DESC;
[출처] [펌] [오라클]디비 모니터링 쿼리|작성자 slash |