ORACLE資料庫中，我們會使用一些SQL陳述句找出存在隱式轉換的問題SQL，其中網上流傳的一個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;
但是筆者測驗驗證發現，有時候，執行計劃中出現INTERNAL_FUNCTION，并不一定代表出現了隱式資料型別轉換，下面我們結合這篇博客“What the heck is the INTERNAL_FUNCTION in execution plan predicate section?”來講述一下執行計劃謂詞部分中的INTERNAL_FUNCTION到底是什么？這篇博客沒有打算直接翻譯這篇文章，而是想結合自己的理解，來簡單講述一下INTERNAL_FUNCTION，其實官方檔案對INTERNAL_FUNCTION的介紹非常少，最常見的理解，INTERNAL_FUNCTION這種特殊函式用于執行隱式資料型別轉換（implicit datatype conversion），可能來自官方檔案https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#sthref141 ，但是這個說法，事實上僅區域分正確，而不是全部的事實，事實上，ORACLE中找不到INTERNAL_FUNCTION這個函式，通過V$SQLFN_METADATA視圖根本找不到INTERNAL_FUNCTION這個物件，
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%')
/
一般而言，我們在執行計劃的的謂詞部分發現出現“INTERNAL_FUNCTION”，那么可能意味著出現了隱式型別轉換(implicit data type conversion)，下面我先簡單構造一個例子，
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.
如下所示，這個SQL會出現隱式資料型別轉換（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.
通過執行計劃，我們看到ORACLE為了能夠比較兩個不同資料型別（欄位A與B之間的比較），強制在欄位A上加了一個資料型別轉換函式，在ORACLE內部，運算從WHERE a=b 轉換為WHERE TO_DATE(a）=b， 這也是為什么執行計劃中出現INTERNAL_FUNCTION的原因-從實際的“二進制”執行計劃生成可讀性的執行計劃的代碼無法將內部操作碼轉換為相應的適合人們容易理解的函式名稱，因此默認使用“INTERNAL_FUNCTION”字串取而代之顯示， 英文原文如下，可以對比理解（如果覺得翻譯的不好的話）
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
執行計劃中出現“INTERNAL_FUNCTION”，還有一種情況是因為不可分割的復雜運算式（Un-unparseable Complex Expressions），下面通過一個例子來說明一下
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
--------------------------------------------------------------------------------
現在，我們讓謂詞稍微復雜一點，在查詢條件中添加另一個OR，但這是針對另一列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表上的兩個查詢條件消失了，由INTERNAL_FUNCTION替換了，接下來，讓我們用IN運算子，而不是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.
很不幸，上面執行計劃中謂詞部分依然出現了INTERNAL_FUNCTION，我們在邏輯上簡化一下，只搜尋同一個欄位上的三個值：
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.
如上所示，它確實生效了，ORACLE已將IN謂詞轉換為（或至少在執行計劃中顯示了）了一堆OR-ed條件（針對同一列）
你可能已經看到了前面的例子的執行計劃輸出內容– DBMS_XPLAN.DISPLAY_CURSOR無法解釋在單個執行計劃步驟中應用的“復雜”的復合謂詞，其中包括多個不同的列，并且至少其中一個列具有多個要檢查的值（例如串列中或OR-ed謂詞）
DISPLAY_CURSOR從何處獲取資料并進行解釋呢？
DBMS_XPLAN.DISPLAY_CURSOR從V$SQL_PLAN獲取其執行計劃的相關資料，謂詞部分來自ACCESS_PREDICATES和FILTER_PREDICATES列，但是當我直接查詢V$SQL_PLAN時，我仍然看到相同的問題：
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')
你可能已經注意到，上面的原始ORed條件周圍也有括號（），這在9i中，意味著謂詞周圍的“二進制”執行計劃中存在“無法解釋的”內部函式，但是在這種情況下（如10g +支持internal_function命名），不應出現空白的函式名稱……不確定為什么會出現這種情況，但這對本篇文章來說太深入了，
V$SQL_PLAN視圖本身訪問庫高速快取（library cache）中的實際“二進制”子游標（在使用了適當的latches/pins/mutexe之后）并對其進行決議，為什么用這樣的術語-其實并不是根據人類容易理解的輸入并將其轉換為計算機可理解的“二進制”格式，悄悄相反– V$SQL_PLAN訪問游標中的“二進制”執行計劃的記憶體結構，并將其轉換為人類可讀的執行計劃輸出，甚至還有一個引數控制此V$SQL_PLAN的行為，如果將其設定為false，則ACCESS_PREDICATES和FILTER_PREDICATES列將為空：
這段真不好翻譯（有可能翻譯不當），參考英文原文如下：
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
順便說一句，為什么我總是說“二進制”執行計劃并用雙引號括起來？ 這是因為我想強調，ORACLE的實際執行計劃并不像我們在螢屏上看到的輸出的文本那樣，這些輸出的“執行計劃”只是為了在troubleshooting的時候，更好的適應人類的閱讀習慣而生成的文本（這里其實就是說轉換成了符合人類閱讀系統的文本），執行計劃也不是真正的可執行二進制檔案（如oracle.exe中一樣），也沒有直接反饋給CPU執行， 庫快取子游標中的物理執行計劃（physical execution plan）是一堆操作碼（a bunch of opcodes），object_id和指標，用于定義行源執行的層次結構和順序， SQL執行引擎去回圈遍歷這些操作碼，對其進行解碼，然后知道下一步該做什么（要呼叫哪個rowsource函式），
因此，如上所述，某些具有復雜AND / OR條件的謂詞被DBMS_XPLAN顯示為INTERNAL_FUNCTION（），DISPLAY_CURSOR和V$SQL_PLAN因為它們也無法完全解碼（決議）執行計劃資訊，
Using the good old EXPLAIN PLAN
不過有個好訊息！ 舊的EXPLAIN PLAN命令能夠正確的決議這些復雜謂詞（當然僅僅是其中一部分），當EXPLAIN PLAN以一種特殊、更加儀器化的方式（more instrumented way）決議給定的SQL陳述句時，它顯然手頭有更多資訊（并且它還使用了更多的記憶體），或者可能只是誰寫了V$SQL_PLAN，沒有撰寫一段代碼來決議更復雜的謂詞:)，如下所示：
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> 這真是一個奇跡，INTERNAL_FUNCTION消失不見了，所有的謂詞都正確的顯示了，EXPLAIN PLAN命令在這里非常有用，
因此，盡管我通常不使用EXPLAIN PLAN命令，因為EXPLAIN PLAN輸出的執行計劃可能會騙你，但是，每當我在DISPLAY_CURSOR/V$SQL_PLAN/SQL Monitor輸出中看到INTERNAL_FUNCTION時，我都會運行EXPLAIN PLAN命令執行同一個SQL，希望快速找出其中的謂詞INTERNAL_FUNCTION代表的真正意義，
參考資料：
https://blog.tanelpoder.com/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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/28235.html
標籤：Oracle
上一篇：'OracleInternal.MTS.DTCPSPEManager' 型別初始值設定項引發例外
下一篇：Oracle轉SqlServer