12.2 新特性：锁信息获取之在线删除索引-阿里云开发者社区

超过10年专职电信行业 Oracle 数据库管理和运维经验，熟悉电信行业的业务、数据库及硬件架构。擅长数据库各种迁移方法、优化、疑难故障排除、数据库异常恢复等。

在 12.2 之前，对索引的创建和修改已经实现在线操作，但是在线删除索引功能在 12.2 中才出来。在线删除索引有什么作用，个人感觉作用不大，基本上，生产环境中我们很少会删除索引信息，也有可能是在 12C 之前，对索引的使用监控没有一个好的办法，我们不能确定哪些索引需要使用，哪些索引不使用，所以不敢删除。对索引是否删除，作为一个运维 DBA 而非开发 DBA，对索引认为“存在即是合理”。在 12.2 之前，对索引的 DDL 语句会导致游标失效，但是在 12.2 中引入了新的选项，可以选择 DDL 是否让相关游标失效。

下面测试在线删除索引需要获取哪些锁信息。

数据库版本信息

BANNER CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production

数据库版本是12.2.0.1，没有安装任何的补丁信息。

环境准备

创建测试表和测试索引。

SQL> create table drop_index as select * from dba_objects;

Table created.

SQL> create index ind_drop_index_1 on drop_index(object_id);

Index created.

测试在线删除索引获取的锁信息

在 12.2 中，TRACE 会话锁的 EVENT 已经发生了变化，使用 trace[ksq] 来实现，不要使用之前的 EVENT 10904。

SQL> alter session set events='trace[ksq] disk medium';

Session altered.

SQL> drop index ind_drop_index_1 online;

Index dropped.

SQL> select to_char(object_id,'xxxxxxx') from dba_objects where object_name=upper('drop_index');

TO_CHAR(OBJECT_I

----------------

11f45

通过 TRACE 文件可以看到在表上申请的 TM 为2级别的锁。不是原来的 TM 为6级别的锁。

[oracle@db5 trace]$ grep ksqgtlctx htz_ora_6304.trc|grep "\*\*\*"|grep -i "11f45"

2017-03-21 06:55:47.293*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***

2017-03-21 06:55:47.298*:ksq.c@9033:ksqgtlctx(): *** OD-00011F45-00000000-00000000-00000000 mode=4 flags=0x10401 why=269 timeout=0 ***

2017-03-21 06:55:47.298*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***

上面只给出在索引所在表上的 TM 级别锁，可以看到在表级别上面是申请的 MODE=2 级别的锁，不再是原来的 MODE=6 级别的锁，也就意味着 DML 语句可以正常执行。

在线删除索引对 DML 语句的影响。

下面一共4个会话，会话1模拟 DML 事务，会话2执行 drop index online，会话3模拟 DML 事务，回话4查询锁的信息。

1，会话1

SQL> delete drop_index where object_id=10;

1 row deleted.

2，会话2

SQL> alter session set events='trace[ksq] disk medium';

Session altered.

SQL> drop index ind_drop_index_1 online;

这里 HANG 住了

3，会话3\

SQL> delete drop_index where object_id=1001;

1 row deleted.

这里任然可以进行 DML 语句，所以对后面的业务没有任何的影响。

4，查询会话锁的信息

SQL> @lock.sql

Enter value for sid: 58 会话1


SQL_ID

sid:serial:os sessio COMMAND OBJECT_NAME HASH_VALUE SQL_CHILD_NUMBER ID1-ID2 LMODE REQUEST TYPE CTIME

-------------------- --------------- ------------------------------ ---------- ----------------- -------------------- -------------------- --------------- ------------------------- -------

1.58.534.158 BACKGROUND None 2564605132 980yabycdtj6c: 655377-796 6||Exclusive Transaction enqueue lock 534

1.58.534.158 BACKGROUND None 2564605132 980yabycdtj6c: 73541-0 3||Row Exclusive DML enqueue lock 534

1.58.534.158 BACKGROUND None 2564605132 980yabycdtj6c: 133-0 4||Share Other type 575

SQL> @lock.sql

Enter value for sid: 57 会话2

SQL_ID

sid:serial:os sessio COMMAND OBJECT_NAME HASH_VALUE SQL_CHILD_NUMBER ID1-ID2 LMODE REQUEST TYPE CTIME

-------------------- --------------- ------------------------------ ---------- ----------------- -------------------- -------------------- --------------- ------------------------- -------

1.57.505.40 DROP INDEX HTZ.IND_DROP_INDEX_1 900803336 9xb91y0uv2as8:0 73545-0 6||Exclusive Other type 505

1.57.505.40 DROP INDEX Rollback Segment 900803336 9xb91y0uv2as8:0 327698-768 6||Exclusive Transaction enqueue lock 505

1.57.505.40 DROP INDEX HTZ.DROP_INDEX 900803336 9xb91y0uv2as8:0 73541-0 2||Row Share DML enqueue lock 505

1.57.505.40 DROP INDEX HTZ.DROP_INDEX 900803336 9xb91y0uv2as8:0 73541-0 4||Share Other type 505

1.57.505.40 DROP INDEX SYS.ORA$BASE 900803336 9xb91y0uv2as8:0 133-0 4||Share Other type 522

但是这里很奇怪的就是在 lock 中，没有找到会话2申请锁的信息，但是 drop 进程后台日志里面可以看到申请 TX 锁失败，一直在重复申请。

*** 2017-03-21T07:22:19.310314+08:00

2017-03-21 07:22:19.309*:ksq.c@8249:ksqcmi(): deadlock not detected on TX-000A0011-0000031C-00000000-00000000

2017-03-21 07:23:19.358*:ksq.c@8219:ksqcmi(): performing local deadlock detection on TX-000A0011-0000031C-00000000-00000000

*** 2017-03-21T07:23:19.359224+08:00

2017-03-21 07:23:19.358*:ksq.c@8249:ksqcmi(): deadlock not detected on TX-000A0011-0000031C-00000000-00000000

2017-03-21 07:24:19.371*:ksq.c@8219:ksqcmi(): performing local deadlock detection on TX-000A0011-0000031C-00000000-00000000

*** 2017-03-21T07:24:19.372349+08:00

2017-03-21 07:24:19.371*:ksq.c@8249:ksqcmi(): deadlock not detected on TX-000A0011-0000031C-00000000-0000000

下面查看整个锁的申请流程：

[oracle@db5 trace]$ grep ksqgtlctx htz_ora_6507.trc|grep "\*\*\*"

2017-03-21 07:12:21.089*:ksq.c@9033:ksqgtlctx(): *** CU-64E51B40-00000000-00000000-00000000 mode=6 flags=0x10000 why=231 timeout=300 ***

2017-03-21 07:12:21.090*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***

2017-03-21 07:12:21.093*:ksq.c@9033:ksqgtlctx(): *** SE-00000039-00008453-00000000-00000000 mode=4 flags=0x0 why=299 timeout=21474836 ***

2017-03-21 07:12:21.093*:ksq.c@9033:ksqgtlctx(): *** OD-00011F45-00000000-00000000-00000000 mode=4 flags=0x10401 why=269 timeout=0 ***

2017-03-21 07:12:21.093*:ksq.c@9033:ksqgtlctx(): *** OD-00011F49-00000000-00000000-00000000 mode=6 flags=0x10401 why=269 timeout=0 ***

2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***

2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-0000004B-00000000-00000000-00000000 mode=3 flags=0x401 why=167 timeout=21474836 ***

2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-00000013-00000000-00000000-00000000 mode=3 flags=0x401 why=167 timeout=21474836 ***

2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TX-00020012-000002CE-00000000-00000000 mode=6 flags=0x401 why=170 timeout=0 ***

2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-00000012-00000000-00000000-00000000 mode=3 flags=0x401 why=167 timeout=21474836 ***

2017-03-21 07:12:21.095*:ksq.c@9033:ksqgtlctx(): *** TX-00050012-00000300-00000000-00000000 mode=6 flags=0x401 why=170 timeout=0 ***

2017-03-21 07:12:21.095*:ksq.c@9033:ksqgtlctx(): *** TX-000A0011-0000031C-00000000-00000000 mode=4 flags=0x10001 why=171 timeout=21474836 ***

5，提交会话1

SQL> commit;

Commit complete.

会话2马上 DROP 成功。

SQL> drop index ind_drop_index_1 online;

Index dropped.

原文发布时间为：2018-04-5

本文作者：黄廷忠

本文来自云栖社区合作伙伴“数据和云”，了解相关信息可以关注“数据和云”微信公众号

12.2 新特性：锁信息获取之在线删除索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景