--1,测试用表
drop table mh;
create table mh(x int,y int);
create index idx_mh_xy on mh(x,y);
insert into mh(x,y) values(1,1);
insert into mh(x,y) values(2,2);
insert into mh(x,y) values(3,3);
commit;
--2,创建新索引
create index idx_mh_xy2 on mh(y,x);
SQL> select index_name,column_name,column_position from user_ind_columns where table_name='MH';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------ -------------- ---------------
IDX_MH_XY X 1
IDX_MH_XY Y 2
IDX_MH_XY2 Y 1
IDX_MH_XY2 X 2
==>允许相同列以不同顺序创建索引
drop index idx_mh_xy2;
------------------------------------------------------------------------------------------------------------------------------------------------------
--3，联机情况
3.1 Oracle10.2.0.1情况
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
--会话1(SID=513)：
SQL> delete from mh where x=1;
已删除1行。
--会话2(SID=747)：
SQL> create index idx_mh_xy2 on mh(y,x);
create index idx_mh_xy2 on mh(y,x)
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
create index idx_mh_xy2 on mh(y,x) online parallel 4 nologging;
--挂起
--会话3(SID=511)：
SQL> delete from mh where x=2;
--挂起
--会话4：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(513,747,511);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ---------------------------- ---------- ---------- ---------- ----------------
513 SQL*Net message from client 1413697536 1 0
747 enq: TM - contention 1414332420 225988 0 513
511 enq: TM - contention 1414332419 225988 0 747
==>创建索引阻塞了后续dml操作!!!
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(513,747,511) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
513 TX 1441805 9808 6 0 183 0
513 TM 225988 0 3 0 183 1
747 TX 524306 69840 6 0 135 0
747 DL 225988 0 3 0 135 0
747 DL 225988 0 3 0 135 0
747 TM 225988 0 2 4 135 0
747 TM 225991 0 4 0 134 0
511 TM 225988 0 0 3 75 0
8 rows selected
SQL> select object_name,object_id,object_type from dba_objects where object_id in(225988,225991);
OBJECT_NAME OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
MH 225988 TABLE
SYS_JOURNAL_225990 225991 TABLE
--会话1(SID=513)：
commit;
--会话4：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(513,747,511);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ----------------------------- ---------- ---------- ---------- ----------------
513 SQL*Net message from client 1413697536 1 0
747 enq: TM - contention 1414332420 225988 0 511
511 SQL*Net message from client 1413697536 1 0
==>747又被511阻塞!!!
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(513,747,511) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
747 DL 225988 0 3 0 745 0
747 TX 524306 69840 6 0 745 0
747 DL 225988 0 3 0 745 0
747 TM 225991 0 4 0 744 0
747 PS 1 4 4 0 401 0
747 PS 1 6 4 0 401 0
747 PS 1 7 4 0 401 0
747 TM 225988 0 2 4 401 0
511 TM 225988 0 3 0 401 1
747 PS 1 3 4 0 401 0
747 PS 1 2 4 0 401 0
747 PS 1 1 4 0 401 0
747 PS 1 0 4 0 401 0
747 PS 1 5 4 0 401 0
511 TX 196616 68422 6 0 401 0
747 TS 8 46265836 4 0 400 0
16 rows selected
--会话3(SID=511)：
SQL> commit;
--会话2(SID=747)：
SQL> create index idx_mh_xy2 on mh(y,x) online parallel 4 nologging;
索引已创建。
SQL> drop index idx_mh_xy2;
索引已删除。
3.1 Oracle11.2.0.3情况
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--会话1(SID=5649)：
SQL> delete from mh where x=1;
已删除1行。
--会话2(SID=5857)：
SQL> create index idx_mh_xy2 on mh(y,x);
create index idx_mh_xy2 on mh(y,x)
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> create index idx_mh_xy2 on mh(y,x) online parallel 4 nologging;
--挂起
--会话3(SID=6078)：
SQL> delete from mh where x=2;
已删除 1 行。
==>此处没有挂起！与10g不同。
--会话4：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(5649,5857,6078);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ------------------------------- ---------- ---------- ---------- ----------------
5649 SQL*Net message from client 1413697536 1 0
5857 enq: TX - row lock contention 1415053316 21299232 6156 5649
6078 SQL*Net message from client 1413697536 1 0
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(5649,5857,6078) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
5649 AE 100 0 4 0 1674 2
5857 AE 100 0 4 0 1655 2
6078 AE 100 0 4 0 1647 2
5649 TX 21299232 6156 6 0 79 1
5649 TM 87222 0 3 0 79 2
5857 TX 165412871 23996 6 0 68 2
5857 TM 87222 0 2 0 68 2
5857 TM 87225 0 4 0 68 2
5857 DL 87222 0 3 0 67 2
5857 TX 21299232 6156 0 4 67 0
5857 DL 87222 0 3 0 67 2
5857 OD 87222 0 4 0 67 2
6078 TX 125501448 5507 6 0 56 2
6078 TM 87222 0 3 0 56 2
14 rows selected
SQL> select object_name,object_id,object_type from dba_objects where object_id in(87222,87225);
OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------- ---------- -------------------
MH 87222 TABLE
SYS_JOURNAL_87224 87225 TABLE
--会话1(SID=5649)：
commit;
--会话4：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(5649,5857,6078);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ------------------------------- ---------- ---------- ---------- ----------------
5649 SQL*Net message from client 1413697536 1 0
5857 enq: TX - row lock contention 1415053316 125501448 5507 6078
6078 SQL*Net message from client 1413697536 1 0
==>5857又被6078阻塞!!!
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(5649,5857,6078) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
5649 AE 100 0 4 0 1910 2
5857 AE 100 0 4 0 1891 2
6078 AE 100 0 4 0 1883 2
5857 TM 87225 0 4 0 304 2
5857 TX 165412871 23996 6 0 304 2
5857 TM 87222 0 2 0 304 2
5857 DL 87222 0 3 0 303 2
5857 OD 87222 0 4 0 303 2
5857 DL 87222 0 3 0 303 2
6078 TM 87222 0 3 0 292 2
6078 TX 125501448 5507 6 0 292 1
5857 TX 125501448 5507 0 4 73 0
12 rows selected
--会话3(SID=6078)：
SQL> commit;
提交完成。
--会话2(SID=5857)：
SQL> create index idx_mh_xy2 on mh(y,x) online parallel 4 nologging;
索引已创建。
--会话4：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(5649,5857,6078);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ----------------------------- ---------- ---------- ---------- ----------------
5649 SQL*Net message from client 1413697536 1 0
5857 SQL*Net message from client 1413697536 1 0
6078 SQL*Net message from client 1413697536 1 0
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(5649,5857,6078) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
5649 AE 100 0 4 0 1998 2
5857 AE 100 0 4 0 1979 2
6078 AE 100 0 4 0 1971 2
5857 TO 68090 1 3 0 29 2
SQL> drop index idx_mh_xy2;
索引已删除。
------------------------------------------------------------------------------------------------------------------------------------------------------
--4，修改索引属性
--会话1(SID=618)：
SQL> delete from mh;
已删除0行。
--会话2(SID=821)：
SQL> alter index idx_mh_xy2 logging noparallel;
alter index idx_mh_xy2 logging noparallel
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> alter index idx_mh_xy2 rebuild online logging noparallel;
--被挂起
--会话3：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(618,821);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ----------------------------- ---------- ---------- ---------- ----------------
618 SQL*Net message from client 1413697536 1 0
821 enq: TM - contention 1414332420 225713 0 618
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(618,821) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
618 TM 225713 0 3 0 907 1
821 DL 225713 0 3 0 874 0
821 TX 852003 29648 6 0 874 0
821 TM 225713 0 2 4 874 0
821 TM 225720 0 4 0 874 0
821 DL 225713 0 3 0 874 0
6 rows selected
SQL> select object_name,object_id,object_type from dba_objects where object_id in(225713,225720);
OBJECT_NAME OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
MH 225713 TABLE
SYS_JOURNAL_225716 225720 TABLE
--会话1(SID=618)：
commit;
--会话2(SID=821)：
SQL> alter index idx_mh_xy2 rebuild online logging noparallel;
索引已更改。
--5，删除索引
--会话1(SID=618)：
SQL> delete from mh;
已删除0行。
--会话2(SID=821)：
SQL> drop index idx_mh_xy;
drop index idx_mh_xy
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
--删除只能通过不断尝试完成。
--会话1(SID=618)：
SQL>commit;
SQL>drop index idx_mh_xy;
--6，最近有其他产品在批量里面使用并行DML，影响联机交易
--6.1 DML阻塞并行DML
--会话1(SID=618)：
SQL> delete from mh;
已删除0行。
--会话2(SID=821)：
alter session enable parallel dml;
delete /*+ parallel(t 8) */ from mh t;
--会话3：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(618,821);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ----------------
618 SQL*Net message from client 1413697536 1 0
821 enq: TM - contention 1414332422 225713 0 618
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(618,821) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
618 TM 225713 0 3 0 282 1
821 TM 225713 0 0 6 48 0
SQL> select object_name,object_id,object_type from dba_objects where object_id in(225713);
OBJECT_NAME OBJECT_ID OBJECT_TYPE
--------------- ---------- -------------------
MH 225713 TABLE
--会话1(SID=618)：
commit;
--会话2(SID=821)：
SQL> delete /*+ parallel(t 8) */ from mh t;
已删除0行。
--会话3：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(618,821);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ---------------------------- ---------- ---------- ---------- ----------------
618 SQL*Net message from client 1413697536 1 0
821 SQL*Net message from client 1413697536 1 0
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(618,821) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
821 PS 1 0 4 0 63 0
821 TX 1703956 4693 6 0 63 0
821 PS 1 2 4 0 63 0
821 PS 1 3 4 0 63 0
821 PS 1 4 4 0 63 0
821 PS 1 5 4 0 63 0
821 PS 1 6 4 0 63 0
821 PS 1 7 4 0 63 0
821 PS 1 8 4 0 63 0
821 PS 1 9 4 0 63 0
821 PS 1 10 4 0 63 0
821 PS 1 11 4 0 63 0
821 PS 1 12 4 0 63 0
821 PS 1 13 4 0 63 0
821 PS 1 14 4 0 63 0
821 PS 1 15 4 0 63 0
821 TM 225713 0 6 0 63 0
821 PS 1 1 4 0 63 0
18 rows selected
--会话2(SID=821)：
commit;
--6.2 并行DML阻塞DML
--会话2(SID=821)：
alter session enable parallel dml;
delete /*+ parallel(t 8) */ from mh t;
--会话1(SID=618)：
delete from mh;
--被阻塞
--会话3：
SQL> select sid,event,p1,p2,p3,blocking_session from v$session where sid in(618,821);
SID EVENT P1 P2 P3 BLOCKING_SESSION
---------- ----------------------------- ---------- ---------- ---------- ----------------
618 enq: TM - contention 1414332419 225713 0 821
821 SQL*Net message from client 1413697536 1 0
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(618,821) order by ctime desc;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
821 PS 1 0 4 0 82 0
821 TX 786470 29287 6 0 82 0
821 PS 1 2 4 0 82 0
821 PS 1 3 4 0 82 0
821 PS 1 4 4 0 82 0
821 PS 1 5 4 0 82 0
821 PS 1 6 4 0 82 0
821 PS 1 7 4 0 82 0
821 PS 1 8 4 0 82 0
821 PS 1 9 4 0 82 0
821 PS 1 10 4 0 82 0
821 PS 1 11 4 0 82 0
821 PS 1 12 4 0 82 0
821 PS 1 13 4 0 82 0
821 PS 1 14 4 0 82 0
821 PS 1 15 4 0 82 0
821 TM 225713 0 6 0 82 1
821 PS 1 1 4 0 82 0
618 TM 225713 0 0 3 50 0
19 rows selected
--会话2(SID=821)：
commit;
--会话1(SID=618)：
SQL> delete from mh;
已删除0行。
SQL> commit;
提交完成。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18922393/viewspace-1069659/ ，如需转载，请注明出处，否则将追究法律责任。
