解锁不可见索引新特性，处理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 问题。

作者：何国亮。

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

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

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