代码改变世界

ORACLE数据库中执行计划出现INTERNAL_FUNCTION一定是隐式转换吗？

2019-12-10 14:06  潇湘隐者  阅读(3088)  评论(0)  编辑  收藏  举报

ORACLE数据库中，我们会使用一些SQL语句找出存在隐式转换的问题SQL，其中网上流传的一个SQL语句如下，查询V$SQL_PLAN的字段FILTER_PREDICATES中是否存在INTERNAL_FUNCTION： 

 

SELECT
     SQL_ID,
     PLAN_HASH_VALUE
FROM
     V$SQL_PLAN X
WHERE
     X.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 = b
 
Plan 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 statement
 
 
22 rows selected.

 

clip_image001

 

 

通过执行计划，我们看到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(*)
----------
     23851
 
SQL> 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 statement
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 

现在，我们让谓词稍微复杂一点，在查询条件中添加另一个OR，但这是针对另一列object_id的查询条件，如下所示：

 

SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR object_id = 123;
 
  COUNT(*)
----------
     23851
 
SQL> 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' OR
object_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 statement
 
 
24 rows selected.

 

clip_image002

 

修改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(*)
----------
       896
 
SQL> 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') AND
object_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(*)
----------
     23857
 
SQL> 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 unparse
Show all parameters and session values from x$ksppi/x$ksppcv...
 
NAME                             VALUE                                      DESCRIPTION
----------------------------- --------- -----------------------------------------------
_cursor_plan_unparse_enabled      TRUE          enables/disables using unparse to build
                                                                  projection/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