enqueue等待事件的处理
有些时候，操作执行非常慢，当你从session_wait会话里查看在等待什么时，如果发现有很多的enqueue事件，那么可以看以下步骤处理
select * from gv$session_wait where event not like ‘SQL%’ AND EVENT=’enqueue’
首先查询会话都在等待什么资源
SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like ‘SQL*%’ and event not like ‘rdbms%’;
EVENT SID P1 P2 P3
—————————————————————- ———- ———- ———- ———-
pmon timer 1 300 0 0
ges remote message 4 32 0 0
gcs remote message 5 64 0 0
gcs remote message 7 64 0 0
smon timer 12 300 0 0
enqueue 23 1415053318 655363 1673
global cache s to x 24 2 3 1.5094E+10
7 rows selected.
发现enqueue等待非常严重，查询enqueue等待事件的详细信息
select * from v$enqueue_stat;
INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
———- — ———- ———– ———- ———– ————-
1 CF 1825643 14 1825640 3 0
1 CU 257 0 257 0 0
1 DL 41 19 41 0 0
1 DM 5 4 5 0 0
1 DP 1 0 1 0 0
1 DR 649 649 649 0 0
1 DS 2 0 2 0 0
1 DT 1 0 1 0 0
1 HW 2219 14 2219 0 0
1 IA 1 1 1 0 0
1 IR 524 0 524 0 0
1 IS 11 0 11 0 0
1 MD 5 5 5 0 0
1 MR 365 7 365 0 0
1 PE 50122 0 50122 0 0
1 PI 1 0 1 0 0
1 PR 1 0 1 0 0
1 RO 17 14 17 0 0
1 RT 4 1 3 1 0
1 SQ 251 0 251 0 0
1 SR 1 0 1 0 0
1 SS 1 0 1 0 0
1 ST 209 209 209 0 0
1 SW 2 0 2 0 0
1 TA 7633 167 7633 0 0
1 TD 3887 3887 3887 0 0
1 TM 178395 59 178395 0 0
1 TS 25 9 25 0 0
1 TT 169372 0 169372 0 0
1 TX 167088 9 167086 0 0
1 US 667 0 667 0 0
1 WL 3 3 3 0 0
1 XR 4 2 4 0 0
33 rows selected.
发现TX,TT,TM这三种锁等待时间都比较长，
TX是事务锁：
主要发生在以下情况：
1 你请求的资源备其他会话锁住
2 当两个会话同时插入一个相同的VALUE，但是两个会话都没有提交，因此，最后 一个会话等待第一个会话提交或回滚
3 没有可能用的事务槽。
TT:DDL操作表空间的一些操作
TM DML，TM是伴随着TX出现的，TM代表有DML（INSERT UPDATE,DELETE)操作引起的锁
那么现在找出现在持有 和等待enqueue事件的锁
SQL> set line 160
SQL> col machine format a10
SQL> col username format a15
SQL> select b.sid,b.serial#,b.username,machine,event,wait_time,chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) “Enqueue Type” from v$session_wait a,v$session b
2 where a.event not like ‘SQL*N%’ and a.event not like ‘rdbms%’ and a.sid=b.sid
3 and b.sid>8 and a.event=’enqueue’
4 order by username;
SID SERIAL# USERNAME MACHINE EVENT WAIT_TIME En
———- ———- ————— ———- —————————— ———- –
23 44840 SYS tsp5a enqueue 0 TX
发现23号会话在等待enqueue事件。
SQL> SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)|| sid sess, id1, id2, lmode,
2 request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
SESS ID1 ID2 LMODE REQUEST TY
———————————————— ———- ———- ———- ———- –
Holder: 24 655363 1673 6 0 TX
Waiter: 23 655363 1673 0 6 TX
24号会话持有23号会话需要的锁。
继续分析，查询具体导致的SQL语句
23号
SQL> SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = &sid) ORDER BY piece ASC;
Enter value for sid: 23
old 1: SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = &sid) ORDER BY piece ASC
new 1: SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = 23) ORDER BY piece ASC
SQL_TEXT
—————————————————————-
delete from test where rowid in(select rid from
(select rowid rid,row_number() over(partition by id,name order by id desc) rn from test)
where rn > 1)
查询会话24执行的SQL语句内容:
SQL> SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = &sid) ORDER BY piece ASC;
Enter value for sid: 24
old 1: SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = &sid) ORDER BY piece ASC
new 1: SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = 24) ORDER BY piece ASC
SQL_TEXT
—————————————————————-
delete FROM test WHERE ROWID!=(SELECT MAX(ROWID) FROM test D W
HERE test.name=D.name AND test.id=D.id)
这条语句我之前也执行过,但由于执行时间太长,还没等结果返回我就按ctrl+c强制结束了它,语句非正常退出.
查询被锁的对象
SQL> SELECT /*+ rule */ lpad(’ ‘,decode(l.xidusn ,0,3,0))||l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#
2 FROM v$locked_object l,dba_objects o,v$session s
3 WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC;
USER_NAME
————————————————————————————————————————
OWNER
——————————
OBJECT_NAME
————————————————————————————————————————
OBJECT_TYPE SID SERIAL#
—————— ———- ———-
SYS
SYS
TEST
TABLE 24 36906
SYS
SYS
TEST
TABLE 23 44840
根据以上信息我们可以断定是由于会话24锁住了会话23请求的数据行,造成了enqueue等待,从而使删除语句执行很慢,并且一直没有结果返回.
解决方法:
杀死相应的会话并释放锁资源:
SQL> alter system kill session ‘24,36906′;
System altered.
再次查询会话等待事件及锁信息:
SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like ‘SQL*%’ and event not like ‘rdbms%’;
EVENT SID P1 P2 P3
—————————— ———- ———- ———- ———-
pmon timer 1 300 0 0
ges remote message 4 32 0 0
gcs remote message 5 64 0 0
gcs remote message 7 64 0 0
smon timer 12 300 0 0
SQL> SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)|| sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
no rows selected
SQL> select * from v$locked_object;
no rows selected
enqueue等待事件消失,数据库恢复了正常,删除语句很快返回了结果:
SQL> delete from test where rowid in (select rid from
2 (select rowid rid,row_number() over(partition by id,name order by id desc) rn from test)
3 where rn > 1);
40000 rows deleted.
但此时通过查询v$session,发现会话24依然存在,并属于僵死状态(KILLED):
SQL> select sid,serial#,username,status from v$session;
SID SERIAL# USERNAME STATUS
———- ———- —————————— ——–
1 1 ACTIVE
2 1 ACTIVE
3 1 ACTIVE
4 1 ACTIVE
5 1 ACTIVE
7 1 ACTIVE
9 1 ACTIVE
10 1 ACTIVE
11 1 ACTIVE
12 1 ACTIVE
13 1 ACTIVE
14 1 ACTIVE
15 1 ACTIVE
18 1 ACTIVE
20 30 RTP_USER_99 INACTIVE
21 20 RTP_USER_99 INACTIVE
22 10984 RTP_USER_99 INACTIVE
23 44840 SYS INACTIVE
24 36906 SYS KILLED
25 330 SYS ACTIVE
20 rows selected.
对于僵死会话,需要杀掉对应的OS进程才能完全清除.
找出进程在OS中的位置,并kill掉:
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=&sid and ses.paddr=pro.addr;
Enter value for sid: 24
old 1: select pro.spid from v$session ses,v$process pro where ses.sid=&sid and ses.paddr=pro.addr
new 1: select pro.spid from v$session ses,v$process pro where ses.sid=24 and ses.paddr=pro.addr
SPID
————
17676
oracle@tsp5a> ps -ef |grep 17676 | grep -v grep
oracle 17676 17675 0 04:25:14 ? 0:00 oracleT001 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle@tsp5a> kill -9 17676
至此,问题全部解决完毕.
总结: 同样的enqueue可能在不同的场景中发生。比如TX, 就可能在下列情况下发生: ITL分配,其他事务占用,数据行被锁定,索引块分裂等,本篇文章就是数据行被锁定引发enqueue等待的其中一个案例,所以在处理enqueue等待时,除了要清楚enqueue的类型,还要清楚是什么原因导致该enqueue的,才好对症下药。
EYGLE写了一篇非常好的文章描述如何处理ST enqueue的一个案例,具体可以参考: http://www.eygle.com/statspack/statspack13.htm
附: 关于Enqueue 等待事件的整理:
(1). ST enqueue，用于空间管理和字典管理的表空间(DMT)的区间分配,在DMT中典型的是对于uet$和fet$数据字典表的 争用。对于支持LMT的版本,应该尽量使用本地管理表空间. 或者考虑手工预分配一定数量的区(Extent),减少动态扩展时发生的严重队列竞争。
(2). HW enqueue指和段的高水位标记相关等待; 手动分配适当区可以避免这一等待。
(3). TX是最常见的enqueue等待, TX enqueue等待通常是以下三个问题之一产生的结果。
第一个问题是唯一索引中的重复索引,你需要执行提交（commit）/回滚（rollback）操作来释放enqueue。
第二个问题是对同一位图索引段的多次更新。因为单个位图段可能包含多个行地址（rowid）,所以当多个用户试图更新同一段时,可能一个用户会锁定其他用户请求的记录,这时等待出现。直到获得锁定的用户提交或回滚, enqueue才释放。
第三个问题,也是最可能发生的问题是多个用户同时更新同一个块。如果没有足够的ITL槽,就会发生块级锁定。通过增大initrans和/或maxtrans以允许使用多个ITL槽(对于频繁并发进行DML操作的数据表，在建表之初就应该考虑为相应参数设置合理的数值,避免系统运行以后在线的更改,在8i之前, freelists等参数不能在线更改,设计时的考虑就尤为重要),或者增大表上的pctfree值,就可以很容易的避免这种情况。
(4). TM enqueue队列锁在进行DML操作前获得,以阻止对正在操作的数据表进行任何DDL操作(在DML操作一个数据表时，其结构不能被更改)。
