DML锁
一、参数
SQL> show parameter dml
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------
adg_redirect_dml boolean FALSE
dml_locks integer 8676 --DML锁数量SQL> alter system set dml_locks=0;
alter system set dml_locks=0
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set dml_locks=0 scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2466249672 bytes
Fixed Size 8899528 bytes
Variable Size 905969664 bytes
Database Buffers 1543503872 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0
Process ID: 37780
Session ID: 1014 Serial number: 2145219C不让设置为0了。
二、enq: TX - contention
2.1、参数
- P1 = name|mode
- P2 = object #
- P3 = table/partition --0为表，1为分区
2.2、测试
SQL> select * from v$mystat where rownum=1; --session 23
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
23 0 0 0
SQL> lock table t in exclusive mode;
Table(s) Locked.
SQL> select * from v$mystat where rownum=1; --session 1061
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
1061 0 0 0
SQL> lock table t in exclusive mode;
SQL> select * from v$mystat where rownum=1; --session 1042
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
1042 0 0 0
SQL> select event,sid,sql_id,p1,p1raw，p2,p2raw,p3 ,p3raw from v$session where sid in ('23','1061');
EVENT SID SQL_ID P1 P1RAW P2 P2RAW P3 P3RAW
------------------------------ ---------- --------------------------------------- ---------- ---------------- ---------- ---------------- ---------- ----------------
SQL*Net message from client 23 1650815232 0000000062657100 1 0000000000000001 0 00
enq: TM - contention 1061 d2xpd5bgm8uch 1414332422 00000000544D0006 76593 0000000000012B31 0 00
SQL> select * from v$lock where sid in (23,1061);
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000008C16AF98 000000008C16AFC8 23 AE 134 0 4 0 796 2 0
000000008C16C400 000000008C16C430 1061 AE 134 0 4 0 796 2 0
00007F3D06D014F8 00007F3D06D01520 23 TM 76593 0 6 0 740 1 0
00007F3D06D014F8 00007F3D06D01520 1061 TM 76593 0 0 6 732 0 02.3、解释
P1:1414332422 P1RAW:00000000544D0006
SQL> select to_char(1414332422,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(1414332422,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
--------------------------------------------------------------------------------
544d0006
P1和P1RAW分别为十进制和16进制。
0x0006为十进制6 对应排他锁
544d-->0x54 0x4d -->84 77(十进制)-->T M(ASCII对照表)P2:76593 P2RAW:0000000000012B31
SQL> select to_char(76593,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(76593,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
-------------------------------------------------------------------------------
12b31
SQL> select object_name from dba_objects where object_id='76593';
OBJECT_NAME
--------------------
T注：v$session中的P2、P3对用v$lock的ID1、ID2
2.4、脚本
sELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request,l.id1,l.id2
FROM
( SELECT p2, p3, 'TM-'||substr(p2raw,-8)||'-'||lpad(p3,8,'0') TM
FROM v$session_wait
WHERE event='enq: TM - contention'
and state='WAITING'
) w,
gv$lock L
WHERE l.type(+)='TM'
and l.id1(+)=w.p2
and l.id2(+)=w.p3
ORDER BY tm, lmode desc, request desc
;
TM OBJECT_ID INST_ID SID LMODE REQUEST ID1 ID2
---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TM-00012B31-00000000 76593 1 1030 6 0 76593 0
TM-00012B31-00000000 76593 1 1013 0 6 76593 02.5、等待链
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/diag/rdbms/erpdb/erpdb1/trace/erpdb1_ora_46602.trcSQL> !vi /u01/app/oracle/diag/rdbms/erpdb/erpdb1/trace/erpdb1_ora_46602.trc
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (erpdb.erpdb1)
os id: 46598
process id: 79, oracle@erp51 (TNS V1-V3)
session id: 1013
session serial #: 5121
module name: 0 (sqlplus@erp51 (TNS V1-V3))
}
is waiting for 'enq: TM - contention' with wait info:
{
p1: 'name|mode'=0x544d0006
p2: 'object #'=0x12b31
p3: 'table/partition'=0x0
time in wait: 3 min 24 sec
timeout after: never
wait id: 40
blocking: 0 sessions
current sql_id: 3514215722
current sql: lock table t in exclusive mode
short stack: ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-semtimedop()+10<-skgpwwait()+187<-ksliwat()+2218<-kslwaitctx()+188<-kjusuc()+4689<-ksipgetctxia()+2081<-ksqcmi()+2757<-ksqgtlctx()+6620<-ktaiam()+719<-ktagetg0()+929<-ktagetp_internal()+141<-ktagetg_internal()+67<-kkbkexe()+210<-opiexe()+11890<-kpoal8()+2387<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1895<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
wait history:
* time between current wait and wait #1: 0.007966 sec
1. event: 'SQL*Net message from client'
time waited: 36.446112 sec
wait id: 39 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000019 sec
2. event: 'SQL*Net message to client'
time waited: 0.000002 sec
wait id: 38 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000032 sec
3. event: 'SQL*Net message from client'
time waited: 0.003336 sec
wait id: 37 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (erpdb.erpdb1)
os id: 46597
process id: 55, oracle@erp51 (TNS V1-V3)
session id: 1030
session serial #: 49910
module name: 0 (sqlplus@erp51 (TNS V1-V3))
}
which is waiting for 'SQL*Net message from client' with wait info:
{
p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
time in wait: 3 min 3 sec
timeout after: never
wait id: 137
blocking: 1 session
current sql_id: 0
current sql: <none>
short stack: ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-read()+14<-sntpread()+28<-ntpfprd()+126<-nsbasic_brc()+399<-nioqrc()+438<-opikndf2()+999<-opitsk()+905<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
wait history:
* time between current wait and wait #1: 0.000048 sec
1. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 136 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000028 sec
2. event: 'SQL*Net message from client'
time waited: 0.013977 sec
wait id: 135 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000010 sec
3. event: 'PGA memory operation'
time waited: 0.000256 sec
wait id: 134
}
Chain 1 Signature: 'SQL*Net message from client'<='enq: TM - contention'
Chain 1 Signature Hash: 0x163c4cba三、enq: TX - row lock contention
TX Lock“Transaction Enqueue”用于在事务执行时维护事务的完整性，防止其他会话同时修改相同的数据
3.1、参数
- P1 = name|mode
- P2 = usn<<16 | slot
- P3 = sequence
3.2、测试
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
49 0 0 0
SQL> update t set object_name ='t' where object_id=3;
1 row updated.
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
1013 0 0 0
SQL> update t set object_name ='t' where object_id=3;
SQL> select event,sid,sql_id,p1,p1raw，p2,p2raw,p3 ,p3raw from v$session where sid in ('49','1013');
EVENT SID SQL_ID P1 P1RAW P2 P2RAW P3 P3RAW
------------------------------ ---------- --------------------------------------- ---------- ---------------- ---------- ---------------- ---------- ----------------
SQL*Net message from client 49 1650815232 0000000062657100 1 0000000000000001 0 00
enq: TX - row lock contention 1013 fc2r6f784gbnr 1415053318 0000000054580006 458781 000000000007001D 2045 00000000000007FD
SQL> select * from v$lock where type in ('TM','TX');
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000008C16ADF8 000000008C16AE28 1013 TX 458781 2045 0 6 187 0 0
00007F8E7065C6F0 00007F8E7065C718 49 TM 77743 0 3 0 238 2 0
00007F8E7065C6F0 00007F8E7065C718 1013 TM 77743 0 3 0 187 2 0
000000007E399E48 000000007E399E80 49 TX 458781 2045 6 0 238 1 03.3、解释
TM ID1:object id ID2:0表示表，1表示分区表
P1、P1RAW
SQL> select to_char(1415053318,'xxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(1415053318,'XXXXXXXXXXXXXXXXXXXXXX')
---------------------------------------------------------------------
54580006
5458 --> 0x54 0x58-->84(T) 88(X) -->TX
0x0006-->6 6级锁
P2、P2RAW
SQL> select to_char(458781,'xxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(458781,'XXXXXXXXXXXXXXXXXXXXXX')
---------------------------------------------------------------------
7001d
0x0007-->7:回滚段号 0x001d->29：XIDSLOT 事务槽号
select ADDR,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,NAME from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK NAME
---------------- ---------- ---------- ---------- ---------- ---------- ------------------------------
000000007E399E48 7 29 2045 4 4292
P3/P3RAW 2045 00000000000007FD
SQL> select to_char(2045,'xxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(2045,'XXXXXXXXXXXXXXXXXXXXXX')
---------------------------------------------------------------------
7fdXIDUSN 回滚段
XIDSLOT 事务槽号
UBABLK 前镜像的数据块地址
UBAFIL 前镜像的文件号
USED_UREC 不断增加，说明该事物正在继续，如果该字段不断下降，说明该事物正在回滚。select sid,serial#,username,sql_id,event,ROW_WAIT_OBJ#, ROW_WAIT_FILE#,BLOCKING_SESSION,BLOCKING_SESSION_STATUS from v$session where saddr=(select ses_addr from v$transaction where XIDUSN=7);
SID SERIAL# USERNAME SQL_ID EVENT ROW_WAIT_OBJ# ROW_WAIT_FILE# BLOCKING_SESSION BLOCKING_SESSION_STATUS
---------- ---------- ------------------------------ ---------- ------------------------------ ------------- -------------- ---------------- ---------------------------------
49 32228 SYS SQL*Net message from client 77743 1 UNKNOWN
SQL> select object_id,object_name from dba_objects where object_id='77743';
OBJECT_ID OBJECT_NAME
---------- --------------------
77743 TSQL> select * from v$rollname a where a.usn=7;
USN NAME CON_ID
---------- ------------------------------ ----------
7 _SYSSMU7_2329891355$ 0
SQL> select * from v$rollstat where usn=7;
USN LATCH EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------- ---------- ---------- ----------
7 1 5 3268608 20362 1 190 0 3268608 0 0 0 0 0 ONLINE 3 68 0SQL>
SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session
WHERE event='enq: TX - row lock contention'
4 AND state='WAITING'
5 /
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
77743 1 116217 9SQL> alter system dump datafile 1 block 116217;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
[oracle@erp51:/home/oracle]$vi /u01/app/oracle/diag/rdbms/erpdb/erpdb1/trace/erpdb1_ora_78179.trc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000000000b59ce4
0x02 0x0007.01d.000007fd 0x010010c4.021d.05 ---- 1 fsc 0x0005.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
tab 0, row 9, @0x9d
tl: 89 fb: --H-FL-- lb: 0x2 cc: 22
col 0: [ 3] 53 59 53 -->83 89 83 -->SYS
col 1: [ 1] 74-->116-->t-->这个值竟然是未提交的值。
col 2: *NULL* -->null
col 3: [ 2] c1 04 --> 猜测c1为数字标识符，然后4-1表示数字
col 4: [ 2] c1 04
col 5: [ 5] 49 4e 44 45 58-->73 78 68 69 88-->I N D E X
col 6: [ 7] 78 77 04 11 01 39 0c -->120 119 4 17 1 57 16-->是否可以认为这个未时间标识符
col 7: [ 7] 78 77 04 11 01 39 0c
col 8: [19] 32 30 31 39 2d 30 34 2d 31 37 3a 30 30 3a 35 36 3a 31 31
-->50 48 49 57 45 48 52 45 49 55 58 48 48 58 53 54 58 49 49
--> 2 0 1 9 - 0 4 - 1 7 : 0 0 : 5 6 : 1 1
col 9: [ 5] 56 41 4c 49 44-->86 65 76 73 68-->V A L I D
col 10: [ 1] 4e -->78-->N
col 11: [ 1] 4e -->78-->N
col 12: [ 1] 4e -->78-->N
col 13: [ 2] c1 05-->4
col 14: *NULL* -->null
col 15: [ 4] 4e 4f 4e 45-->78 79 78 69--> N O N E
col 16: *NULL*--null
col 17: [ 1] 59 -->89-->Y
col 18: [ 1] 4e -->78-->N
col 19: *NULL* -->null
col 20: [ 1] 4e -->N
col 21: [ 1] 4e -->N
末尾4列为空竟然不储存。如果导致行争用的事件序列不清楚，那么可以使用 LogMiner 来获取有关事务以什么顺序执行的详细信息。
检查 AWR 的“Segments by Row Lock Waits”部分以获取有关导致高等待的对象的线索
使用 ADDM 报告来识别系统范围内高 TX 等待所涉及的 SQL 和会话。
深入了解显示高等待的单个 SQL 或会话，以了解等待中涉及哪些应用程序代码、哪些对象以及哪些 SQL。
ASH 报告也可以提供帮助显示高“enq：TX - 行锁争用”等待时间的会话的。
‘enq: TX - row lock contention’ 是数据库时间的重要组成部分
“CPU 繁忙时间”不大于“CPU 总时间”的 80%
在本地数据库中花费的时间很长
只有某些会话、查询或作业出现缓慢（不是整个数据库）
如果等待时间很短，这往往表明所涉及的对象的并发性很高，并且锁正在保护多个用户输入的数据。
3.4、脚本
SQL> SELECT
2 sid, seq#, state, seconds_in_wait,
3 'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0') ,8,'0') TX,
4 trunc(p2/65536) XIDUSN,
5 trunc(mod(p2,65536)) XIDSLOT,
6 p3 XIDSQN
7 FROM v$session_wait
8 WHERE event='enq: TX - row lock contention'
9 ;
SID SEQ# STATE SECONDS_IN_WAIT TX XIDUSN XIDSLOT XIDSQN
---------- ---------- ------------------------------ --------------- ------------------------------ ---------- ---------- ----------
1013 2445 WAITING 3264 TX-0007001D-000007FD 7 29 2045
SQL> SELECT distinct w.tx, l.inst_id, l.sid, l.lmode, l.request
2 FROM
3 ( SELECT p2,p3,
4 'TX-'||lpad(ltrim(p2raw,'0'),8,'0' )||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX
5 FROM v$session_wait
6 WHERE event='enq: TX - row lock contention'
7 and state='WAITING'
8 ) W ,
9 gv$lock L
10 WHERE l.type(+)='TX'
11 and l.id1(+)=w.p2
12 and l.id2(+)=w.p3
13 ORDER BY tx, lmode desc, request desc
14 /
TX INST_ID SID LMODE REQUEST
------------------------------ ---------- ---------- ---------- ----------
TX-0007001D-000007FD 1 49 6 0
TX-0007001D-000007FD 1 1013 0 6四、enq: TX - index contention
4.1、说明
在运行 OLTP 系统时，可能会在与表相关联的索引上看到高 TX 排队争用，这些表具有来自应用程序的高并发性。这通常发生在应用程序同时执行大量 INSERT 和 DELETE 时。对于 RAC 系统，并发 INSERT 和 DELETE 可能发生在所有实例中。
原因是索引块在向索引中插入新行时拆分。事务将不得不等待模式 4 中的 TX 锁定，直到正在执行块拆分的会话完成操作。当会话在需要插入新行的索引块中找不到空间时，会话将启动索引块拆分。在开始拆分之前，它会清理块中的所有键以检查块中是否有足够的空间。
删除拆分器必须执行以下活动：
- 分配一个新块。
- 将一定百分比的行复制到新缓冲区。
- 将新缓冲区添加到索引结构并提交操作。
在 RAC 环境中，由于包含全局缓存操作，这可能是一项昂贵的操作。如果拆分发生在分支或根块级别，影响会更大。
最可能的原因是：
- 应用程序大量访问的表上的索引。
- 单调增长的表列上的索引。换句话说，大多数索引插入只发生在索引的右边缘。
- 已执行大数据清除，随后是高并发插入
Top 5 Timed Events:
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
en: TX - index contention 89,350 40,991 459 63.3 Concurrency
db file sequential read 1,458,288 12,562 9 19.4 User I/O
CPU time 5,352 8.3
Instance Activity Stats:
Statistic Total per Second per Trans
branch node splits 945 0.26 0.00
leaf node 90-10 splits 1,670 0.46 0.00
leaf node splits 35,603 9.85 0.05
objects can be found either from V$SEGMENT_STATISTICS or from 'Segments by Row Lock Waits' or 'Segments by ITL Waits' or 'Service ITL Waits' of the AWR reports.
Segments by Row Lock Waits:
Owner Tablespace Object Name Obj.Type Row Lock Waits % of Capture
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_PK INDEX 3,425 43.62
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_ST INDEX 883 11.25
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_DT INDEX 682 8.69
Segments by ITL Waits
Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits % of Capture
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_PK INDEX 6 50.00
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_ST INDEX 3 25.00
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_DT INDEX 3 25.00Solutions:
Solution here is to tune the indexes avoid heavy access on a few set of blocks.
Following are the options we could try:
Rebuild the index as reverse key indexes or hash partition the indexes which are listed in the 'Segments by Row Lock Waits' of the AWR reports
For example:
CREATE INDEX <index name> ON <column> REVERSE;清除大量数据后重建或收缩关联索引
如果已完成大量数据清除（删除），重建或收缩关联索引应该通过 alter index rebuild 或 alter index shrink 命令有助于减少等待
五、enq: TX - allocate ITL entry
默认情况下，表的 INITRANS 值为 1，索引为 2。这定义了一个称为事务列表 (ITL) 的内部块结构。为了修改块中的数据，
进程需要使用一个空的 ITL 槽来记录事务对修改块中的某些数据。如果空闲 ITL 插槽不足，则将在块中保留的空闲空间中占用新的 ITL 插槽。
如果用完并且太多的并发 DML 事务正在竞争同一个数据块，出现以下等待事件的争用 - “enq: TX - allocate ITL entry”。
处理方式
此问题的主要解决方案是通过重新创建表或索引并更改 INITRANS 或 PCTFREE 参数以能够处理更多并发事务来增加表或索引的 ITL 功能。
这反过来将有助于减少“enq: TX - allocate ITL entry”等待事件。
1 增加 INITRANS
1) 根据表中事务的数量，需要更改 INITRANS 的值。
alter table <table name> INITRANS 50;
2) 然后使用 move 重新组织表
alter table <table_name> move;
3) 然后重建该表的所有索引，如下所示
alter index <index_name> rebuild INITRANS 50;2 增加PCTFREE
如果增加 INITRANS 不能解决问题，尝试增加 PCTFREE。增加 PCTFREE 会保留更多空间，
因此会将相同数量的行分布在更多块上。这意味着总体上有更多的 ITL 插槽可用。
1）将行分散到更多的块中也将有助于减少这种等待事件。
alter table <table_name> PCTFREE 20；
2）然后使用move重新组织表
alter table <table_name> move;
3) 重建索引
alter index index_name rebuild online PCTFREE 20;3 增加 INITRANS 和 PCTFREE 的组合
1) 将 INITRANS 设置为 50 并将 pct_free 设置为 20
alter table <table_name> PCTFREE 20 INITRANS 50；
2) 使用 move 重新组织表
alter table <table_name> move;
3) 然后重建表的所有索引，如下所示
alter index <index_name> rebuild online PCTFREE 20 INITRANS 50；