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

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


                              

简介:

黄廷忠,电信行业资深专家,超过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

SQL> delete drop_index where object_id=10;

 

1 row deleted.

 

会话2

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

 

Session altered.

 

SQL> drop index ind_drop_index_1 online;

这里 HANG 住了

 

会话3

SQL> delete drop_index where object_id=1001;

 

1 row deleted.

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

 

查询会话锁的信息 

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 ***

 

提交会话1

SQL> commit;

Commit complete.

 

会话2马上 DROP 成功。

SQL> drop index ind_drop_index_1 online;

Index dropped.

 

本文章由 admin 于2018年04月11日发布在Events分类下, 通告目前不可用,你可以至底部留下评论。
转载请注明:12.2 新特性:锁信息获取之在线删除索引-中国 Oracle 用户组

好文章就要一起分享!

更多

发表评论

你的大名(必填)

你的邮箱(必填)

你的网站(选填)

评论内容(必填)

😉 😐 😡 😈 🙂 😯 🙁 🙄 😛 😳 😮 mrgreen.png 😆 💡 😀 👿 😥 😎 ➡ 😕 ❓ ❗