Ask Maclean

【Oracle Database 12c新特性】DROP Index/CONSTRAINT ONLINE

12c中提供了DROP Index/CONSTRAINT ONLINE的新特性， ONLINE意味着在drop index期间对于表或分区的DML操作不被阻塞。

DROP INDEX ONLINE对于分区索引或者非分区索引均可用。

 

在12c drop index online之前普通的drop index操作会可能导致短暂的enq: TM 等待，原因是

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> 
SQL> create table DML_ONLINE as select * from dba_tables;

Table created.

SQL> create index idx_name on DML_ONLINE(table_name);

Index created.

SQL> alter session set events '10704 trace name context forever , level 10';

Session altered.

SQL> drop index idx_name ;

Index dropped.

SQL> select object_id from dba_objects where object_name='DML_ONLINE';

 OBJECT_ID
----------
     97302

	97302=>17C16 

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/s01/admin/G10R25/udump/g10r25_ora_7007.trc

[oracle@vrh8 ~]$ grep "ksqgtl \*\*\*" /s01/admin/G10R25/udump/g10r25_ora_7007.trc
ksqgtl *** TX-00080027-000021d4 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TM-00017c16-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TM-00000318-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** CU-92e387e8-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl *** TM-0000023a-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000049-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000004a-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000004b-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000004c-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000002d8-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000039-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** CU-92e37c98-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl *** CU-92e37c98-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl *** TM-00000014-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** CU-92e377a8-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl *** CU-92e377a8-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TS-00000000-00414641 mode=6 flags=0x11 timeout=0 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010014-00000001 mode=6 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010014-00000002 mode=1 flags=0x11 timeout=21474836 ***
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00010018-00001cbc mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TX-00030006-00002417 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CI-00000001-00000005 mode=6 flags=0x10 timeout=21474836 ***
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** CU-92e372b8-00000000 mode=6 flags=0x10 timeout=300 ***

97302=>17C16  ksqgtl *** TM-00017c16-00000000 mode=6   这里可以看到 drop index 期间拿了对应表的TM mode=6 exclusive mode table lock

 

 

 

 

如果使用12c 中的drop index Online则：

 

 

 

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table DML_ONLINE as select * from dba_tables;

表已创建。

SQL> create index idx_name on DML_ONLINE(table_name);

索引已创建。

SQL> alter session set events '10704 trace name context forever , level 10';

会话已更改。

SQL> drop index idx_name online ;

索引已删除。

SQL> select object_id from dba_objects where object_name='DML_ONLINE';

 OBJECT_ID
----------
    117648

SQL>  oradebug setmypid
已处理的语句
SQL> oradebug tracefile_name
maclean_ora_1212.trc

[oracle@vrh8 ~]$ grep "ksqgtl \*\*\*"  maclean_ora_1212.trc 

[oracle@vrh8 ~]$ grep "ksqgtl \*\*\*"  maclean_ora_4280.trc 
ksqgtl *** CU-0FE14E98-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** TM-0001CB90-00000000-00000000-00000000 mode=2 flags=0x401 timeout=0 ***
ksqgtl *** OD-0001CB90-00000000-00000000-00000000 mode=4 flags=0x10401 timeout=0 ***
ksqgtl *** OD-0001CB91-00000000-00000000-00000000 mode=6 flags=0x10401 timeout=0 ***
ksqgtl *** TM-0001CB90-00000000-00000000-00000000 mode=2 flags=0x401 timeout=0 ***
ksqgtl *** CU-0FE103C8-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** CU-0FE103C8-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** TM-0000004B-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** CU-0FE07708-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** CU-0FE07708-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** TM-00000013-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00090010-00000C08-00000000-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TM-00000012-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00030006-00000D1A-00000000-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CU-0FDFABF8-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** TM-00000224-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00001569-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000004D-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000265-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000007F-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000000EB-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000000ED-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000000F3-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000026C-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000142-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000143-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000123-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000002AF-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000146-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000127-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000128-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000129-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000012A-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000138-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000003D-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000014-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000013-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000000E-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000012-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TS-00000000-00419660-00000000-00000000 mode=6 flags=0x10001 timeout=0 ***
ksqgtl *** TT-00000000-00000010-00000000-00000000 mode=4 flags=0x10001 timeout=21474836 ***
ksqgtl *** TS-00000000-00419661-00000000-00000000 mode=6 flags=0x10001 timeout=21474836 ***
ksqgtl *** RO-00010038-00000001-00000000-00000000 mode=6 flags=0x10001 timeout=21474836 ***
ksqgtl *** RO-00010038-00000002-00000000-00000000 mode=6 flags=0x10001 timeout=21474836 ***
ksqgtl *** TM-0000000E-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00040014-00000BB2-00000000-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TX-00090006-00000C0D-00000000-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CR-00010038-00000001-00000000-00000000 mode=6 flags=0x10001 timeout=21474836 ***
ksqgtl *** CR-00010038-00000002-00000000-00000000 mode=6 flags=0x10001 timeout=21474836 ***
ksqgtl *** TM-0000000E-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** CU-0FDFA688-000007FF-00000000-00000000 mode=6 flags=0x10000 timeout=300 ***
ksqgtl *** MC-00000000-87EFB967-00000000-00000000 mode=4 flags=0x10001 timeout=21474836 ***

[oracle@vrh8 ~]$ grep "ksqgtl \*\*\*"  maclean_ora_4280.trc |grep TM-
ksqgtl *** TM-0001CB90-00000000-00000000-00000000 mode=2 flags=0x401 timeout=0 ***
ksqgtl *** TM-0001CB90-00000000-00000000-00000000 mode=2 flags=0x401 timeout=0 ***
ksqgtl *** TM-0000004B-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000013-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000012-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000224-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00001569-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000004D-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000265-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000007F-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000000EB-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000000ED-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000000F3-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000026C-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000142-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000143-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000123-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-000002AF-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000146-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000127-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000128-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000129-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000012A-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000138-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000003D-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000014-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000013-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000000E-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00000012-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000000E-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-0000000E-00000000-00000000-00000000 mode=3 flags=0x401 timeout=21474836 ***

117648=> 1CB90  

可以看到 drop index online之后只 获取了 mode=2 SS的 TM lock  TM-0001CB90-00000000-00000000-00000000 mode=2

 

 

 

类似于 INDEX ， 12c中还提供了 DROP CONSTRAINT ONLINE的新特性， 允许在线drop 约束; 但是存在如下限制：

  • Cannot drop a constraint with CASCADE
  • Cannot drop a referencing constraint

 

具体语法如下：

 

 

ALTER TABLE hr.employees DROP CONSTRAINT emp_email_uk ONLINE;

 

 

这2中 ONLINE DROP 新特性 究其根本都是在DROP 过程中不在acquire TM mode=6的表队列锁，虽然这些操作本身的耗时并不久，也就不会长时间阻塞DML ，但是对于高并发的OLTP系统而言 阻塞仍可能是致命的。

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *