RAC Diagnostics Script

G \|.}6O5[f
O#?B0

from metalink:

This script is broken up into different SQL statements that can be used ITPUB个人空间'YKZ,L0x-K+qA@
individually.  Each SQL statement adds information to help in debugging an ITPUB个人空间
Ik2fvh:u4`
RAC hang/severe performance scenerio.  ITPUB个人空间T"A aj_#[$Y^
ITPUB个人空间*Si;?y iAJC
Script
9b0dP5@dvl0-------ITPUB个人空间g#w
}1R T^
hz
Q

P&v8Xadi)UA0 - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -ITPUB个人空间3X"Y?`mY&D(?

$U{k(tnd0-- NAME:  RACDIAG.SQL 
"o`[p5[S6c5`6{K!ak0--    SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON
$Y/q+CQ"x-V5M0-- ------------------------------------------------------------------------ 
3@Ox?y}?rZ8G0-- AUTHOR:  ITPUB个人空间3d;Al6\1C6k
R2iF
--    Michael Polaski - Oracle Support Services - DataServer Group
c|'d1xGt(S6gd q0--    Copyright 2002, Oracle Corporation      ITPUB个人空间V2D\&hHV"WG6t
-- ------------------------------------------------------------------------ ITPUB个人空间9fU"_Ngk
-- PURPOSE: 
V0Y O/zGw2fD0-- This script is intended to provide a user friendly guide to troubleshoot RAC  
8\'F3S-HD0s"g0-- hung sessions or slow performance scenerios.  The script includes information 
-R"f^:^Y(`0-- to gather a variety of important debug information to determine the cause of an ITPUB个人空间dF6e3q2Z$`A!GN.df
-- RAC hang.  The script will create a file called racdiag_<timestamp>.out ITPUB个人空间$zo(IV.m"C
-- in your local directory while dumping hang analyze dumps in the user_dump_dest(s)ITPUB个人空间
Z'rH\@,_
-- and background_dump_dest(s) on all nodes.
dh$i
|+B0--
yDvp'J0-- ------------------------------------------------------------------------ ITPUB个人空间,XhW^(hm5^
-- DISCLAIMER: ITPUB个人空间%w`qLY"G
--    This script is provided for educational purposes only. It is NOT  
s*M5s+`.R	P{0--    supported by Oracle World Wide Technical Support. 
"kn&h"K]
I0oyx`0--    The script has been tested and appears to work as intended. 
&qi;~|3Gv.@0--    You should always run new scripts on a test instance initially. ITPUB个人空间C+B	OwJq
-- ------------------------------------------------------------------------ ITPUB个人空间{g~/K:?um"^
-- Script output is as follows:
0@%g#C.O9x.L4a/o:i*`2O0ITPUB个人空间:j4jB5rJ9?(x/U
set echo off
~Jd\3c:i0set feedback offITPUB个人空间Pf\-K;Y.`-F)?iB
column timecol new_value timestampITPUB个人空间,M)^+Y;UPJ|l
column spool_extension new_value suffix
&x
s1}1W;j]E
V0select to_char(sysdate,'Mondd_hhmi') timecol,ITPUB个人空间"?1f X
z/~9h6u
'.out' spool_extension from sys.dual;ITPUB个人空间j'^(q1Ebn!oR
column output new_value dbnameITPUB个人空间Z&BzQ!q)z[uu
select value || '_' outputITPUB个人空间-j1S2{
XJ
from v$parameter where name = 'db_name';
,aa*M1n+dj;]+wZ0spool racdiag_&&dbname&×tamp&&suffixITPUB个人空间D!Z7^h!G	M
set lines 200ITPUB个人空间5GIDnh
set pagesize 35ITPUB个人空间8FZ]S|
set trim onITPUB个人空间8Wr(c
?naB
set trims onITPUB个人空间TgcB{7u^
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
YhE^Xb0alter session set timed_statistics = true;ITPUB个人空间-\S,r
Kd)f!NH
set feedback onITPUB个人空间J4mT	a2vU$v;@(P|
select to_char(sysdate) time from dual;ITPUB个人空间
UIF'Zghk
ITPUB个人空间A'w6C&A\'}T
set numwidth 5ITPUB个人空间'X!caW'ZBd-G'd
column host_name format a20 tru
GkR3Qb{$c0select inst_id, instance_name, host_name, version, status, startup_timeITPUB个人空间-Q7@{{P
Y
from gv$instance
*h*}4t`
@5CP\*a0order by inst_id;

S?|0x1b)Cc0ITPUB个人空间8zH2r3R#UVJ5G;d
set echo on
m}	H!e%U'P4{,N]0
,rT2uC
fX0-- Taking Hang Analyze dumps
)s	Sy3V!{5t0-- This may take a little while...ITPUB个人空间8u$gh rK!z8{k
oradebug setmypidITPUB个人空间/Mvh3P7q&Jl
oradebug unlimit
/J.DP6R)b)h6y1z0oradebug -g all hanganalyze 3
5bpvL1A0-- This part may take the longest, you can monitor bdump or udump to see if theITPUB个人空间5zCvDS2K
p
-- file is being generated.ITPUB个人空间
ki:~6A:|;P'_xH
\g/e
oradebug -g all dump systemstate 266
;P,nz/Ql)H9lYm0
QWWA^#b4[0-- WAITING SESSIONS:ITPUB个人空间0s4~ c_\&sN
-- The entries that are shown at the top are the sessions that have ITPUB个人空间
S'}XV,y-OP$YC
-- waited the longest amount of time that are waiting for non-idle wait 
rBC9vbK0-- events (event column).  You can research and find out what the waitITPUB个人空间%jf._K
~0GR
-- event indicates (along with its parameters) by checking the Oracle 
 hKw(OD7?0-- Server Reference Manual or look for any known issues or documentation 
4\e&Y0\/Z-R0-- by searching Metalink for the event name in the search bar.  Example ITPUB个人空间vE
p9qo}
I?\a&A\
-- (include single quotes): [ 'buffer busy due to global cache' ].
i+@x2l U)E
Y8u0-- Metalink and/or the Server Reference Manual should return some useful 
H*d)e&_K;[)w0-- information on each type of wait event.  The inst_id column shows theITPUB个人空间R&Y!v.v.?]
v
-- instance where the session resides and the SID is the unique identifierITPUB个人空间%Mueen_P9h
-- for the session (gv$session).  The p1, p2, and p3 columns will show 
o5T?'D
o-B&d0-- event specific information that may be important to debug the problem.ITPUB个人空间'[o~5IXHO
-- To find out what the p1, p2, and p3 indicates see the next section. ITPUB个人空间w[a@%U.O{+k
-- Items with wait_time of anything other than 0 indicate we do not know
6QD4iI)Lz0-- how long these sessions have been waiting.ITPUB个人空间.c!\ g*Njm4F\5feR
--
d3x]^u8g`G1D0set numwidth 10ITPUB个人空间a'N*}4l'bp0O
column state format a7 truITPUB个人空间W3|:l1d!i[
column event format a25 truITPUB个人空间h-t8V)q*}LN3xTW
p
column last_sql format a40 truITPUB个人空间u"\Rf*ai$Z
select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds, ITPUB个人空间(\^	s/[	IYf8v2h
sw.p1, sw.p2, sw.p3, sa.sql_text last_sqlITPUB个人空间nk9R)H7i}
from gv$session_wait sw, gv$session s, gv$sqlarea sa
eH/B	\TS0where sw.event not in 
K5f{1N:Gs-^3Reb"R0('rdbms ipc message','smon timer','pmon timer',ITPUB个人空间E U'g
o,d
e-b
^\/Z
'SQL*Net message from client','lock manager wait for remote message',ITPUB个人空间:q
dD6o!kj)Q5P#qPn
'ges remote message', 'gcs remote message', 'gcs for action', 'client message', ITPUB个人空间];h[
Fq4GV
'pipe get', 'null event', 'PX Idle Wait', 'single-task message', ITPUB个人空间+M	\]lb9n
MqJ
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', ITPUB个人空间
hz"`#xG+}9l
'listen endpoint status','slave wait','wakeup time manager')
;g$AG^
fu+_8O-i0and sw.seconds_in_wait > 0 
vE&h6[9xB*W0and (sw.inst_id = s.inst_id and sw.sid = s.sid)ITPUB个人空间}M
k'A2H8~
l9U*Y
and (s.inst_id = sa.inst_id and s.sql_address = sa.address)
s-h/o"PW7[!Li
\I0order by seconds desc;ITPUB个人空间[@)y7DN/h W4KA

f1ow!]d0T9m s"P0-- EVENT PARAMETER LOOKUP:
+|dI^5|l6|0-- This section will give a description of the parameter names of theITPUB个人空间b;y0hhoUW9n
-- events seen in the last section.  p1test is the parameter value forITPUB个人空间
J}[p+la^V4n.m
-- p1 in the WAITING SESSIONS section while p2text is the parameter
VE0x1f4K6\Fk
v0-- value for p3 and p3 text is the parameter value for p3.  The
*}3t;?}Hn6C%{q0-- parameter values in the first section can be helpful for debugging
 I*S1Yx0GlP p0-- the wait event.
$L(pj+B'h"i.G%FD0--
y/K
r6YR0column event format a30 truITPUB个人空间3x{;SBQn
column p1text format a25 truITPUB个人空间8dn-S7@4H/T
column p2text format a25 truITPUB个人空间 f%C%Iy(MF
column p3text format a25 truITPUB个人空间3^4R
[4GB;x(@Il?.m
select distinct event, p1text, p2text, p3textITPUB个人空间 Vu\@K&~I
a
from gv$session_wait swITPUB个人空间
X'edp$eMLZ
F
where sw.event not in ('rdbms ipc message','smon timer','pmon timer',ITPUB个人空间|,|7x2g2IU.I
'SQL*Net message from client','lock manager wait for remote message',
G;^'kRY;OMe0'ges remote message', 'gcs remote message', 'gcs for action', 'client message', ITPUB个人空间gRVI2u2U^m
'pipe get', 'null event', 'PX Idle Wait', 'single-task message', ITPUB个人空间;t)]-UGzv/d
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 
"~
V2]Uk \4K0'listen endpoint status','slave wait','wakeup time manager')
v9C[;?5|T~t&baL0and seconds_in_wait > 0ITPUB个人空间8@vx n!f*p1[$e4d
order by event;
A+s0e#vq"^B0ITPUB个人空间?$T
Izd3q:Y
-- GES LOCK BLOCKERS:ITPUB个人空间$hl"LZUe
-- This section will show us any sessions that are holding locks thatITPUB个人空间zq+fv(g
-- are blocking other users.  The inst_id will show us the instance thatITPUB个人空间
s4I}8z(`;V
-- the session resides on while the sid will be a unique identifier for
!g/mL(@wL A0-- the session.  The grant_level will show us how the GES lock is granted to 
5q!OyL`l
t/C0-- the user.  The request_level will show us what status we are trying to obtain.ITPUB个人空间4H)nn)K~^?|Ss1U
-- The lockstate column will show us what status the lock is in.  The last column ITPUB个人空间'wL:Z3t"@
-- shows how long this session has been waiting.
pSoN.lEAr0--
X4TA;ai0set numwidth 5
]$jP+qVH0column state format a16 tru;ITPUB个人空间_Y;UI
^	\K
column event format a30 tru;
/S1V*F\M0select dl.inst_id, s.sid, p.spid, dl.resource_name1, ITPUB个人空间1fc3a6q"NXR{7CD^[n
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',ITPUB个人空间5P*`d1hT	_b
\$T!l
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
2c5J5RkR\1Tn
|5d-l0'KJUSEREX','Exclusive',request_level) as grant_level,ITPUB个人空间a
\W j
EQM"e
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',ITPUB个人空间9h%YG3sF:F:?@)K].b:Q{
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',ITPUB个人空间yP\'txY
'KJUSEREX','Exclusive',request_level) as request_level, 
9na/T
b#nXLNs0decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
$\2N;M9K
S%U
H0'KJUSERCA','Canceling','KJUSERCV','Converting') as state,ITPUB个人空间	HcO$kD@2^z
s.sid, sw.event, sw.seconds_in_wait sec
4F(^aA4pomU"}@0from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
V
_d"s+mz
@9f5E0where blocker = 1
#Ep ly(?/c0and (dl.inst_id = p.inst_id and dl.pid = p.spid)ITPUB个人空间K
e&z3\+k"Sw
and (p.inst_id = s.inst_id and p.addr = s.paddr)

VQ!V1R}1M0and (s.inst_id = sw.inst_id and s.sid = sw.sid)
c6F#@)j2u[&V#p0order by sw.seconds_in_wait desc;ITPUB个人空间fy(i3Y7d cr
ITPUB个人空间%k:Th)vu])y
-- GES LOCK WAITERS:ITPUB个人空间VvCh@Y;]G
-- This section will show us any sessions that are waiting for locks thatITPUB个人空间F-{4L
JC)u0^
-- are blocked by other users.  The inst_id will show us the instance thatITPUB个人空间CoI;Y
_cw
-- the session resides on while the sid will be a unique identifier for
'K*\ZN#?	h0-- the session.  The grant_level will show us how the GES lock is granted to ITPUB个人空间'C"J1wv'TXghw
-- the user.  The request_level will show us what status we are trying to obtain.ITPUB个人空间l4leHe:V_'p+rDR
-- The lockstate column will show us what status the lock is in.  The last column ITPUB个人空间mU7y'x5|XB
-- shows how long this session has been waiting.ITPUB个人空间KR9jf"E
--ITPUB个人空间GnB,NT+Y4[ize
set numwidth 5
Cp9z@H"~7G6J"h%w0column state format a16 tru;
B8{?w6P
d0column event format a30 tru;ITPUB个人空间-yuA:IFdSs
select dl.inst_id, s.sid, p.spid, dl.resource_name1, ITPUB个人空间9oJ	IK9o2oY.hr
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
m;bvC[gYe!U0'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',ITPUB个人空间9j6C&l_%d0T8k l
h
'KJUSEREX','Exclusive',request_level) as grant_level,
t2M;k*Ci0decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',ITPUB个人空间.p|oe#Q*N~?
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',ITPUB个人空间@8L2d9m4h*eB*@,v.\
'KJUSEREX','Exclusive',request_level) as request_level, 
'|]S
j't [5eB0decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
/WII~6U"VN7h0'KJUSERCA','Cancelling','KJUSERCV','Converting') as state,ITPUB个人空间.x Z	z)ysN8K9FD
s.sid, sw.event, sw.seconds_in_wait sec
y0M0_.P#U&~b&Pk0from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
yYx$^W6}3r u!b0},]0where blocked = 1
2}v5Jt2Z	@'oio1z0and (dl.inst_id = p.inst_id and dl.pid = p.spid)
U#k%R\x+lr*Q0and (p.inst_id = s.inst_id and p.addr = s.paddr)
f)@m
UU|0and (s.inst_id = sw.inst_id and s.sid = sw.sid)
BY$Q^/EY|4b}Q&p0order by sw.seconds_in_wait desc;ITPUB个人空间P1]op
iZ	U ~7@/E
ITPUB个人空间g'i6m1I|#P
-- LOCAL ENQUEUES:
.?BC@4V,?U0-- This section will show us if there are any local enqueues.  The inst_id will ITPUB个人空间6h(a#nd"~
-- show us the instance that the session resides on while the sid will be a ITPUB个人空间s]is5@;T+{Y$LS
-- unique identifier for.  The addr column will show the lock  address. The type 
`)D3gw?*C0-- will show the lock type.  The id1 and id2 columns will show specific parameters 
)B	_3r9[K9L0-- for the lock type.  
\
x-j"`c	Frc0--
c'o
C&wYs6Gzq7i0set numwidth 12
|UoC.HU'B
Ll0column event format a12 tru
(`
?:l+W]$y2MM0select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2, 
*W7w9y_5|0decode(l.block,0,'blocked',1,'blocking',2,'global') block, 
G@%FRI0p8@;O0sw.event, sw.seconds_in_wait sec
,MeOc~!kb0from gv$lock l, gv$session_wait swITPUB个人空间 mRB2D6j
|~9?
where (l.sid = sw.sid and l.inst_id = sw.inst_id) ITPUB个人空间`[pLGMKP1\	B
and l.block in (0,1)

wb)V7`-H0order by l.type, l.inst_id, l.sid;
2X;?'U'q
^;P)d0ITPUB个人空间
SLb'z6fI
-- LATCH HOLDERS:ITPUB个人空间b}3^_+[*rUKv
-- If there is latch contention or 'latch free' wait events in the WAITINGITPUB个人空间ak5Y6c*}{K(?,S&So
-- SESSIONS section we will need to find out which proceseses are holding 
5?P)q\1o6s)y0-- latches.  The inst_id will show us the instance that the session resides ITPUB个人空间
_&~5Rq
vw8k8OI
-- on while the sid will be a unique identifier for.  The username column ITPUB个人空间#GLxbmV)e"p$_i
-- will show the session's username.  The os_user column will show the os 
B[@"_F1i3~0-- user that the user logged in as.  The name column will show us the type
.E[2w
{qHx0-- of latch being waited on.  You can search Metalink for the latch name in ITPUB个人空间'S |Hu!Do8QA
-- the search bar.  Example (include single quotes): 
o^v
A1q$s
f?0-- [ 'library cache' latch ]. Metalink should return some useful information ITPUB个人空间3[hqv._d'GDs]
-- on the type of latch.  
!hv7C'V1K\]]0--ITPUB个人空间N&}s~}l#{
set numwidth 5

m}WPqr0select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.nameITPUB个人空间D N3?k6?	G
from gv$latchholder lh, gv$session s, gv$process pITPUB个人空间'UC@ ]#XZ.n
GV
where (lh.sid = s.sid  and lh.inst_id = s.inst_id)
R-]#I-RG;pvuhX[\0and (s.inst_id = p.inst_id and s.paddr = p.addr)
9z/O8bs.~ Y7t0order by lh.inst_id, s.sid;
Sz(|3u-w5X`0ITPUB个人空间mnS`u(wOu^/D
-- LATCH STATS:
9uu0M$ebi#T0-- This view will show us latches with less than optimal hit ratios
Y4lY6z)?{S
~'f th0-- The inst_id will show us the instance for the particular latch.  The 
6_X'{$Gq2Q/MJQ8` wC0-- latch_name column will show us the type of latch.  You can search Metalink 
Y-bM
PN+Tg0-- for the latch name in the search bar.  Example (include single quotes): 
i:Bi[.j5k~)z(u0-- [ 'library cache' latch ]. Metalink should return some useful information ITPUB个人空间'cW@l+eeRZ
-- on the type of latch.  The hit_ratio shows the percentage of time we 
;`,~BG7P
KO"Ukp0-- successfully acquired the latch.ITPUB个人空间&ex[#H#@:r%N	g(YU
--
q,xv	NBb\W.xW0column latch_name format a30 truITPUB个人空间!co]L*g'tfR
select inst_id, name latch_name,
s!pr?&E"T
Dz0round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio, 
+^;R@5z%Q7HM0round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"ITPUB个人空间'mMGC9Lc
from gv$latchITPUB个人空间~"T-A3w.M
where round((gets-misses)/decode(gets,0,1,gets),3) < .99
\H
R:Pxv
c0and gets != 0
@KlPXG%l	r&S0order by round((gets-misses)/decode(gets,0,1,gets),3);ITPUB个人空间'F~2b,ME'SE

oe.KO$?%p(RMB0-- No Wait Latches:ITPUB个人空间:U6W
\YxU8f|
[6t
--
R3b~9v&Pp0select inst_id, name latch_name,
_
vOEdW!]&z0round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio, 
$v+kte&coU0round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS"ITPUB个人空间OkEuf
from gv$latch
E~E)g(A)]St)B0where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99
0O0R#e6RK4o1jA0and immediate_gets + immediate_misses > 0
|~Uz,bV;v0order by round((immediate_gets/(immediate_gets+immediate_misses)), 3);
fg
s6N|;X.L0ITPUB个人空间k0BedBS*QK
-- GLOBAL CACHE CR PERFORMANCEITPUB个人空间r/og*icFWO
h"G'V1fy
-- This shows the average latency of a consistent block request.  ITPUB个人空间NA|!d%|,n*Rv
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds depending 
2Wo@)yN0-- on your system configuration and volume, is the average latency of a ITPUB个人空间m1V/j3P*\{
-- consistent-read request round-trip from the requesting instance to the holding ITPUB个人空间pp6|ho
-- instance and back to the requesting instance. If your CPU has limited idle time 
wi3r	z}	w&H:Q0-- and your system typically processes long-running queries, then the latency may ITPUB个人空间a/]8U`T$v%`9?3b
-- be higher. However, it is possible to have an average latency of less than one ITPUB个人空间'P'A3D/cK4Zy;Kj
-- millisecond with User-mode IPC. Latency can be influenced by a high value for ITPUB个人空间6O3NU-Dvst Ddm
-- the DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process 
1h_wF_7h;m6V0-- can issue more than one request for a block depending on the setting of this 

~l
G(j;tX0-- parameter. Correspondingly, the requesting process may wait longer.  Also check
ct[|tw!J0-- interconnect badwidth, OS tcp settings, and OS udp settings if 
 |i8nC"H$Uv9x0-- AVG CR BLOCK RECEIVE TIME is high.ITPUB个人空间2NTX'c
O
--ITPUB个人空间
XCmi,EW.n
set numwidth 20
u9~1A(c$z)Z*s#Q0column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
~u^p2kef8\0select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED", 

ijo!p5R]p0b1.value "GCS CR BLOCK RECEIVE TIME",ITPUB个人空间o2I#M)U%OB
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
?i8I
LPM0from gv$sysstat b1, gv$sysstat b2
	x2j5]oa1{n:NFbW0where b1.name = 'global cache cr block receive time' andITPUB个人空间N*]@%m]p
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id ;ITPUB个人空间oUmcD\!_

;Qr1E+K
gk:w0-- GLOBAL CACHE LOCK PERFORMANCEITPUB个人空间eF2|1@,Cw5F3Ma
PQ
-- This shows the average global enqueue get time. 
9Zc
BAso0-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds.  the elapsed ITPUB个人空间[:Y b
^'l~"EB
-- time for a get includes the allocation and initialization of a new global 
j5w%IR!bd0-- enqueue. If the average global enqueue get (global cache get time) or average ITPUB个人空间B'N^Oq+Jb.\
-- global enqueue conversion times are excessive, then your system may be 
G
E.G6WL"c S0-- experiencing timeouts.  See the 'WAITING SESSIONS', 'GES LOCK BLOCKERS', ITPUB个人空间t
`|	?jFiy
-- 'GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM' sections if the 
]O;A,Boa	M0-- AVG GLOBAL LOCK GET TIME is high.ITPUB个人空间W[9d/U3|GS%x
--

XpqUGI
O0set numwidth 20ITPUB个人空间Y*hByM;?
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
v1e6P'X+S	qXyVq0select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS", 
8Z_ d0S3mXa"|#l5u0b3.value "GLOBAL LOCK GET TIME",ITPUB个人空间5B)O.H
m%Q	w
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"ITPUB个人空间4E-h^fX.t
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3ITPUB个人空间(St.}.NM6Pt
where b1.name = 'global lock sync gets' and
1d:Q:L M-y0b2.name = 'global lock async gets' and b3.name = 'global lock get time'
#X"[2TekZgU%xf0and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;
Q8qTwt4]0ITPUB个人空间R'dw!{)d(A
-- RESOURCE USAGEITPUB个人空间,B3n(}4qk5~ U+P"h
-- This section will show how much of our resources we have used. 
'\
P%QJ/r'i0--
cb#DgUH0set numwidth 8
 P{
MV$Io'`h~Eu2}0select inst_id, resource_name, current_utilization, max_utilization,ITPUB个人空间g]#t]Oc
a
initial_allocationITPUB个人空间0S)Q7J/D3RX~o9~~
from gv$resource_limitITPUB个人空间.ftj
?9a*JxW7C
where max_utilization > 0ITPUB个人空间3ec#B@2VKk
order by inst_id, resource_name;
 he6w
b,LAm0ITPUB个人空间g
J4x"|Oo%s
-- DLM TRAFFIC INFORMATION
e"DL)kL8uZY1V0-- This section shows how many tickets are available in the DLM.  If the 
1r;S3H
]`S3S
J{1BK0-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which couldITPUB个人空间^Q4edKgt
-- cause a DLM hang.  Make sure that you also have enough TCKT_AVAIL.  
(Fb,^d~AG
gN0--
A,c.a;@5]z-K*Y2Y0set numwidth 5
7cm$QZ
l0select * from gv$dlm_traffic_controller
"QF(B{@i|0order by TCKT_AVAIL;
%v#oq/t|0
.q`"J|o6j0-- DLM MISC
*P2H$E$r?D8S0--ITPUB个人空间(`px
~
Y2P/t\"e
set numwidth 10
!i},?.o``*[;H0select * from gv$dlm_misc;
0u
qJ+X9U
]j0
0k8V%`SAi0-- LOCK CONVERSION DETAIL:
2dSN-}y.]4Dd}0-- This view shows the types of lock conversion being done on each instance.ITPUB个人空间5dzB1u+@m/A;?
--
Nw:Jt0x/Wx0select * from gv$lock_activity;ITPUB个人空间`
T0Sg!yHA9]%^(F
ITPUB个人空间a$W1o3q5D{
-- TOP 10 WRITE PINGING/FUSION OBJECTSITPUB个人空间UQ7LI!Xpl/C
-- This view shows the top 10 objects for write pings accross instances.  ITPUB个人空间&K
s&gbx1kf8_9O
-- The inst_id column shows the node that the block was pinged on.  The name 
ganbRzsn0-- column shows the object name of the offending object.  The file# shows the ITPUB个人空间yeq!Cj
_9H({PhD
-- offending file number (gc_files_to_locks).  The STATUS column will show the 
5tVTkG$J7w&r,R0-- current status of the pinged block.  The READ_PINGS will show us read converts 
 UIiecT0-- and the WRITE_PINGS will show us objects with write converts.  Any rows that ITPUB个人空间%Lzv/Z1B,y
L
-- show up are objects that are concurrently accessed across more than 1 instance.
-F|nrf0--ITPUB个人空间/QXe-tC3M)y&W@
Y
set numwidth 8
\2t5g}t0column name format a20 truITPUB个人空间D6htfu"B%|L&bc*A
column kind format a10 truITPUB个人空间_%w;y
d-fxT
select inst_id, name, kind, file#, status, BLOCKS, ITPUB个人空间
{^Rs$r
READ_PINGS, WRITE_PINGSITPUB个人空间7j$cySV]]
j
from (select p.inst_id, p.name, p.kind, p.file#, p.status, ITPUB个人空间(LB.Q#[@g,{Qu
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS, ITPUB个人空间wM;oR\Q
sum(p.forced_writes) WRITE_PINGS
PM'SA#N0zR1un3q0from gv$ping p, gv$datafile df
1tR+Pn(x0where p.file# = df.file# (+)
`I_k]0b,O0group by p.inst_id, p.name, p.kind, p.file#, p.status
?5z Y
\cb
^M:K-}0order by sum(p.forced_writes) desc)
p'A'P3SlK*P	}.d6B0where rownum < 11ITPUB个人空间q
N$@5?6K?,MCk
order by WRITE_PINGS desc;ITPUB个人空间_f+J;m&[

5`L:Dq;xI8t0-- TOP 10 READ PINGING/FUSION OBJECTSITPUB个人空间~6QV#Ac/^*zC9D
-- This view shows the top 10 objects for read pings.  The inst_id column shows 
"wQ#Ob)Id	cc0-- the node that the block was pinged on.  The name column shows the object name ITPUB个人空间C*V\#y'[ O-I
-- of the offending object.  The file# shows the offending file number ITPUB个人空间3M'_PgS3]9n$k|
-- (gc_files_to_locks).  The STATUS column will show the current status of theITPUB个人空间TL(k7y0[5t#F
-- pinged block.  The READ_PINGS will show us read converts and the WRITE_PINGS ITPUB个人空间^f-wkIPt
-- will show us objects with write converts.  Any rows that show up are objects ITPUB个人空间,l'j{4W*A\RK8i Z
-- that are concurrently accessed across more than 1 instance.
;r7pf,Z4}-tM0--
$@-}p3L}!hy*T0set numwidth 8
_vh$P4E:g#l0column name format a20 truITPUB个人空间ainu7hE
column kind format a10 tru
+tlW2]:}0select inst_id, name, kind, file#, status, BLOCKS, ITPUB个人空间)l%V1_"G	zOs
READ_PINGS, WRITE_PINGSITPUB个人空间.urYd4_7B
from (select p.inst_id, p.name, p.kind, p.file#, p.status, 
g|8O3xPK0count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS, 
)hg1t?){0T m0sum(p.forced_writes) WRITE_PINGSITPUB个人空间D'Kc
k_$Z
from gv$ping p, gv$datafile df
2Q'o8q%O
c-UB/]&~0where p.file# = df.file# (+)

BAHn4t0group by p.inst_id, p.name, p.kind, p.file#, p.status
K	d7\ha[Z!DhC0order by sum(p.forced_reads) desc)
R,f6\~$I3A2M-J0where rownum < 11
9ibTrudk'x:x$}0order by READ_PINGS desc;
Q#f3[p`0ITPUB个人空间`T^+Qwo&p
-- TOP 10 FALSE PINGING OBJECTS
(mN V+}s*^0-- This view shows the top 10 objects for false pings.  This can be avoided by
1bQ9Q
g4U/a0-- better gc_files_to_locks configuration.  The inst_id column shows the nodeITPUB个人空间p"RjI:uOrC
-- that the block was pinged on.  The name column shows the object name of the 
:ItIUT;U8f0-- offending object.  The file# shows the offending file number 
m6X3w5e-a8l0-- (gc_files_to_locks).  The STATUS column will show the current status of the
*M#ez5W	N0i0-- pinged block.  The READ_PINGS will show us read converts and the WRITE_PINGS ITPUB个人空间Bv{9Kby&`mYp
-- will show us objects with write converts.  Any rows that show up are objects ITPUB个人空间0nk'yz9C0l/BEO
-- that are concurrently accessed across more than 1 instance.
\vk%IK
S	vc7{/A0--ITPUB个人空间X?D5sB8dn9a%t
set numwidth 8ITPUB个人空间3i|Tu"G.zEL,M3^*i
column name format a20 tru

s8@5f?kZy*m/x{0column kind format a10 tru

zt2eP	BqE.bT0select inst_id, name, kind, file#, status, BLOCKS, ITPUB个人空间
J7z'l.JY&dr
READ_PINGS, WRITE_PINGS
/[Q.m1V&td#gv7_,O0from (select p.inst_id, p.name, p.kind, p.file#, p.status, 
n0KWR7G-V2pJ0count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS, ITPUB个人空间 x7r]@g.q5w7~1o
sum(p.forced_writes) WRITE_PINGS
f%bI1w!C`&@hHe0from gv$false_ping p, gv$datafile dfITPUB个人空间0V4{H$m1zv0s
where p.file# = df.file# (+)
rI?)aE0group by p.inst_id, p.name, p.kind, p.file#, p.status
;]l;]I({:bcGQ0order by sum(p.forced_writes) desc)ITPUB个人空间a ^U~9vk4C
where rownum < 11
(?[:Q
~
bSxKd?-p0order by WRITE_PINGS desc;ITPUB个人空间s0[&uP:},lW
ITPUB个人空间,k+_2uS}$C;t$N
-- INITIALIZATION PARAMETERS:ITPUB个人空间P0E&x#}[
g-C&U9g
-- Non-default init parameters for each node.ITPUB个人空间Z
][1O,x.BO&S.uW
--
H)@2e'g.d!h8^'vw0set numwidth 5
9_lQ
\J.X0column name format a30 tru
q{0@5_Jh0column value format a50 wra
U;a$DG:u;G&q$W;c0column description format a60 truITPUB个人空间O1T(F
v y3v:O
select inst_id, name, value, descriptionITPUB个人空间(V!KF3J5v9yQJ;[
from gv$parameterITPUB个人空间Y)H9O	|$a/MxS-m@
where isdefault = 'FALSE'ITPUB个人空间G
`.Q
m({K*t
order by inst_id, name;
Sx O:K@"F0
7C	r"hv5_7H8P1a%q0-- TOP 10 WAIT EVENTS ON SYSTEMITPUB个人空间DM"H
kRbk~
-- This view will provide a summary of the top wait events in the db.
'CL_{I@H0--
0jL7^p(D0D&CZ0set numwidth 10ITPUB个人空间G co-iB	n,sH
column event format a25 truITPUB个人空间
Z-M)tL5f)C
select inst_id, event, time_waited, total_waits, total_timeouts
pv'I["N}0from (select inst_id, event, time_waited, total_waits, total_timeouts
,|
cu|W'uc0from gv$system_event where event not in ('rdbms ipc message','smon timer',ITPUB个人空间1HHD|/Acmv
'pmon timer', 'SQL*Net message from client','lock manager wait for remote message',
k8nR7pw"["g2~0'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 
9p3bE6D8Y?9SW0'pipe get', 'null event', 'PX Idle Wait', 'single-task message', ITPUB个人空间3n1sNUID:X`
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 
EK8Os&^"gG0'listen endpoint status','slave wait','wakeup time manager')ITPUB个人空间$z	Cw#Z0c3Q
order by time_waited desc)
_TK&j'X4Y
P_0where rownum < 11ITPUB个人空间,K0l&].m2J!I@o#H
order by time_waited desc;ITPUB个人空间5zp3uUV/r
ITPUB个人空间alaFA!_@J
-- SESSION/PROCESS REFERENCE:ITPUB个人空间9Om-{)Kev
-- This section is very important for most of the above sections to find out 
%^6z
S	[j'`C9R	]4V0-- which user/os_user/process is identified to which session/process.
Nc.l;V2rt0--ITPUB个人空间,W6}V8dT$u8D
set numwidth 7ITPUB个人空间5z)v!x3FOh(K
column event format a30 truITPUB个人空间
i+t5Y;N]\ Hp
column program format a25 tru

[LV;xo0column username format a15 tru
6N WE"o:|'EK&J]p0select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username, ITPUB个人空间*X+sq7M!GN
p.username os_user, sw.event, sw.seconds_in_wait sec  ITPUB个人空间N#w_2Z)B)l f!E#i$Z
from gv$process p, gv$session s, gv$session_wait sw
(Qo$C:B"Urp0where (p.inst_id = s.inst_id and p.addr = s.paddr)
z1~9N'x$T+J`
y6g(^"D0and (s.inst_id = sw.inst_id and s.sid = sw.sid)
'^Wh#Yv0order by p.inst_id, s.sid;
3G:LH:u4I0ITPUB个人空间j4P1dl
}Z3]q
-- SYSTEM STATISTICS:ITPUB个人空间%[e*eg
L*ZA
-- All System Stats with values of > 0.  These can be referenced in the

H!k[)M:`3f0-- Server Reference Manual
^2K0a&z{7wZ0--ITPUB个人空间Z\?)?}
set numwidth 5ITPUB个人空间lI*s
iY!Y1O(m
column name format a60 truITPUB个人空间hl/JY,[E(p7H~"r b&r
column value format 9999999999999999999999999
U9w7yWJ0select inst_id, name, valueITPUB个人空间4b`K9_w
M"M^
from gv$sysstatITPUB个人空间8lu$N!Y$d(a0|+i%Og
where value > 0 
9{CN;Dt	j0order by inst_id, name;ITPUB个人空间3IQIjK8l#V

|*L%~M$]IsL0-- CURRENT SQL FOR WAITING SESSIONS:ITPUB个人空间'wx`8c9Krt3u0]
-- Current SQL for any session in the WAITING SESSIONS list
	P!gb}GP5@f0--
;S(m4KocX0set numwidth 5ITPUB个人空间8B+Y~)b#t'E3e W
column sql format a80 wraITPUB个人空间0o'J&}8H;zu{ib1n
select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql
g},cI
c+Ua_0from gv$session_wait sw, gv$session s, gv$sqlarea saITPUB个人空间3u(mOr*d@
r4^9[BH a
where sw.sid = s.sid (+) 
o
TMt}3n'E|0and sw.inst_id = s.inst_id (+)ITPUB个人空间]$q\Db-p3r
wOZ4O
and s.sql_address = sa.address ITPUB个人空间8P
e8g6T
{9k,D"AP
and sw.event not in ('rdbms ipc message','smon timer','pmon timer',ITPUB个人空间C|&nQ|)f-k(y)`	[-K
'SQL*Net message from client','lock manager wait for remote message',
*G.qqg	{&x0'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 
;I.y:]6]^M0'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 
k
{,ZVil,l]E0'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', ITPUB个人空间8oY]u3c5?#M Z
'listen endpoint status','slave wait','wakeup time manager')
;[qG"joDO3t0and seconds_in_wait > 0
l)t*ZS,v#mu0order by sw.seconds_in_wait desc; 
J1aF`!}
P'w'B0ITPUB个人空间#a&x,^G5DC
-- Taking Hang Analyze dumps
	n,t+t$Zr'U0-- This may take a little while...
S&t
iB^F0oradebug setmypidITPUB个人空间d*b kY6@XS
oradebug unlimitITPUB个人空间iws)f6V-oS+u(Z,r
oradebug -g all hanganalyze 3ITPUB个人空间7n.T&h0R9} {9I4{
-- This part may take the longest, you can monitor bdump or udump to see if theITPUB个人空间R(m D}
o
Q'I}hUMQ
-- file is being generated.
-C6B|En4K$l0oradebug -g all dump systemstate 266ITPUB个人空间S)i$iiI5C^	pa3l

6D"`%a*F[t(h0set echo off
b	?Az!s~^0
:t]8R?!yf]c%e0select to_char(sysdate) time from dual;ITPUB个人空间!B$m(j+hz1e^

'ou#UC7|Ti0spool offITPUB个人空间:[f	D*G!c Fz+Z;NU9O
ITPUB个人空间W8Wl4i7WpYT
-- ---------------------------------------------------------------------------ITPUB个人空间ia)?"D"\2`xum
Prompt;
|,@(Joe8s]Wx
i5Q0Prompt racdiag output files have been written to:;
_#l0MQ+s&c6|$GMz0Prompt;ITPUB个人空间:VL4~Z7N
host pwdITPUB个人空间H2[V*W~4M4c[
Prompt alert log and trace files are located in:;

\S i)a+i?QSxV0column host_name format a12 truITPUB个人空间){xO9Aa,|RA
column name format a20 tru
WliSD_#h
}Ix0column value format a60 truITPUB个人空间h#p-{)J8SS$jn!V
select distinct i.host_name, p.name, p.valueITPUB个人空间6Z;S:d(g
Y)FR
JH/[3@q3g
from gv$instance i, gv$parameter p
wdP;X@F0where p.inst_id = i.inst_id (+)ITPUB个人空间
T0gr1_+`'Dd
and p.name like '%_dump_dest' ITPUB个人空间^DVt/{
S1tP#p#Yg
and p.name != 'core_dump_dest';ITPUB个人空间
L1y9?toe
ITPUB个人空间O;m5f4r)q{]
 - - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -