AWR报告，很多索引的行级锁冲突。

lsq_008

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: TX - index contention          100,662     192,189   1909   56.0 Concurrenc
enq: US - contention                172,842      64,292    372   18.7      Other
latch: row cache objects            827,074      38,649     47   11.3 Concurrenc
db file sequential read           2,213,102      18,923      9    5.5   User I/O
CPU time                                         14,680           4.3

Segments by Row Lock Waits           DB/Inst: xxxx/xxxx  Snaps: 16921-16922                  
-> % of Capture shows % of row lock waits for each top segment compared                        
-> with total row lock waits for all segments captured by the Snapshot                        
                        
                                                                     Row                        
           Tablespace                      Subobject  Obj.          Lock    % of               
Owner         Name    Object Name            Name     Type         Waits Capture               
---------- ---------- -------------------- ---------- ----- ------------ -------               
xxxxx      xxxxxxxxxx xxxxxxxxxxxxx           INDEX       14,726   38.69                        
xxxxx      xxxxxxxxxx xxxxxxxxxxxxx           INDEX        5,271   13.85                        
xxxxx      xxxxxxxxxx xxxxxxxxxxxxx           INDEX        3,949   10.38                        
xxxxx      xxxxxxxxxx xxxxxxxxxxxxx           INDEX        3,736    9.82                        
xxxxx      xxxxxxxxxx xxxxxxxxxxxxx           INDEX        3,349    8.80                        
                        
这可能是索引块分裂引起的锁等待。

[ 本帖最后由 lsq_008 于 2010-6-22 16:18 编辑 ]

zergduan

原帖由 lsq_008 于 2010-6-22 16:15 发表
.....                    
                        
这可能是索引块分裂引起的锁等待。

? 为什么这么说？

lsq_008

原帖由 zergduan 于 2010-6-22 16:21 发表


? 为什么这么说？

session 1:

SQL> create table t1(x number,y char(20),z date,q varchar2(4000)) tablespace users;

Table created.

SQL> create index t1_idx1 on t1(q,z)  tablespace users;

Index created.

SQL> begin
  2   for x in 1..500 loop
  3       insert into t1 values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
  4   end loop;
  5   end;
  6   /   

PL/SQL procedure successfully completed.

session 2: 与session 1并发执行insert

SQL> begin
  2   for x in 1..500 loop
     insert into t1 values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
end loop;
end;
/      3    4    5    6  

PL/SQL procedure successfully completed.

session 3:

session 1与session 2开始插入前：
SQL> select object_name,STATISTIC_NAME,value from v$segment_statistics where object_name in ('T1','T1_IDX1') and STATISTIC_NAME='row lock waits' order by 1;

OBJECT_NAME                                   STATISTIC_NAME                                                        VALUE
--------------------------------------------- ---------------------------------------------------------------- ----------
T1                                            row lock waits                                                            0
T1_IDX1                                       row lock waits                                                            0

插入后：
SQL> select object_name,STATISTIC_NAME,value from v$segment_statistics where object_name in ('T1','T1_IDX1') and STATISTIC_NAME='row lock waits' order by 1;

OBJECT_NAME                                   STATISTIC_NAME                                                        VALUE
--------------------------------------------- ---------------------------------------------------------------- ----------
T1                                            row lock waits                                                            0
T1_IDX1                                       row lock waits                                                            3

lsq_008

其实这个row lock wait往往与enq: TX - index contention 伴随产生

zergduan

good~!

wei-xh

好厉害！真是学习了。

wei-xh

原帖由 zergduan 于 2010-6-22 14:58 发表
并发更新非唯一索引字段 row lock waits发生在table上，不在索引上~ 可以试验证明~

学习了。

wei-xh

原帖由 lsq_008 于 2010-6-22 16:47 发表
其实这个row lock wait往往与enq: TX - index contention 伴随产生

这个
begin
  for x in 1..2000 loop
      insert into t1 values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
  end loop;
  end;
  /   
我在两个会话同时进行，用10046 level12跟踪一下，产生的等待很多啊。可是我不太同意产生的锁竞争是由于索引块分裂导致的，我一直理解的索引块分裂是由于插入的值处于索引块里键值的中间值，而实验里的数据都是一样大小的。因此在进行的插入的时候，如果索引块容纳不了这个索引键值了，就会找一个新的块来插入，而不会去分裂索引块。
不过查看文档对于enq:TX - index contention的解释，确实是由于索引块分裂导致的。
-------------------------------------------------------------------------------------------------------------
Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX - index contention.
-------------------------------------------------------------------------------------------------------------
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        15        0.24          0.64
  enq: HW - contention                           18        1.69          5.77
  read by other session                           1        0.00          0.00
  buffer busy waits                              35        1.00          5.72
  enq: TX - index contention                     11        0.01          0.03
  enq: TX - contention                            3        2.51          4.54
  rdbms ipc reply                                26        0.30          0.74
  log buffer space                               18        0.41          2.97
  control file sequential read                   95        0.25          2.54
  Data file init write                           45        0.19          1.49
  db file single write                            5        0.01          0.04
  control file parallel write                    15        0.04          0.08
  enq: TX - allocate ITL entry                    2        0.00          0.00
  buffer deadlock                                 3        0.00          0.00
  latch: redo allocation                          1        0.00          0.00
  enq: US - contention                            1        0.00          0.00
  latch: cache buffers lru chain                  1        0.00          0.00
  log file switch completion                      3        0.99          1.22
  log file switch (checkpoint incomplete)         3        1.01          2.60
  latch: cache buffers chains                     1        0.00          0.00

[ 本帖最后由 wei-xh 于 2010-6-22 22:31 编辑 ]

maolinxie

原来这样啊

lsq_008

原帖由 wei-xh 于 2010-6-22 22:09 发表

这个
begin
  for x in 1..2000 loop
      insert into t1 values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
  end loop;
  end;
  /   
我在两个会话同时进行，用10046 level12跟踪一下，产生的等待很多啊。可是我不太同意产生的锁竞争是由于索引块分裂导致的，我一直理解的索引块分裂是由于插入的值处于索引块里键值的中间值，而实验里的数据都是一样大小的。因此在进行的插入的时候，如果索引块容纳不了这个索引键值了，就会找一个新的块来插入，而不会去分裂索引块。
不过查看文档对于enq:TX - index contention的解释，确实是由于索引块分裂导致的。
-------------------------------------------------------------------------------------------------------------
Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX - index contention.
-------------------------------------------------------------------------------------------------------------
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        15        0.24          0.64
  enq: HW - contention                           18        1.69          5.77
  read by other session                           1        0.00          0.00
  buffer busy waits                              35        1.00          5.72
  enq: TX - index contention                     11        0.01          0.03
  enq: TX - contention                            3        2.51          4.54
  rdbms ipc reply                                26        0.30          0.74
  log buffer space                               18        0.41          2.97
  control file sequential read                   95        0.25          2.54
  Data file init write                           45        0.19          1.49
  db file single write                            5        0.01          0.04
  control file parallel write                    15        0.04          0.08
  enq: TX - allocate ITL entry                    2        0.00          0.00
  buffer deadlock                                 3        0.00          0.00
  latch: redo allocation                          1        0.00          0.00
  enq: US - contention                            1        0.00          0.00
  latch: cache buffers lru chain                  1        0.00          0.00
  log file switch completion                      3        0.99          1.22
  log file switch (checkpoint incomplete)         3        1.01          2.60
  latch: cache buffers chains                     1        0.00          0.00

索引块分裂分为90-10和50-50的分裂，当一个索引数据块满了的时候，需要一个新的数据块来容纳新插入的数据，这也是索引块分裂，90-10的分裂