關注「數據和雲」，精彩不容錯過

何國亮

雲和恩墨交付部技術顧問，獲得 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

----------------------------------------------------------

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> select /*+index(tab tab_idx1)*/ * from tab where object_name="EMP";

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------

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

----------------------------------------------------------

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> set autotrace traceonly

SQL> select * from tab where object_name="EMP";

Execution Plan

----------------------------------------------------------

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> select * from tab where object_name="EMP";

Execution Plan

----------------------------------------------------------

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 上搜索，發現一篇文章和該故障現象類似。文章為：

其中有一段內容為:

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 問題。

作者：何國亮。

投稿：有投稿意向技術人請在公眾號對話框留言。

轉載：意向文章下方留言。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！