当前位置: 首页 > EVENT > 正文

12c lock info about drop index online

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

 

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

1,数据库版本信息

SQL> @db_version.sql
SQL> SET ECHO OFF

Session altered.


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

QL> 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.


本文固定链接: http://www.htz.pw/2017/03/23/12c-lock-info-drop-index-online.html | 认真就输

该日志由 huangtingzhong 于2017年03月23日发表在 EVENT 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 12c lock info about drop index online | 认真就输