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
댓글 없음:
댓글 쓰기