觸類旁通：那些關於 TBL$OR$IDX$PART$NUM 的詭異案例和知識

最新 07-21

你是否留意過資料庫中有一個奇怪的函數 TBL$OR$IDX$PART$NUM ，你是否留意過很多場景下都出現過它的身影？

讓我們通過幾個案例、幾個特性，了解一下 Oracle 的隱藏世界。

故事一：ORA-1008 錯誤診斷

某日，在客戶的運行場景下，特定SQL遭遇 ORA-00604 / ORA-01008, 導致某些報表無法正常運行。

資料庫版本為 11.2.0.4.4，操作系統 Linux，執行的錯誤信息如下：

ORA-604: error occurred at recursive SQL level 1

ORA-1008: not all variables bound

用戶仔細檢查了 n 次 SQL，並未發現綁定變數賦值問題，但是SQL操作的主要表是複合分區表，啟用10046 事件跟蹤或者Errorstack，可以通過後台日誌找到了可疑的問題SQL和詳細信息。

原來出現問題的並不是用戶SQL，而是在後台執行的一條遞歸SQL語句，這個資料庫自身的SQL語句調用，因為缺少變數輸入，而出現了 ORA-01008 錯誤：

----- Error Stack Dump -----

ORA-01008: not all variables bound

----- Current SQL Statement for this session (sql_id=7ughmqbx14mfz) -----

SELECT distinct TBL$OR$IDX$PART$NUM("TERRY", 0, 2, 0, "ID") FROM (SELECT "B"."ID" "ID" FROM "G_DAW" "B" WHERE "B"."SALE_END_TIME">=TO_DATE(:B1,"yyyy-mm-dd hh24:mi:ss") AND "B"."RDC_ID"=TO_NUMBER(:B2) AND "B"."RDC_ID"=TO_NUMBER(:B3) AND "B"."SALE_BEGIN_TIME"

注意：在很多資料庫問題的診斷過程中，我們都能發現遞歸SQL的身影，當遞歸 SQL 出現在 AWR 報告的 TOP SQL中時，就需要注意，是否觸發了BUG。

通過TBL$OR$IDX$PART$NUM關鍵字和 ORA-1008 錯誤號，在 MOS 上很容易找到這個問題的相關BUG：

這個BUG的描述大致是：

對於複合分區表的某些分區剪裁，可能會發生這種情況。如果子分區級別的子查詢剪裁在執行時出現 ORA-604 / ORA-1008，那就可能和這個BUG相關。

其典型特徵就是，出錯時指向如下SQL：

SELECT distinct TBL$OR$IDX$PART$NUM ....

簡單來說，就是Oracle在子分區級別執行子查詢分區剪裁時，調用的內部遞歸SQL，因為缺少變數傳入而出錯，其解決方案是，禁用子查詢謂詞分區剪裁。

這個特性是由參數控制的，在會話級別禁用：

alter session

set "_subquery_pruning_enabled"=false;

或者修改初始化參數，在全局禁用之：

alter system

set 「_subquery_pruning_enabled」=false;

這個特性是在 Oracle 10g 引入的，在執行計劃中，當出現 PARTITION RANGE SUBQUERY ，我們可以看到在 Pstart 和 Pstop 執行了分區剪裁，就是使用到了這個特性。當禁用這個參數，將會影響這一部分執行計劃：

通過 x$ksppi 表，可以找到所有隱含參數，及了解其含義：

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm="_subquery_pruning_enabled";

KSPPINM

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

KSPPDESC

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

_subquery_pruning_enabled

enable the use of subquery predicates to perform pruning

該參數的作用就是：允許使用子查詢謂詞來執行分區剪裁。和這個參數相關的還有幾個參數，通過參數描述就可以知道其作用：

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like "_subquery_pruning%";

KSPPINM

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

KSPPDESC

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

_subquery_pruning_cost_factor

subquery pruning cost factor

_subquery_pruning_reduction

subquery pruning reduction factor

_subquery_pruning_enabled

enable the use of subquery predicates to perform pruning

_subquery_pruning_mv_enabled

enable the use of subquery predicates with MVs to perform pruning

這個BUG的主要影響範圍是 12.1.0.1 (Base Release) 和 11.2.0.4 ，大家可以酌情參考。

故事二：MERGE 的緩慢之夜

看完了這個小案例之後，我們想起了近期遇到的另外一個問題，在一個客戶的分析系統中，遭遇到性能問題，從 32K 的塊大小可以看出，這是一個倉庫型應用：

在AWR報告中的 TOP SQL 中，我們注意到前兩個SQL語句 MERGE 操作，執行時間超長。第一條 SQL 執行了 167，844 秒，對照AWR的採樣時間 780 分鐘，接近是 4 倍的 Elappsed Time：

為什麼TOP SQL執行如此之久，並且執行時間（ 167844/780/60 = 3.59 ）遠超單CPU周期，我們看一下這條SQL就一目了然了，因為其使用了 4 個並行度，並行執行時間被累計：

MERGE /*+ APPEND NOLOGGINGPARALLEL ("TRANS_DEPOSITS", 4)*/

INTO "TRANS_DEPOSITS" USING (SELECT ....

這個問題，進一步怎麼分析呢？雖然我們可以分析執行計劃、阻塞、鎖定等相關因素，但是第三個SQL引起了我的關注。通過上一個案例的分析，我們已經不陌生了，這是一條遞歸SQL，執行時間同樣超長，存在問題。

展開這條 SQL ，仔細看一下，顯然同樣是對於分區表的內部操作：

SELECT distinct TBL$OR$IDX$PART$NUM("TRANS_DEPOSITS", 0, 1, 0, "TRAN_DATE")

FROM

(SELECT "BANS_INCT01"."D2_TRN_DATE" "TRAN_DATE"

FROM "ODS"."BANS_INCT01" "BANS_INCT01") ORDER BY 1

這條SQL有什麼特殊之處呢？多看一秒鐘。

如果注意看，你會發現這個 SQL 的子查詢 FROM 子句中沒有 WHERE 條件。

有了這些基本分析之後，我們判斷這可能是另外一個 BUG 了，結合TBL$OR$IDX$PART$NUM關鍵字和 MERGE 操作，在 MOS 上很快能夠匹配到以下 BUG：

Bug 18794814 MERGE statement is slow

due to recursive query on TBL$OR$IDX$PART$NUM

這個 BUG 離我們更近了一步，影響範圍是 12.1.0.2 (Server Patch Set) 和 11.2.0.4 ，修正版本是：12.2.0.1 或者 12.1.0.2.170718 。

這個 BUG 的觸發條件：

如果發現 MERGE SQL 執行緩慢並跟蹤/堆棧，發現如下遞歸SQL：

SELECT distinct TBL$OR$IDX$PART$NUM（....

並且這個SQL沒有連接條件，則可能遇到此BUG。

這個問題的臨時解決方案和上一個案例相同：

設置 _subquery_pruning_enabled = false ，或者找到適合的補丁應用。

注意：由於在很多場景下遇到問題，初始化配置資料庫時，可以考慮將參數 _subquery_pruning_enabled 設置為 False，以規避可能遇到的種種問題。

故事三：TBL$OR$IDX$PART$NUM 的身世之謎

我們已經反覆看到了 TBL$OR$IDX$PART$NUM 的身影，那麼現在是時候研究一下這是一個什麼函數了。

TBL$OR$IDX$PART$NUM 函數是一個內部函數，用於確定特定記錄所屬的分區。這個函數經常用於各種內部操作，前面我們已經見到了幾個案例。

函數調用的基本格式如下：

TBL$OR$IDX$PART$NUM(PARTITIONED_TABLE_NAME,0,d#,p#,COLUMN_NAME) .

這個函數沒有文檔說明，需要通過測試來進行一些驗證。如下創建測試分區表：

create table enmotech

( depid number,

kpiid varchar2(1),

ename varchar2(10),

npscr number)

partition by range (depid,kpiid)

subpartition by range (npscr)

subpartition template

( subpartition G1 values less than(60),

subpartition G2 values less than(90),

subpartition G3 values less than(100))

( partition "P1" values less than (10,"A"),

partition "P2" values less than (10,"B"),

partition "P3" values less than (10,"C"),

partition "P4" values less than (20,"A"),

partition "P5" values less than (20,"B"),

partition "P6" values less than (20,"C"));

insert into enmotech values(10, "A","ANG", 95);

insert into enmotech values(10, "B","ING", 85);

insert into enmotech values(10, "C","ION", 75);

insert into enmotech values(20, "A","HUA", 95);

insert into enmotech values(20, "B","JUU", 85);

commit;

如果我們想找出（depid，kpiid）為（20，"B"）的記錄所屬分區，以下查詢即可支持：

select TBL$OR$IDX$PART$NUM("C##EYGLE"."ENMOTECH", 0, 1, 0, 20, "B") pt

from dual;

----------

查詢一下表中現有記錄及分區情況：

SQL> SELECT en.*,

TBL$OR$IDX$PART$NUM("C##EYGLE"."ENMOTECH", 0, 1, 0,

en."DEPID", en."KPIID") pt

FROM ENMOTECH en;

DEPID K ENAME NPSCR PT

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

10 A ANG95 2

10 B ING85 3

10 C ION75 4

20 A HUA95 5

20 B JUU85 6

根據ROWID轉換對象號出來：

SQL> select

tbl$or$idx$part$num("C##EYGLE"."ENMOTECH",

"ROWID") objn from enmotech ;

OBJN

----------

96047

96048

96049

96050

96051

有了這個對象號，關聯 DBA_OBJECTS 或者 USER_OBJECTS ，可以找到對象名稱等信息，整個轉換鏈路就可以非常靈活.

注意，以下查詢是在 11.2.0.4 版本上的查詢輸出，我們可以清晰的看到每一條記錄所屬的對象和分區：

故事四：來自 12.2 的未知 BUG

再請注意，當我們在 12.2 版本上執行這個SQL時，確觸發了內部錯誤，這是一個未知的BUG，使用的是 12.2 多租戶版本，使用 common 用戶執行測試：

在告警日誌中記錄的錯誤如下，在 MOS 上沒有匹配的已知BUG，也請謹慎測試：

Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x10EA885B, lnxsni()+2059] [flags: 0x0, count: 1]

Errors in file /u01/trace/enmo12c_ora_3508.trc (incident=256928) (PDBNAME=CDB$ROOT):

ORA-07445: exception encountered: core dump [lnxsni()+2059] [SIGSEGV] [ADDR:0x0] [PC:0x10EA885B] [SI_KERNEL(general_protection)] []

檢查一下跟蹤文件，注意標紅部分，最後執行這個遞歸查詢之後，進入SQL執行階段，出現錯誤，這個基於view$的查詢，傳入一個綁定變數 ROWID：

我們來檢查一下，這個遞歸SQL的執行作用。

首先這個ROWID 00002F93.0006.0001 是一個歷史版本的限制 ROWID格式，需要轉換成擴展 ROWID：

SQL> select DBMS_ROWID.ROWID_TO_EXTENDED

("00002F93.0006.0001","SYS","VIEW$",0)

from dual;

DBMS_ROWID.ROWID_T

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

AAAABfAABAAAC+TAAG

這個SQL返回的對象是什麼呢？確認一下：

SQL> select obj# from view$

where rowid=DBMS_ROWID.ROWID_TO_EXTENDED

("00002F93.0006.0001","SYS","VIEW$",0);

OBJ#

----------

4440

SQL> select object_name

from dba_objects where object_id=4440;

OBJECT_NAME

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

_CURRENT_EDITION_OBJ

竟然是版本對象惹的禍。

再來檢查一下這個SQL查詢，其中 enmotech 是一個記錄表，dba_objects 是一個複雜視圖，顯然是在這個視圖關聯和展開時遇到了和版本對象相關的 BUG，將視圖查詢結果實體化，可以繞過這個問題：

在這個查詢中，類似的結果集可以通過 dbms_rowid.rowid_object 方式獲取：

那麼除此之外，在哪些場景下還可能用到TBL$OR$IDX$PART$NUM這個函數呢？

故事五：12c 的延遲索引維護特性

在 Oracle 12c 中，有一個新特性被稱為：延遲全局索引維護 - Delayed Global Index Maintenance 。

大家知道，在分區表中，如果創建全局索引，當我們對分區進行刪除、截斷等操作時，全局索引會失效，除非增加關鍵字 UPDATE GLOBAL INDEXES 同時維護索引，但是增加這個關鍵字又可能導致分區維護過慢。

在 12c 中，增加了一個特性，延遲全局索引維護，可以將索引維護工作非同步延遲進行，在資料庫中是通過 SYS.PMO_DEFERRED_GIDX_MAINT_JOB 任務，在每天02:00運行。

那麼問題來了，延遲維護的全局索引，在查詢中還會生效嗎？

基於以上測試表創建一個全局索引：

SQL> create index enmotech_idx1 on enmotech (depid) global ;

Index created.

索引在查詢中生效：

刪除一個分區，再來檢查索引狀態，在orphaned_entries欄位已經記錄了索引出現孤兒條目：

SQL> alter table enmotech drop partition p2update indexes;

Table altered.

SQL> COL INDEX_NAME FOR A20

SQL> select index_name, status,orphaned_entries

from dba_indexes

where index_name="ENMOTECH_IDX1";

INDEX_NAME STATUS ORP

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

ENMOTECH_IDX1 VALIDYES

注意此時的執行計劃，仍然使用了索引，但是多了一個謂詞過濾條件，是通過 TBL$OR$IDX$PART$NUM 函數對 ROWID 進行判斷過濾，以保證索引中的孤兒條目不會被執行，當然有了這個步驟，我們看到遞歸調用的數量大大增加了：

我們可以檢查一下PMO_DEFERRED_GIDX_MAINT_JOB的工作情況，其執行是調用了dbms_part的索引維護包：

SQL> exec print_table("select JOB_NAME,LAST_START_DATE,RUN_COUNT from dba_scheduler_jobs where job_name=""PMO_DEFERRED_GIDX_MAINT_JOB""");

JOB_NAME : PMO_DEFERRED_GIDX_MAINT_JOB

LAST_START_DATE :11-JUL-18 02.00.00.356202 AM UTC

RUN_COUNT : 73

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

SQL> select program_action

from dba_SCHEDULER_PROGRAMS

where program_name="PMO_DEFERRED_GIDX_MAINT"

PROGRAM_ACTION

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

dbms_part.cleanup_gidx_internal(

noop_okay_in => 1);

現在手工調用，消除索引的孤兒狀態，查詢即刻恢復到正常的狀態：

SQL> exec dbms_part.cleanup_gidx_internal(noop_okay_in => 1);

PL/SQL procedure successfully completed.

SQL> select index_name, status,orphaned_entries

from dba_indexes where index_name="ENMOTECH_IDX1";

INDEX_NAME STATUS ORP

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

ENMOTECH_IDX1 VALIDNO

在 Oracle 故障處理和學習的過程中，歸納匯總，聚點滴於一體，就能逐步讓我們的知識成為體系，四通八達，所以，走多遠，鑽多深，取決於我們自己。

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

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！

請您繼續閱讀更多來自 雲和恩墨 的精彩文章:

※ProxySQL！像C羅一樣的強大！

TAG:雲和恩墨 |