|
ORACLE數(shù)據(jù)庫中，我們會(huì)使用一些SQL語句找出存在隱式轉(zhuǎn)換的問題SQL，其中網(wǎng)上流傳的一個(gè)SQL語句如下，查詢V$SQL_PLAN的字段FILTER_PREDICATES中是否存在INTERNAL_FUNCTION：
SELECTSQL_ID, PLAN_HASH_VALUE FROMV$SQL_PLAN X WHEREX.FILTER_PREDICATES LIKE '%INTERNAL_FUNCTION%' GROUP BY SQL_ID, PLAN_HASH_VALUE;
但是筆者測(cè)試驗(yàn)證發(fā)現(xiàn)，有時(shí)候，執(zhí)行計(jì)劃中出現(xiàn)INTERNAL_FUNCTION，并不一定代表出現(xiàn)了隱式數(shù)據(jù)類型轉(zhuǎn)換，下面我們結(jié)合這篇博客“What the heck is the INTERNAL_FUNCTION in execution plan predicate section?”來講述一下執(zhí)行計(jì)劃謂詞部分中的INTERNAL_FUNCTION到底是什么？這篇博客沒有打算直接翻譯這篇文章，而是想結(jié)合自己的理解，來簡(jiǎn)單講述一下INTERNAL_FUNCTION。其實(shí)官方文檔對(duì)INTERNAL_FUNCTION的介紹非常少，最常見的理解，INTERNAL_FUNCTION這種特殊函數(shù)用于執(zhí)行隱式數(shù)據(jù)類型轉(zhuǎn)換（implicit datatype conversion），可能來自官方文檔https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#sthref141 。但是這個(gè)說法，事實(shí)上僅僅部分正確，而不是全部的事實(shí)。事實(shí)上，ORACLE中找不到INTERNAL_FUNCTION這個(gè)函數(shù)，通過V$SQLFN_METADATA視圖根本找不到INTERNAL_FUNCTION這個(gè)對(duì)象。
COL sqlfn_descr HEAD DESCRIPTION FOR A100 WORD_WRAP COL sqlfn_name HEAD NAME FOR A30 SELECT func_id , name sqlfn_name , offloadable -- , usage , minargs , maxargs -- this is just to avoid clutter on screen , CASE WHEN name != descr THEN descr ELSE null END sqlfn_descr FROM v$sqlfn_metadata WHERE UPPER(name) LIKE UPPER('%&1%') /
一般而言，我們?cè)趫?zhí)行計(jì)劃的的謂詞部分發(fā)現(xiàn)出現(xiàn)“INTERNAL_FUNCTION”，那么可能意味著出現(xiàn)了隱式類型轉(zhuǎn)換(implicit data type conversion)，下面我先簡(jiǎn)單構(gòu)造一個(gè)例子，
SQL> CREATE TABLE t(a VARCHAR2(20), b DATE); Table created.SQL> INSERT INTO t VALUES( TO_CHAR(sysdate), sysdate) ; 1 row created.SQL> commit; Commit complete.
如下所示，這個(gè)SQL會(huì)出現(xiàn)隱式數(shù)據(jù)類型轉(zhuǎn)換（implicit datatype conversion）
SQL> SELECT * FROM t WHERE a = b; no rows selected SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------SQL_ID 4ptcbny27y9b0, child number 0-------------------------------------SELECT * FROM t WHERE a = bPlan hash value: 1601196873 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| ||* 1 | TABLE ACCESS FULL| T | 1 | 21 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"=INTERNAL_FUNCTION("A"))Note ----- - dynamic sampling used for this statement22 rows selected.
通過執(zhí)行計(jì)劃，我們看到ORACLE為了能夠比較兩個(gè)不同數(shù)據(jù)類型（字段A與B之間的比較），強(qiáng)制在字段A上加了一個(gè)數(shù)據(jù)類型轉(zhuǎn)換函數(shù)，在ORACLE內(nèi)部，運(yùn)算從WHERE a=b 轉(zhuǎn)換為WHERE TO_DATE(a）=b， 這也是為什么執(zhí)行計(jì)劃中出現(xiàn)INTERNAL_FUNCTION的原因-從實(shí)際的“二進(jìn)制”執(zhí)行計(jì)劃生成可讀性的執(zhí)行計(jì)劃的代碼無法將內(nèi)部操作碼轉(zhuǎn)換為相應(yīng)的適合人們?nèi)菀桌斫獾暮瘮?shù)名稱，因此默認(rèn)使用“INTERNAL_FUNCTION”字符串取而代之顯示。 英文原文如下，可以對(duì)比理解（如果覺得翻譯的不好的話）
What happens here is that Oracle is forced to (implicitly) add a datatype conversion function around column A, to be able to physically compare two different datatypes. Internally Oracle is not running a comparison <strong>"WHERE a = b"</strong> anymore, but rather something like <strong>"WHERE TO_DATE(a) = b"</strong>. This is one of the reasons why the INTERNAL_FUNCTION shows up – the code generating the human-readable execution plan from the actual “binary” execution plan is not able to convert the internal opcode to a corresponding human-readable function name, thus shows a default “INTERNAL_FUNCTION” string there instead.
Un-unparseable Complex Expressions
執(zhí)行計(jì)劃中出現(xiàn)“INTERNAL_FUNCTION”，還有一種情況是因?yàn)椴豢煞指畹膹?fù)雜表達(dá)式（Un-unparseable Complex Expressions），下面通過一個(gè)例子來說明一下
SQL> drop table t purge; Table dropped.SQL> CREATE TABLE t AS SELECT * FROM dba_objects; Table created.SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM'; COUNT(*)---------- 23851SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------SQL_ID 77xzyugx5q3kf, child number 0-------------------------------------SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM'Plan hash value: 2966233522 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 108 (100)| || 1 | SORT AGGREGATE | | 1 | 17 | | |PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------|* 2 | TABLE ACCESS FULL| T | 22494 | 373K| 108 (7)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("OWNER"='SYS' OR "OWNER"='SYSTEM'))Note ----- - dynamic sampling used for this statementPLAN_TABLE_OUTPUT --------------------------------------------------------------------------------
現(xiàn)在，我們讓謂詞稍微復(fù)雜一點(diǎn)，在查詢條件中添加另一個(gè)OR，但這是針對(duì)另一列object_id的查詢條件，如下所示：
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR object_id = 123; COUNT(*)---------- 23851SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------SQL_ID 9vh8b6ku8sd1t, child number 0-------------------------------------SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' ORobject_id = 123 Plan hash value: 2966233522 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 111 (100)| |PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------| 1 | SORT AGGREGATE | | 1 | 30 | | ||* 2 | TABLE ACCESS FULL| T | 22494 | 659K| 111 (10)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((INTERNAL_FUNCTION("OWNER") OR "OBJECT_ID"=123))Note -----PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - dynamic sampling used for this statement24 rows selected.
修改WHERE查詢條件后，OWNER表上的兩個(gè)查詢條件消失了，由INTERNAL_FUNCTION替換了，接下來，讓我們用IN運(yùn)算符，而不是OR，但是上面SQL是不同字段之間的OR，我們需要修改一下SQL語句
SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE'; COUNT(*)---------- 896SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------SQL_ID gcqgrmtna9g1u, child number 0-------------------------------------SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') ANDobject_type = 'TABLE'Plan hash value: 2966233522 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 111 (100)| |PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------| 1 | SORT AGGREGATE | | 1 | 16 | | ||* 2 | TABLE ACCESS FULL| T | 894 | 14304 | 111 (10)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("OBJECT_TYPE"='TABLE' AND INTERNAL_FUNCTION("OWNER")))20 rows selected.
很不幸，上面執(zhí)行計(jì)劃中謂詞部分依然出現(xiàn)了INTERNAL_FUNCTION，我們?cè)谶壿嬌虾?jiǎn)化一下，只搜尋同一個(gè)字段上的三個(gè)值：
SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT'); COUNT(*)---------- 23857SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------SQL_ID 2qazbqj67y17s, child number 0-------------------------------------SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT')Plan hash value: 2966233522 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 111 (100)| || 1 | SORT AGGREGATE | | 1 | 7 | | |PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------|* 2 | TABLE ACCESS FULL| T | 24133 | 164K| 111 (10)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM'))19 rows selected.
如上所示，它確實(shí)生效了，ORACLE已將IN謂詞轉(zhuǎn)換為（或至少在執(zhí)行計(jì)劃中顯示了）了一堆OR-ed條件（針對(duì)同一列） 你可能已經(jīng)看到了前面的例子的執(zhí)行計(jì)劃輸出內(nèi)容– DBMS_XPLAN.DISPLAY_CURSOR無法解釋在單個(gè)執(zhí)行計(jì)劃步驟中應(yīng)用的“復(fù)雜”的復(fù)合謂詞，其中包括多個(gè)不同的列，并且至少其中一個(gè)列具有多個(gè)要檢查的值（例如列表中或OR-ed謂詞）
DISPLAY_CURSOR從何處獲取數(shù)據(jù)并進(jìn)行解釋呢？
DBMS_XPLAN.DISPLAY_CURSOR從V$SQL_PLAN獲取其執(zhí)行計(jì)劃的相關(guān)數(shù)據(jù)，謂詞部分來自ACCESS_PREDICATES和FILTER_PREDICATES列。但是當(dāng)我直接查詢V$SQL_PLAN時(shí)，我仍然看到相同的問題： SQL> SELECT id, filter_predicates FROM v$sql_plan WHERE sql_id = 'gcqgrmtna9g1u';
ID FILTER_PREDICATES ---------- ------------------------------------------------------------ 0 1 2 (INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE')
你可能已經(jīng)注意到，上面的原始ORed條件周圍也有括號(hào)（），這在9i中，意味著謂詞周圍的“二進(jìn)制”執(zhí)行計(jì)劃中存在“無法解釋的”內(nèi)部函數(shù)，但是在這種情況下（如10g +支持internal_function命名），不應(yīng)出現(xiàn)空白的函數(shù)名稱……不確定為什么會(huì)出現(xiàn)這種情況，但這對(duì)本篇文章來說太深入了。 V$SQL_PLAN視圖本身訪問庫高速緩存（library cache）中的實(shí)際“二進(jìn)制”子游標(biāo)（在使用了適當(dāng)?shù)?span lang="X-NONE" style="line-height: 16pt">latches/pins/mutexe之后）并對(duì)其進(jìn)行解析。為什么用這樣的術(shù)語-其實(shí)并不是根據(jù)人類容易理解的輸入并將其轉(zhuǎn)換為計(jì)算機(jī)可理解的“二進(jìn)制”格式。悄悄相反– V$SQL_PLAN訪問游標(biāo)中的“二進(jìn)制”執(zhí)行計(jì)劃的內(nèi)存結(jié)構(gòu)，并將其轉(zhuǎn)換為人類可讀的執(zhí)行計(jì)劃輸出。甚至還有一個(gè)參數(shù)控制此V$SQL_PLAN的行為，如果將其設(shè)置為false，則ACCESS_PREDICATES和FILTER_PREDICATES列將為空： 這段真不好翻譯（有可能翻譯不當(dāng)），參考英文原文如下： The V$SQL_PLAN view itself accesses the actual “binary” child cursor in library cache (after taking appropriate latches/pins/mutexes) and UNPARSES it. Why such term – well isn’t parsing something that takes a human readable input and translates it into computer-understandable “binary” format. Thus unparsing is the opposite – V$SQL_PLAN accesses the cursor’s “binary” execution plan memory structure and translates it to human-readable execution plan output. There’s even a parameter controlling this V$SQL_PLAN behavior, if it’s set to false, the ACCESS_PREDICATES and FILTER_PREDICATES columns will be empty there:
SQL> @pd unparseShow all parameters and session values from x$ksppi/x$ksppcv... NAME VALUE DESCRIPTION----------------------------- --------- -----------------------------------------------_cursor_plan_unparse_enabled TRUE enables/disables using unparse to buildprojection/predicates
順便說一句，為什么我總是說“二進(jìn)制”執(zhí)行計(jì)劃并用雙引號(hào)括起來？ 這是因?yàn)槲蚁霃?qiáng)調(diào)，ORACLE的實(shí)際執(zhí)行計(jì)劃并不像我們?cè)谄聊簧峡吹降妮敵龅奈谋灸菢?，這些輸出的“執(zhí)行計(jì)劃”只是為了在troubleshooting的時(shí)候，更好的適應(yīng)人類的閱讀習(xí)慣而生成的文本（這里其實(shí)就是說轉(zhuǎn)換成了符合人類閱讀系統(tǒng)的文本），執(zhí)行計(jì)劃也不是真正的可執(zhí)行二進(jìn)制文件（如oracle.exe中一樣），也沒有直接反饋給CPU執(zhí)行。 庫緩存子游標(biāo)中的物理執(zhí)行計(jì)劃（physical execution plan）是一堆操作碼（a bunch of opcodes），object_id和指針，用于定義行源執(zhí)行的層次結(jié)構(gòu)和順序。 SQL執(zhí)行引擎去循環(huán)遍歷這些操作碼，對(duì)其進(jìn)行解碼，然后知道下一步該做什么（要調(diào)用哪個(gè)rowsource函數(shù)）。 因此，如上所述，某些具有復(fù)雜AND / OR條件的謂詞被DBMS_XPLAN顯示為INTERNAL_FUNCTION（）。DISPLAY_CURSOR和V$SQL_PLAN因?yàn)樗鼈円矡o法完全解碼（解析）執(zhí)行計(jì)劃信息。 Using the good old EXPLAIN PLAN
不過有個(gè)好消息！ 舊的EXPLAIN PLAN命令能夠正確的解析這些復(fù)雜謂詞（當(dāng)然僅僅是其中一部分），當(dāng)EXPLAIN PLAN以一種特殊、更加儀器化的方式（more instrumented way）解析給定的SQL語句時(shí)，它顯然手頭有更多信息（并且它還使用了更多的內(nèi)存）?；蛘呖赡苤皇钦l寫了V$SQL_PLAN，沒有編寫一段代碼來解析更復(fù)雜的謂詞:)，如下所示：
SQL> EXPLAIN PLAN FOR 2 SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Plan hash value: 2966233522---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 16 | 111 (10)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 16 | | ||* 2 | TABLE ACCESS FULL| T | 894 | 14304 | 111 (10)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------- 2 - filter("OBJECT_TYPE"='TABLE' AND ("OWNER"='SCOTT' OR"OWNER"='SYS' OR "OWNER"='SYSTEM')) 15 rows selected.SQL> 這真是一個(gè)奇跡，INTERNAL_FUNCTION消失不見了，所有的謂詞都正確的顯示了，EXPLAIN PLAN命令在這里非常有用。
因此，盡管我通常不使用EXPLAIN PLAN命令，因?yàn)?span lang="X-NONE" style="line-height: 16pt">EXPLAIN PLAN輸出的執(zhí)行計(jì)劃可能會(huì)騙你，但是，每當(dāng)我在DISPLAY_CURSOR/V$SQL_PLAN/SQL Monitor輸出中看到INTERNAL_FUNCTION時(shí)，我都會(huì)運(yùn)行EXPLAIN PLAN命令執(zhí)行同一個(gè)SQL，希望快速找出其中的謂詞INTERNAL_FUNCTION代表的真正意義。
參考資料：
https://blog./2013/01/16/what-the-heck-is-the-internal_function-in-execution-plan-predicate-section/ https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#sthref141
|
|
|
