[본문스크랩] 오라클 모니터링 하기 위한 쿼리 2

1.Locks Holders and Requests

àDisplays blocking user session, the locked object, and the sessions requesting the object

select distinct sh.username||'('||sh.sid||')' "Lock Holder"

,ao.object_name "Object",

l.type,

decode(lmode,1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE','?') "LOCK MODE"

,SR.USERNAME||'('||SR.SID||')' "OBJECT REQUESTER"

from v$session sh, all_objects ao, v$lock l, v$session sr

where l.id1 = ao.object_id

and sh.sid = l.sid

and sh.lockwait is  null

and sr.lockwait is not null

and l.type='TM'

 order by 1,2,3

 

2.Memory Allocated Current Count

àReturns the amount of memory currently allocated to all sessions

select sum(value)

from v$statname n,v$sesstat s

where n.statistic#=s.statistic#

and name='session uga memory'

 

3.Memory Allocated Max count

àReturns the maximum amount of memory allocated to all sessions

select sum(value)

from v$statname n,v$sesstat s

where n.statistic#=s.statistic#

and name='session uga memory max'

 

4.MTS Circuit Report

àReturns information regarding the virtual circuits in the shared server

select  rawtohex(c.circuit) "Address",

d.name "Dispatcher",ss.name "Server",

s.sid,s.serial#,

s.status,c.queue, c.messages, c.bytes

from v$circuit c, v$dispatcher d, v$session s, v$shared_server ss

where c.dispatcher = d.paddr(+)

and c.server = ss.paddr(+)

and c.saddr = s.saddr(+)

order by c.circuit

 

5.MTS Dispatcher Report

àReturns information regarding the dispatcher process in the shared server

select name, status, accept, messages, bytes

owned, created, idle*100 "IDLE", busy*100 "BUSY",

round((busy/(idle + busy))*100) "Percent Busy"

from v$dispatcher

order by name

 

6.MTS Processes

àLists user, dispatcher, server and user process information for a multi-threaded environment

select sess.username,

sess.status,

cir.queue "Query Location",

dis.name "Disp Name",

dis.status "Disp status",

ss.name "Serv Name",

ss.status "Serv Status"

from v$circuit cir, v$session sess, v$dispatcher dis, v$shared_server ss

where sess.saddr =  cir.saddr

and cir.dispatcher = dis.paddr

and cir.server = ss.paddr(+)

 

7.MTS Queue Report

àAverage number of seconds that a shared server request waited in the queue

Select rawtohex(paddr) “Address”,

Type,queued,wait,totalq, round(decode(totalq,0,0,wait/(totalq * 0.1))) “Average wait”

From v$queue

Order by paddr

 

8.MTS Shared Server Report

àDisplay various information regarding the shared server processes.

select name,

rawtohex(paddr) "Paddr",

status,messages,bytes,messages/bytes "Average Bytes",

breaks, rawtohex(circuit) "Circuit", requests,

idle*.01 "Idle", busy*.01 "Idle",

round(busy/(idle+busy)*100) "Percent Busy"

from v$shared_server

order by name

 

9.Open Transaction Count

à Returns a count of open transactions [Use as a proxy for system overhead]

Select sum(xacts)

From v$rollstat

 

10.Parse Efficiency

àPercentage of time that SQL Statement were parsed only once

select round(sum(decode(name,'opened cursors cumulative',value,0))

/sum(decode(name,'parse count',value,0))*100,2)

from v$sysstat

 

11.Parse Report

àReturns various parse information

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

 

12.Physical Read Count

àTotal Number of physical reads

Select sum(value)

From v$sysstat

Where name=’physical read’

 

13.Process Report

àInformation about background and user process currently accessing the database

select p.pid, p.spid, p.username, s.username "Oracle User" ,

decode(s.terminal,null,p.terminal,s.terminal) Terminal,

decode(s.program,null,p.program,s.program) Program,

p.latchwait, s.lockwait,

decode(s.command,0,'None',nvl(a.name,'Unknown')) Command

from audit_actions a, v$process p ,v$session s

where s.paddr = p.addr

and a.action(+) = s.command

 

14.Recursive Calls Count

àTotal number of recursive calls(Oracle issued SQL Statements)

select value

from v$sysstat

where name='recursive calls'

 

15.Redo Log Allocation Latch Contention

àPercentage of time that a process attempted to acquire a redo log latch hold by another process

 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#

 

16.Redo Log Buffer Contention

àNumber of times a user process waited for redo log buffer space

select value

from v$sysstat

where name='redo log space waittime'

 

17.Redo Log Report – Assorted Information regarding the redo logs

select sum(decode(name,'redo blocks written',value,0)) "Block Writes",

sum(decode(name,'redo entried',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

 

18.Reload Efficiency

àPercentage of time that SQL Statements were reloaded

Select round((1-(sum(reloads)/sum(pins)))*100,2)

From v$librarycache

 

19.Rollback Segment Contention

àPercentage that a request for data  resulted in a wait for a rollback segment

Select round(sum(waits)/sum(gets),2)

From v$rollstat

 

20.Rollback Segment Current Report

àShows which rollback segments contain active transactions from which users

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