当前位置: 首页 >> 技术文章 >> 12.2 新特性:锁信息获取之在线删除索引
12.2 新特性:锁信息获取之在线删除索引
发布时间:2018-04-09 发布人:黄廷忠 533


 简介:

黄廷忠,云和恩墨西区交付总监。超过10年专职电信行业 Oracle 数据库管理和运维经验,熟悉电信行业的业务、数据库及硬件架构。擅长数据库各种迁移方法、优化、疑难故障排除、数据库异常恢复等。


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


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



1数据库版本信息



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,没有安装任何的补丁信息。



2环境准备



创建测试表和测试索引。


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.



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



在 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 语句可以正常执行。



4在线删除索引对 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.





资源下载

关注公众号:数据和云(OraNews)回复关键字获取

‘2017DTC’,2017 DTC 大会 PPT

‘DBALIFE’,“DBA 的一天”海报

‘DBA04’,DBA 手记4 经典篇章电子书

‘RACV1’, RAC 系列课程视频及 PPT

‘122ARCH’,Oracle 12.2 体系结构图

‘2017OOW’,Oracle OpenWorld 资料

‘PRELECTION’,大讲堂讲师课程资料