专栏首页数据和云解锁不可见索引新特性,处理ORA-01555故障

解锁不可见索引新特性,处理ORA-01555故障

关注“数据和云”,精彩不容错过

何国亮 云和恩墨交付部技术顾问,获得 Oracle 11g OCM 认证。有超过 6 年超大型数据库专业服务经验,曾为通信运营商、银行、保险、政府、制造业等行业客户的业务关键型系统提供了运维、升级、性能优化、项目实施与管理、容灾建设等咨询与技术实施服务。在超大规模数据库(VLDB)、业务连续性与高可用、升级迁移、性能优化与管理等方面有丰富的实战经验。

摘要

从 Oracle 11g 开始引入了不可见索引(invisible index)新特性。本文将简述不可见索引的相关特性,并作相关测试。最后分享一个使用不可见索引解决 ORA-01555 的故障。希望对大家有帮助。

1.内容概述

Oracle 11g 较之前的版本,推出了很多新功能,其中一项就是不可见索引(invisible index)。本文将简单的研究一下不可见索引以及分享一个使用不可见索引处理 ORA-01555 故障案例。

2.不可见索引简介

从 Oracle 11g 开始,可以创建不可见索引(invisible index)。默认情况下,优化器会忽略 invisible index,不使用 invisible index,即使添加了相关索引 hint,也不会使用 invisible index。

初始化参数 optimizer_use_invisible_indexes 决定优化器是否使用 invisible index,其默认值为 false,即默认不使用 invisible index。但如果在 session 级别或者 system 级别上将 optimizer_use_invisible_indexes 初始化参数设置为 true,那么就可以使用 invisible index。

与不可用索引 (unusable index) 不同,invisible index 在使用 DML 语句期间仍会得到维护。

Oracle 引入不可见索引是有用途的,使索引不可见是使索引不可用或者删除索引的一种替代办法。

在删除索引之前,将索引修改为不可见,观察是否会产生影响,以便判断索引是否可以删除。

当索引不可见时,优化器生成的执行计划不会使用该索引。删除索引时,可以先将索引修改为 invisible,如果未发生性能下降问题,则可以删除该索引。在表上新建索引时,可以先创建一个最初不可见的索引,然后执行测试,看索引的效率怎么样,最后确定是否使该索引可见,是否使用该索引。

可以查看 dba_indexes、all_indexes、user_indexes 视图的 visibility 字段来确定该索引是可见索引还是不可见索引,visible 表示可见,invisible 表示不可见。

3.不可见索引测试

下面做一些简单的测试。

3.1

创建不可见索引

先创建 tab 表,然后在表上创建了一个 invisible 索引。

SQL> create table tab as select * from user_objects; Table created. SQL> create index tab_idx1 on tab(object_name) invisible; Index created. SQL> col INDEX_NAME for a25 SQL> col TABLE_OWNER for a20 SQL> col TABLE_NAME for a20 SQL> col VISIBILITY for a25 SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY,STATUS from user_indexes where TABLE_NAME='TAB'; INDEX_NAME TABLE_OWNER TABLE_NAME VISIBILITY STATUS ---------- ----------- ---------- ----------- -------- TAB_IDX1 SCOTT TAB INVISIBLE VALID

3.2

测试优化器是否会忽略不可见索引

SQL> set autotrace traceonly SQL> select * from tab where object_name='EMP'; Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731

Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='EMP') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 17 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 1328 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> select /*+index(tab tab_idx1)*/ * from tab where object_name='EMP'; Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731

-------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='EMP') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 1328 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

从上面的两个执行计划看出,都没有走 invisible index(即使加了 hint 也被忽略了),均走全表扫描。说明默认情况下,优化器会忽略 invisible index,不使用 invisible index。

3.3

测试优化器是否会使用可见索引

将 invisible index 修改为 visible index,观察优化器会不会使用索引 TAB_IDX1。

SQL> alter index tab_idx1 visible; Index altered. SQL> set autotrace traceonly SQL> select * from tab where object_name='EMP'; Execution Plan ---------------------------------------------------------- Plan hash value: 2166198891

client

2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

TAB_IDX1 索引由 invisible 修改为 visible 后,执行计划走了 TAB_IDX1 索引。

3.4

测试参数 optimizer_use_invisible_indexes 对不可见索引的影响

下面测试参数 optimizer_use_invisible_indexes 对不可见索引的影响。这里仅在 session 级做测试。

将 TAB_IDX1 索引由 visible index 修改为 invisible index。

SQL> alter index tab_idx1 invisible; Index altered.

查看 optimizer_use_invisible_indexes 参数的值,默认为 false。

将参数值修改为 true,优化器走了 TAB_IDX1,使用了 invisible index。

SQL> alter session set optimizer_use_invisible_indexes=true; Session altered. SQL> SQL> set autotrace traceonly SQL> SQL> select * from tab where object_name='EMP'; Execution Plan ---------------------------------------------------------- Plan hash value: 2166198891

Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME"='EMP') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 2 db block gets 9 consistent gets 0 physical reads 0 redo size 1331 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off

将参数值修改为 false,优化器走了全表扫描,没有使用 invisible index。

SQL> alter session set optimizer_use_invisible_indexes=false; Session altered. SQL> set autotrace traceonly SQL> SQL> select * from tab where object_name='EMP'; Execution Plan ---------------------------------------------------------- Plan hash value: 1995730731

Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='EMP') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 1328 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off

经过测试,发现在会话级将 optimizer_use_invisible_indexes 参数设置为 true,优化器会使用 invisible 索引。在会话级将 optimizer_use_invisible_indexes 参数设置为 false,优化器不会使用 invisible 索引。

3.5

不可加索引测试总结

通过测试,可知优化器默认会忽略 invisible index,不使用 invisible index,要想使用 invisible index,需要将 optimizer_use_invisible_indexes 参数修改为 true,才能使用。或者将 invisible index 修改 visible index,这样也可以使用该索引。

随着 invisible index 的引入,给索引的维护管理工作带来了很多便利,所以日常运维时可以尝试使用 invisible index,提高工作效率。

利用不可见索引解决 ORA-01555故障

4.1

故障现象描述

某天,一客户核心数据库的 alert 日志报 ORA-01555 错误,从早上 8:25 开始一直到下午 16:36,连续 ORA-01555 报错达到几百次,导致业务受阻。

Mon Mar 26 08:25:48 2018 ORA-01555 caused by SQL statement below (SQL ID: amk5k57zrbjwe, Query Duration=0 sec, SCN: 0x0107.e729a024): Mon Mar 26 08:25:48 2018 select ID, TRADE_DATE, HOST_DATE, TRADE_TIME, PASSBK_ACCT, NUMSERL, BUSI_CD, INTEREST_START_DATE from tab1 where id in ( select id from ( select row_number() over(order by TRADE_DATE,HOST_DATE,to_number(SERL) asc) as rowNumber,t.* from tab1 t where PASSBK_ACCT=:1 and NOTES_STATUS_FLAG = :2 ) where rowNumber between 1 and 20 ) order by TRADE_DATE,HOST_DATE,to_number(SERL) asc FOR UPDATE

4.2

故障分析

ORA-01555 错误是 oracle 的一个典型的错误,称之为”快照太旧”,其含义是 oracle所需要的前镜像数据,在 undo 表空间中无法找到,就会出现这个错误。根据以往处理 ORA-01555 错误的经验,要么优化 SQL 语句,加速语句执行,缩短语句执行时间。要么调大 undo_retention 的值或者调大 undo 表空间容量,使 undo 数据尽量长时间保留。

但是从 alert 日志中获取的 ORA-01555 错误信息看,同一 SQL 语句,报错几百次,每次执行时间都为 0 秒(0 sec),查看语句的执行计划没有性能问题。同时检查 undo_retention 的值和 undo 表空间容量,参数配置没有问题,undo 空间容量足够,也没有问题。这种现象十分奇怪。

根据关键字 “Query Duration=0 sec”,在 mos 上搜索,发现一篇文章和该故障现象类似。文章为:

Primary Key Index Corruption Generates ORA-01555 With Small Query Duration or with Query Duration as 0 Seconds (文档 ID 977902.1)。

其中有一段内容为:

When running a select statement an incorrect ORA-01555 with query_duration=0 is reported as a side effect of an PK index corruption under the following conditions: * The error is always reproducible when running the select statement

大致意思是,当主键索引损坏后,select 查询语句的执行时间为 0,同时报 ORA-01555 错误。也就是说主键索引损坏会导致 ORA-01555 错误。

4.3

故障解决

4.3.1. mos 解决方法

根据这篇 mos 文档提供的方法:通过先禁用主键,然后再启用主键,在启用主键过程中会重建主键索引,达到修复主键的目的。语法如下:

SQL> alter table tab1 disable primary key; SQL> alter table tab1 enable primary key;

但是当时的情况是该故障数据库是一套非常核心的 7x24 小时不间断的数据库,业务不允许中断。也就是说通过 mos 提供的方法是不可行的,原因如下:业务表 tab1 数据量很大,在启用主键过程中,会重建索引,重建索引这个动作会非常慢。另外,禁用主键约束期间,万一表中出现了重复数据,可能引发其他业务故障。在这种情况下,就不允许采样 mos 的方法(先禁用主键,然后再启用主键)。

4.3.2. 实际解决方法

经过沟通,最后决定采用不可见索引来解决这个问题。解决步骤大致如下:

(这里,假设表 tab1 的主键约束为 pk_tab1_id,主键索引为 pk_tab1_id,主键列为id)

1、检查 tab1 表上的约束情况:经过检查,tab1 表存在主键约束 pk_tab1_id,没有外键约束。 2、在 tab1 表上创建一个唯一的临时索引。 create unique index tmp_idx_tab1_id_1 on tab1(id,0); 注意:这里要创建unique唯一索引,确保id列值唯一。 3、禁用主键约束。 alter table tab1 disable primary key; 4、将主键索引修改为不可见。 alter index pk_tab1_id invisible; 此时主键索引pk_tab1_id是invisible不可见的。id列的唯一性通过临时索引tmp_idx_tab1_id_1来保证。 5、主键索引重建。 alter index pk_tab1_id rebuild; 6、启用主键约束。 alter table tab1 enable primary key; 7、将主键索引修改为可见。 alter index pk_tab1_id visible; 8、将临时索引设置为不可见。 alter index tmp_idx_tab1_id_1 invisible; 9、在业务正常后,删除临时索引。 drop index tmp_idx_tab1_id_1;

最后,alert 日志不再报 ORA-01555 错误,业务恢复正常,至此利用不可见索引成功解决 ORA-01555 问题。

作者:何国亮。

投稿:有投稿意向技术人请在公众号对话框留言。

转载:意向文章下方留言。

更多精彩请关注 “数据和云” 公众号

本文分享自微信公众号 - 数据和云(OraNews)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-07-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle的SQL多版本控制 - VERSION_COUNT

    在上一期的“恩墨讲堂”微信课中,我提到了一个控制SQL多版本的参数,有很多朋友讨论,这个参数是:_CURSOR_OBSOLETE_THRESHOLD 。 为什么...

    数据和云
  • 实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

    在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的DB Time和物理读都明显降低,但是我们发现CPU并没有明显降低。

    数据和云
  • 验证GaussDB T 闪回事务查询功能;闪回表功能强劲闪回TRUNCATE

    总的来说,gaussdb100 T 是可以支持闪回事务查询。 二、GaussDB T 的 Flashback Table 功能非常强劲可以闪回TRUNCATE...

    数据和云
  • 实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

    在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的DB Time和物理读都明显降低,但是我们发现CPU并没有明显降低。

    数据和云
  • css position relative and absolute布局

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    Jerry Wang
  • 优步(Uber)准备将“优步美食”( Uber Eats)印度业务出售给Zomato(Apps)

    三名知情人士告诉科技博客TechCrunch,优步(Uber)与当地竞争对手Zomato就在印度销售外卖服务“优步美食”(Uber Eats)的谈判已进入后期阶...

    邱邱邱邱邱
  • 开发者必备Linux命令

    macrozheng
  • python中的偏函数

    当一个函数有很多参数时,调用者就需要提供多个参数。如果减少参数个数,就可以简化调用者的负担。比如,int()函数可以把字符串转换为整数,当仅传入字符串时,int...

    py3study
  • BI商业智能与大数据的区别

    商业智能(Business Intelligence,简称:BI),又称商业智慧或商务智能,它是一套完整的解决方案,用来处理企业中现有数据,并将其转换成知识、分...

    数据前沿
  • Flash/Flex学习笔记(26):AS3自定义右键菜单

    直接上代码吧,关键地方都加上注释了: var cm:ContextMenu=new ContextMenu(); cm.hideBuiltInItems();/...

    菩提树下的杨过

扫码关注云+社区

领取腾讯云代金券