[20241111]在线建立索引hang.txt
--//昨天朋友遇到在线建立索引挂起，实际上原因在于在线建立索引之前有事务一直没有提交，一般正常的事务很快提交，不会导致这样
--//情况，最常见的原因就是开发或者运维在维护查看数据时使用select * from 表 where xxx =1 for update;，然后一直没有提交导
--//致的。找到这个事务，让对方尽快提交就可以，或者kill该进程(小心)。
--//通过简单的测试模拟遇到的情况：
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试环境：
SCOTT@book01p> create table t1 as select rownum id ,lpad('test',10,'0') vc from dual connect by level<=10;
Table created.
--//分析略。
3.测试：
--//session 1:
SCOTT@book01p> select * from t1 where id=10 for update;
ID VC
---------- ----------
10 000000test
--//session 2:
SCOTT@book01p> create index i_t1_id on t1(id) online;
--//挂起
--//session 3:
SCOTT@book01p> update t1 set vc='1111' where id=9;
1 row updated.
SCOTT@book01p> commit ;
Commit complete.
--//在线建立索引并不影响其他会话的DML操作。
SCOTT@book01p> @ ashtop event,p1raw,p2raw,p3raw 1=1 &30s
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P2RAW P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ----------------- ----------------- ----------------- ------------------- ------------------- ---------- -------- -----------
30 1.0 97% | enq: TX - row lock contention 0000000054580004 000000000006000A 0000000000000700 2024-11-11 09:33:34 2024-11-11 09:34:03 1 30 1
1 .0 3% | 2024-11-11 09:33:43 2024-11-11 09:33:43 1 1 1
SCOTT@book01p> @ ev_namepr "enq: TX - row lock contention"
==============================
EVENT# : 311
EVENT_ID : 310662678
NAME : enq: TX - row lock contention
PARAMETER1 : name|mode
PARAMETER2 : usn<<16 | slot
PARAMETER3 : sequence
WAIT_CLASS_ID : 4217450380
WAIT_CLASS# : 1
WAIT_CLASS : Application
DISPLAY_NAME : enq: TX - row lock contention
CON_ID : 0
PL/SQL procedure successfully completed.
--//P1RAW=0000000054580004, 5458 ascii转换成字符 TX，4表示名字空间，这里指索引。
SYS@book> @ nmsp '' 4
@ nmsp table -1
@ nmsp '' 74 or @ nmsp '' 0x4a|x4a
KGLSTDSC KGLSTIDN KGLSTIDN_HEX
--------- -------- ------------
INDEX 4 4
--//000000000006000A = /2^16 %2^16 (Type | Mode) = 6,10 = 393226
--//P2RAW,P3RAW 结合在一起计算事务的xid。 6 10 0x700 = 1792。
SYS@book> @ slottoxid 6 10 1792
XID
------------------------------
06000A0000070000
SYS@book> @ trans
SYS@book> @ pr
==============================
SID : 278
SERIAL# : 15758
TRANS_USERNAME : SCOTT
TADDR : 00000000771010C8
SES_ADDR : 000000007B8446A8
USED_UBLK : 1
USED_UREC : 1
0xFLAG : 201603
STATUS : ACTIVE
STATUS2 : ACTIVE
START_DATE : 2024-11-11 09:30:50
XIDUSN : 5
XIDSLOT : 6
XIDSQN : 1786
XID : 05000600FA060000
PRV_XID : 0000000000000000
PTX_XID : 0000000000000000
SQL_ID : 80vc7tpygj5ht
PREV_SQL_ID : 73qjnzj3tn4xd
==============================
SID : 396
SERIAL# : 57454
TRANS_USERNAME : SCOTT
TADDR : 000000007716ED18
SES_ADDR : 000000007B974A88
USED_UBLK : 1
USED_UREC : 1
0xFLAG : E03
STATUS : ACTIVE
STATUS2 : ACTIVE
START_DATE : 2024-11-11 09:30:22
XIDUSN : 6
XIDSLOT : 10
XIDSQN : 1792
XID : 06000A0000070000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRV_XID : 0000000000000000
PTX_XID : 0000000000000000
SQL_ID :
PREV_SQL_ID : d3dwa6w0p7tsq
PL/SQL procedure successfully completed.
SYS@book> @ wcx &30s
-- Display ASH Wait Chain Signatures script v0.7 by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN
------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------------- ------------------- -------------------
67% 30 1 -> 396,57454,@1=>278,15758,@1=>enq: TX - row lock contention -> [idle blocker 1,396,57454 (sqlplus@centtest (TNS V1-V3))] 2024-11-11 09:42:23 2024-11-11 09:42:52
22% 10 .3 -> ,,@=>133,45900,@1=> 2024-11-11 09:42:23 2024-11-11 09:42:52
9% 4 .1 -> ,,@=>4,28938,@1=> 2024-11-11 09:42:23 2024-11-11 09:42:49
2% 1 0 -> ,,@=>1,16109,@1=> 2024-11-11 09:42:32 2024-11-11 09:42:32
--//sid=396阻塞了sid=278.
SYS@book> @ sql_id d3dwa6w0p7tsq
-- SQL_ID = d3dwa6w0p7tsq come from shared pool
select * from t1 where id=10 for update;
--//注:真实的环境可能看不见这条sql语句，因为session 1可以继续执行其他sql语句。
SCOTT@book01p> @ sql_id 80vc7tpygj5ht
-- SQL_ID = 80vc7tpygj5ht come from shared pool
create index i_t1_id on t1(id) online;
--//只要sid=396提交事务就可以了,正常情况下找到该工位的维护人员，催促他尽快提交事务。真实的环境可能要kill该事务，小心!!
--//session 3:
SCOTT@book01p> insert into t1 values ( 11,'0000');
1 row created.
--//session 3插入1条记录，不提交。
--//session 1:
SCOTT@book01p> commit ;
Commit complete.
--//session 2:
--//依旧挂起的状态，因为session 3插入一条记录没有提交，正常情况业务操作很快提交。
SCOTT@book01p> @ wcx &30s
-- Display ASH Wait Chain Signatures script v0.7 by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN
------ ---------- ------- ----------------------------------------------------------------------------------------------------------------------- ------------------- -------------------
94% 30 1.0 -> 19,41808,@1=>278,15758,@1=>enq: TX - row lock contention -> [idle blocker 1,19,41808 (sqlplus@centtest (TNS V1-V3))] 2024-11-11 09:54:23 2024-11-11 09:54:52
3% 1 .0 -> ,,@=>387,6899,@1=>control file parallel write 2024-11-11 09:54:29 2024-11-11 09:54:29
3% 1 .0 -> ,,@=>271,4617,@1=> 2024-11-11 09:54:28 2024-11-11 09:54:28
--//现在sid=19阻塞了sid=278.
--//session 3:
SCOTT@book01p> commit ;
Commit complete.
--//session 2：
SCOTT@book01p> create index i_t1_id on t1(id) online;
Index created.
--//索引建立完成。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3048530/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
