enqueue等待事件优化一例

数据库版本:Oracle 9.2.0.4.0

操作系统: Solaris 10

今天在测试库上跑一个SQL语句,目的是为了删除重复记录,但执行却异常的慢,等了20分钟都没返回结果.

该SQL语句如下:

SQL>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);

test表结构如下:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(10)
 TIME                                               DATE

查询表数据量和大小:

SQL> select count(*) from test;

  COUNT(*)
----------
     40001   

SQL> select sum(bytes)/1024/1024 M from dba_segments where segment_name='TEST';

         M
----------
  1.13671875

表不大,按常理不应该删除那么慢,数据库肯定存在一些问题.

查询会话都在等待什么资源:
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等待事件的详细信息:

SQL> 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.

发现TT,TX和TM这三种类型的锁等待时间最长.

TX: 事务锁,主要在以下情况下发生:

1) Another session is locking the requested row.

2) When two sessions tries to insert the same unique key into a table (none of them has done a COMMIT), then the last session is waiting for the first one to COMMIT or ROLLBACK.

3) There are no free ITL (Interested Transaction List) in the block header (increase INI_TRANS or PCT_FREE for the segment).

TT: Serializes DDL operations on tablespaces;

TM: DML enqueue,TM是伴随着TX出现的,TM代表有DML操作引起的锁.

找出enqueue，lock的holder和waiter:
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 event not like 'SQL*N%' and event not like 'rdbms%' and a.sid=b.sid
  3  and b.sid>8 and event='enqueue'
  4  order by username;

       SID    SERIAL# USERNAME        MACHINE    EVENT                           WAIT_TIME En
---------- ---------- --------------- ---------- ------------------------------ ---------- --
        23      44840 SYS             tsp5a      enqueue                                 0 TX

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

查询会话23执行的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: 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)

这就是那条在测试库上跑的SQL语句.

查询会话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强制结束了它,语句非正常退出.

查询被锁的对象,发现是表TEST:
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操作一个数据表时，其结构不能被更改)。

enqueue类型:
BL, Buffer Cache Management
CF, Controlfile Transaction
CI, Cross-instance Call Invocation
CU, Bind Enqueue
DF, Datafile
DL, Direct Loader Index Creation
DM, Database Mount
DR, Distributed Recovery
DX, Distributed TX
FS, File Set
IN, Instance Number
IR, Instance Recovery
IS, Instance State
IV, Library Cache Invalidation
JQ, Job Queue
KK, Redo Log “Kick”
L[A-P], Library Cache Lock
MR, Media Recovery
N[A-Z], Library Cache Pin
PF, Password File
PI, Parallel Slaves
PR, Process Startup
PS, Parallel Slave Synchronization
Q[A-Z], Row Cache
RT, Redo Thread
SC, System Commit Number
SM, SMON
SQ, Sequence Number Enqueue
SR, Synchronized Replication
SS, Sort Segment
ST, Space Management Transaction
SV, Sequence Number Value
TA, Transaction Recovery
TM, DML Enqueue
TS, Temporary Segment (also TableSpace)
TT, Temporary Table
TX, Transaction
UL, User-defined Locks
UN, User Name
US, Undo Segment, Serialization
WL, Being Written Redo Log
XA, Instance Attribute Lock
XI, Instance Registration Lock