从Oracle11gr1开始,dia0后台进程开始收集Hang分析信息并存储在内存中的"hang analysis cache"中.它会每3秒钟收集一次本地的Hang分析和第10秒钟收集一次全局(rac)Hang分析信息.这些信息在出现Hang时提供快速查看Hang链表的方法.
存储在"hang analysiz cache"中的数据对于诊断数据库竞争和Hang是非常有效的
有许多数据库功能可以利用Hang分析缓存:Hang Management, Resource Manager Idle Blocker Kill,
SQL Tune Hang Avoidance和PMON清除以及外部工具象Procwatcher
下面是oracle11gr2中v$wait_chains视图的描述:
SQL> desc v$wait_chains
Name Null Type
----------------------------------------- -------- ----------------------
CHAIN_ID NUMBER
CHAIN_IS_CYCLE VARCHAR2(5)
CHAIN_SIGNATURE VARCHAR2(801)
CHAIN_SIGNATURE_HASH NUMBER
INSTANCE NUMBER
OSID VARCHAR2(25)
PID NUMBER
SID NUMBER
SESS_SERIAL# NUMBER
BLOCKER_IS_VALID VARCHAR2(5)
BLOCKER_INSTANCE NUMBER
BLOCKER_OSID VARCHAR2(25)
BLOCKER_PID NUMBER
BLOCKER_SID NUMBER
BLOCKER_SESS_SERIAL# NUMBER
BLOCKER_CHAIN_ID NUMBER
IN_WAIT VARCHAR2(5)
TIME_SINCE_LAST_WAIT_SECS NUMBER
WAIT_ID NUMBER
WAIT_EVENT NUMBER
WAIT_EVENT_TEXT VARCHAR2(64)
P1 NUMBER
P1_TEXT VARCHAR2(64)
P2 NUMBER
P2_TEXT VARCHAR2(64)
P3 NUMBER
P3_TEXT VARCHAR2(64)
IN_WAIT_SECS NUMBER
TIME_REMAINING_SECS NUMBER
NUM_WAITERS NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
注意:v$wait_chains等价于gv$视图可能在rac环境中报告多个实例
使用sql来查询基本信息
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30)
FROM v$wait_chains; 2
CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID BLOCKER_OSID SUBSTR(WAIT_EVENT_TEXT,1,30)
---------- ----------- ------------ -------------- ------------------------- -----------------------------
1 0 10198 21045 21044 enq: TX - row lock contention
1 1 10214 21044 SQL*Net message from client
查询top 100 wait chain processs
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
Current Process:21549 SID RAC1 INST #: 1
Blocking Process: from Instance Number of waiters:1
Wait Event:SQL*Net message from client P1: 1650815232 P2: 1 P3:0
Seconds in Wait:36 Seconds Since Last Wait:
Wait Chaing:1 : 'SQL*Net message from client '< ='enq: TX - row lock contention'
Blocking Wait Chain:
Current Process:25627 SID RAC1 INST #: 1
Blocking Process:21549 from Instance 1 Number of waiters:0
Wait Event:enq: TX - row lock contention P1:1415053318 P2: 524316 P3:50784
Seconds in Wait:22 Seconds Since Last Wait:
Wait Chain:1 : 'SQL*Net message from client '< ='enq: TX - row lock contention'
Blocking Wait Chain:
ospid 25627正等待一个TX lock正被ospid 21549所阻塞
ospid 21549正空闲等待'SQL*Net message from client'
在oracle11gr2中的最终阻塞会话
在oracle11gr2中可能将v$session.final_blocking_session看作��最终的阻塞者.最终的阻会话/进程在top等待链表上.
这些会话/进程可能是造成问题的原因.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,
'Number of waiters: '||num_waiters waiters,
'Final Blocking Process: '||decode(p.spid,null,'',
p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
'Program: '||p.program image,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
gv$session s,
gv$session bs,
gv$instance i,
gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
and wc.sess_serial# = s.serial# (+))
AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
Current Process:2309 SID RAC1 INST #: 1
Blocking Process: from Instance Number of waiters:2
Wait Event:SQL*Net message from client P1: 1650815232 P2: 1 P3:0
Seconds in Wait:157 Seconds Since Last Wait:
Wait Chaing:1 : 'SQL*Net message from client '< ='enq: TM - contention'<='enq: TM - contention'
Blocking Wait Chain:
Current Process:2395 SID RAC1 INST #: 1
Blocking Process:2309 from Instance 1 Number of waiters:0
Final Block Process:2309 from Instance 1 Program: oracle@racdbe1.us.oracle.com (TNS V1-V3)
Wait Event:enq: TX - contention P1:1415053318 P2: 524316 P3:50784
Seconds in Wait:139 Seconds Since Last Wait:
Wait Chain:1 : 'SQL*Net message from client '< ='enq: TM - contention'<='enq: TM - contention'
Blocking Wait Chain:
B.对数据库性能生成一个awr/statspack快照
C.收集最新的RDA
最新的RDA提供了大量额外关于数据库配置和性能度量的信息可以用来检测可能影响性能的热点的后台进程问题
有时数据库不是真正的被hang住可是只是'spinning' cpu.可以使用以下方法来检查服务器是hang还是spin如果一个操作执行的时间比期待的时间长或者这个操作损害了其它操作的性能时那么最好是检查v$session_wait视图.这个视图显示了在系统中会话当前正在等待的信息.可以使用下面的脚本来操作.
column sid format 990
column seq# format 99990
column wait_time heading 'WTime' format 99990
column event format a30
column p1 format 9999999990
column p2 format 9999999990
column p3 format 9990
select sid,event,seq#,p1,p2,p3,wait_time
from V$session_wait
order by sid
/
上面的查询最少应该执行三次并比较其它查询结果
列意思
sid-- 会话的系统标识符
seq#--序列号.当一个特定会话的等待一个新的事件时这个数字会增加.它能告诉你一个会话是否正在执行
evnet--会话正在等待的或最后等待的操作
p1,p2,p3--它们代表不同的等待值
wait_time--0指示这个会话正在等待的事件.非0指示这个会话最后等待的事件和会话正使用CPU
例如:
SID EVENT SEQ# P1 P2 P3 WTime
---- ------------------------------ ------ ----------- ----------- ----- ------
1 pmon timer 335 300 0 0 0
2 rdbms ipc message 779 300 0 0 0
6 smon timer 74 300 0 0 0
9 Null event 347 0 300 0 0
16 SQL*Net message from client 1064 1650815315 1 0 -1
如果脚本查询的结果显示正在等待一个enqueue等待事件那么你将需要检查与你hang会话相关的锁信息
column sid format 990
column type format a2
column id1 format 9999999990
column id2 format 9999999990
column Lmode format 990
column request format 990
select * from v$lock
/
Spinning
在spin的情况下事件通常来说是静态的且会话不会是正在等待一个事件--而是在等待cpu(注意在极少数情况下,这个事件依赖于执行spin的代码也可能不会静态的.如果会自豪感是spin它将严重使用cpu和内存资源.
对于一个spin的情况重要的是要检测会话正处于spinning的代码.从事件的一些迹象说明通常需要对一个进程生成几次的错误堆栈信息用来分析:
connect sys/sys as sysdba
oradebug setospid
oradebug unlimit
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
这里的spid是操作系统标识符可以从v$process视图是得到.
Hanging
在正常的情况下在v$session_wait视图中的值应该是用每个会话执行的不同操作来替换.
在hang住的情况下对于一个或一组特定会话的所有系统事件将会是保持静态状态且进程不会消耗任何cpu和内存资源.鉴于会话现在没有请求锁定任何资源这就叫hang
在这种情况下可对实例转储系统状态来获得一些更详细更有用的信息.
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL XX';
在oralce9.2.0.6或oracle10.1.0.4或在oracle10g中最高的版本的中这里的xx是266.执行上面的命令在你的user_dump_dest目录中会生成系统状态跟踪文件.
通过下面的查询可以得到问题进程的进程ID
SELECT pid FROM v$process
WHERE addr =
(SELECT paddr FROM v$session
WHERE sid = sid_of_problem_session);
系统状态转储文件包含了每一个进程的信息.可以通过搜索'PROCESS '来找到每一个进程的详细信息.通过搜索'waiting for'来找到当前正在等待的事件.
534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
