Sometimes you see something like this in an execution plan:
有时，你会看到像这个执行计划中的一些情况：
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 22 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=INTERNAL_FUNCTION("A"))
关于INTERNAL_FUNCTION真实的含义和出现的原因，这方面的信息非常少。因而，有了本篇博客。
Oracle中实际上没有名为INTERNAL_FUNCTION的函数，这（部分）也是通过查询V$SQLFN_METADATA视图来确认的：
SQL> @sqlfn %internal%
no rows selected
来自于Oracle文档的通常理解是认为INTERNAL_FUNCTION是某种做数据类型转换的特殊函数。这只是部分正确，但不是全部真相–但是让我们先检查数据类型转换，然后继续解释其他示例。
隐式数据类型转换
我正在创建一个表，它用VARCHAR2数据类型存储日期在A列中，用DATE数据类型存储日期在B列中：
SQL> CREATE TABLE t(a VARCHAR2(20), b DATE);
Table created.
SQL> @desc t
Name Null? Type
------------------------------- -------- ----------------------------
1 A VARCHAR2(20)
2 B DATE
SQL> INSERT INTO t VALUES( TO_CHAR(sysdate), sysdate) ;
1 row created.
现在我们运行一个简单的查询并查看它的执行计划：
SQL> SELECT * FROM t WHERE a = b;
A B
-------------------- -----------------
20130116 17:41:49 20130116 17:41:49
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=INTERNAL_FUNCTION("A"))
这里发生的是Oracle强制的（隐式）添加了数据类型转换函数在A列上，以便可以物理上比较两个不同的数据类型 。在内部，Oracle并没有运行** “where a=b” 的比较，而是类似于 “日期（a）=b” ** 的比较。这是出现内部_函数的原因之一 – 从实际的“二进制”执行计划生成可读执行计划的代码,无法将内部操作代码转换为相应的可读函数名，因而在那里显示默认的“INTERNAL_FUNCTION”字符串。
这是Oracle 10g+ 的行为，在9i及之前，Oracle不打印函数名，来自9i的示例输出如下：
1 - filter(("DUAL"."DUMMY")=:TEST_VAR)
你看到上面的“DUAL”“DUMMY”周围那看起来不必要的括号吗？为什么不直接用“DUAL.DUMMY”=：TEST_VAR，这是不是“DUAL.”DUMMY“：=TEST_VAR的拼写错误？实际上这不是一个拼写错误，在Oracle 10g之前，您必须小心地发现任何这样的“不需要的”括号，因为它们实际上表示有一个函数被调用，类似于F（“DUAL.”DUMMY），只是“F”从未被打印出来。在Oracle10g以后的版本中，会打印一个通用的“INTERNAL_FUNCTION”，而不是什么都不打印，至少我们知道有一些函数应用于列/变量。您需要查看代码（可能还有会话级别的NLS_ 设置）以确定它在逻辑上是什么函数（比如在比较这些数据类型时，函数将时间戳转换为日期）。
无法反解析的复杂表达式
Oracle文档和blogosphere已经讲了数据转换的原因，但还有更多。
检查这个带有OR子句的例子：
SQL> CREATE TABLE t AS SELECT * FROM dba_objects;
Table created.
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM';
COUNT(*)
----------
32272
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 36652 | 608K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter(("OWNER"='SYS' OR "OWNER"='SYSTEM'))
到目前为止一切正常，DBMS_XPLAN.DISPLAY_cursor函数直接从库缓存读取计划，而能够正确解释谓词。
现在我们让谓词稍复杂一些，我会添加另一个OR到谓词中，但是，使用不同的列：
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR object_id = 123;
...
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T | 36652 | 1073K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter((INTERNAL_FUNCTION("OWNER") OR "OBJECT_ID"=123))
现在，在OWNER上的两个条件被替换为了INTERNAL_FUNCTION？
让我们尝试用IN操作符代替OR，但是，我们是在两个不同的列上检查值（所以，我们无法把它们全部放到一个IN子句中）：
SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';
COUNT(*)
----------
1178
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| T | 374 | 10472 | 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter((INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE'))
依旧如此。让我们尝试一个逻辑上简单些的操作，在只在同一个列上搜索3个值：
SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT');
COUNT(*)
----------
32278
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 31960 | 530K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM'))
成功！Oracle已经转换（或者至少如解释那样显示）这个IN谓词为一组OR的条件（在同一个列上）。
您可能已经看到了前面的示例中发生的情况—DBMS_XPLAN.DISPLAY_CURSOR无法解释在单个执行计划步骤中应用的“复杂”组合谓词，该步骤包括多个不同的列，并且至少有一个列有多个要检查的值（如in-list或OR谓词）。
DISPLAY_CURSOR从何处获取数据和解释
DISPLAY_CURSOR从VSQL_plan获取计划数据，谓词部分来自ACCESS_PREDICATES和FILTER_PREDICATES列。但当我直接查询VSQL_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')
而且您可能已经注意到上面的原始OR条件周围也有括号，这在9i中意味着在“二进制”执行计划中，报告的谓词周围有一个“无法解释”的内部函数，但是在这种情况下（因为10g+支持INTERNAL_FUNCTION命名），不应该出现空白的函数名…不确定为什么会出现这种情况，但对于本文来说，这是太低级的小细节了。
VSQL_PLAN视图本身访问库缓存中的实际“二进制”子游标（在使用适当的latches/pins/mutexes之后）并将其UNPARSE（反解析）。为什么用这样的术语-难道解析不是将人类可读的输入，将其转换为计算机可理解的“二进制”格式的东西吗。因此，unparsing（反解析）正好相反–VSQL_PLAN访问游标的“二进制”执行计划内存结构，并将其转换为人类可读的执行计划输出。甚至还有一个参数控制此V$SQL_PLAN行为，如果将其设置为false，则ACCESS_PREDICATES和FILTER_PREDICATES列将在此处为空：
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执行的真正执行计划并不像我们在屏幕上看到的那样是文本形式的，文本只是为人类生成的，出于故障排除的原因。执行计划不是真正的可执行二进制文件（如oracle.exe)或者，它也不会直接输入CPU执行。库缓存子游标中的物理执行计划是一组操作码、对象id和指针，用于定义行源执行的层次结构和顺序。是SQL执行引擎，循环这些操作码，对它们进行解码，并知道接下来要做什么（调用哪个rowsource函数）。
所以，如上所见，一些存在复杂 AND/OR条件链被DBMS_XPLAN.DISPLAY_CURSOR显示为INTERNAL_FUNCTION()，并且V$SQL_PLAN也不能去全部的解码（反解析）执行计划。
使用旧款的EXPLAIN PLAN
不过，有好消息！旧款的EXPLAIN PLAN命令可以正确地反解析（某些）这样的复杂谓词的连接！当EXPLAIN-PLAN操作以一种特殊的、更工具化的方式再次解析给定的SQL时，它手头显然有更多的信息（而且这会使用更多的内存）。也可能是编写了V$SQL_PLAN的人，却没有编写用于反解析更复杂谓词连接的代码：）
检查这个输出，在这里我使用explain plan命令而不是仅仅运行SQL：
SQL> EXPLAIN PLAN FOR
SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 293 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| T | 374 | 10472 | 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')
AND "OBJECT_TYPE"='TABLE')
神奇啊！INTERNAL_FUNCTION不存在了，所有谓词值都正确显示。EXPLAIN PLAN命令在这里非常有用。
因此，虽然我通常不使用EXPLAIN PLAN命令，因为EXPLAIN PLAN可能会欺骗您，但是每当我在DISPLAY_CURSOR/V$SQL_PLAN/SQL Monitor输出中看到一个INTERNAL_FUNCTION时，我都会为同一个查询运行EXPLAIN PLAN命令，希望能够快速找出其中的谓词到底是什么。
好吧，又是凌晨2点半了-睡觉去了！😃
原文链接和内容如下：
https://tanelpoder.com/2013/01/16/what-the-heck-is-the-internal_function-in-execution-plan-predicate-section/
What the heck is the INTERNAL_FUNCTION in execution plan predicate section?
Tanel Poder
2013-01-17
Sometimes you see something like this in an execution plan:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 22 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=INTERNAL_FUNCTION("A"))
There’s quite a little information available about what the INTERNAL_FUNCTION really is and why does it show up, thus this blog entry.
There’s actually no function called INTERNAL_FUNCTION in Oracle and this is (partially) confirmed also by querying the V$SQLFN_METADATA view:
SQL> @sqlfn %internal%
no rows selected
The common understanding, coming from Oracle documentation is that the INTERNAL_FUNCTION is some sort of a special function doing (implicit) datatype conversion. This is only partially true and not the whole truth – but let’s examine the datatype conversion first and proceed to the explanation and other examples later on.
Implicit datatype conversion
I’m creating a table which stores a date in VARCHAR2 datatype in column A and a date in DATE datatype in column B:
SQL> CREATE TABLE t(a VARCHAR2(20), b DATE);
Table created.
SQL> @desc t
Name Null? Type
------------------------------- -------- ----------------------------
1 A VARCHAR2(20)
2 B DATE
SQL> INSERT INTO t VALUES( TO_CHAR(sysdate), sysdate) ;
1 row created.
Now let’s run a simple select query and see its execution plan:
SQL> SELECT * FROM t WHERE a = b;
A B
-------------------- -----------------
20130116 17:41:49 20130116 17:41:49
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=INTERNAL_FUNCTION("A"))
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 “WHERE a = b” anymore, but rather something like “WHERE TO_DATE(a) = b”. 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.
This is Oracle 10g+ behavior, in 9i and before, Oracle just printed nothing as that function name, example output from 9i is below:
1 - filter(("DUAL"."DUMMY")=:TEST_VAR)
Do you see that there are seemingly unnecessary brackets around “DUAL”.”DUMMY” above? Why not just “DUAL.DUMMY”=:TEST_VAR, is this (“DUAL”.“DUMMY”):=TEST_VAR usage some typo? It’s actually not a typo, before Oracle 10g you had to be careful to spot any such “unneccesary” brackets as they really indicated there was some function called, something like F(“DUAL”.”DUMMY”), only that the “F” was never printed. In Oracle 10g onwards, a generic “INTERNAL_FUNCTION” is printed instead of nothing in such cases – at least we know there’s some function applied to the column/variable. You need to look into the code (and possibly session-level NLS_ settings) to figure out what function it could logically be (like a function casting TIMESTAMP into a DATE when comparing these datatypes).
Un-unparseable Complex Expressions
Ok, the datatype conversion reason is already covered in Oracle docs and blogosphere. But there’s more.
Check out this example with an OR clause:
SQL> CREATE TABLE t AS SELECT * FROM dba_objects;
Table created.
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM';
COUNT(*)
----------
32272
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 36652 | 608K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter(("OWNER"='SYS' OR "OWNER"='SYSTEM'))
All is fine so far – the DBMS_XPLAN.DISPLAY_CURSOR function, which reads the plans directly from library cache is able to explain the predicate correctly.
Now let’s make the predicate a little more complex, I will add another OR to this predicate, but it’s against a different column:
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR object_id = 123;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T | 36652 | 1073K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter((INTERNAL_FUNCTION("OWNER") OR "OBJECT_ID"=123))
Now, suddenly the two conditions on the OWNER table are gone and replaced by an INTERNAL_FUNCTION?
Let’s try an IN operator instead of the OR, but wait, we are checking for values in two different columns (so we can’t put them all into the same IN clause):
SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';
COUNT(*)
----------
1178
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| T | 374 | 10472 | 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter((INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE'))
Still no luck.
Let’s try a logically simpler operation, with just searching for 3 values in the same column:
SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT');
COUNT(*)
----------
32278
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 31960 | 530K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM'))
Works! Oracle has transformed (or at least shows it in the explanation) this IN predicate to a bunch of OR-ed conditions (against the same column).
You might already see what’s going on with the earlier examples – DBMS_XPLAN.DISPLAY_CURSOR is not able to explain “complex” composite predicates applied in a single execution plan step, which include multiple different columns AND at least one of the columns has multiple values to check for (like an in-list or OR-ed predicates).
Where does DISPLAY_CURSOR get its data from and an explanation
DBMS_XPLAN.DISPLAY_CURSOR gets its plan data from VSQL_PLAN directly, I still see the same problem:
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')
And you may have noticed that there are brackets() around the raw ORed conditions above too, which in 9i meant that there was an “unexplained” internal function in the “binary” execution plan around the reported predicates, but in this case (as 10g+ supports the internal_function naming), blank function names shouldn’t occur… Not really sure why there about this, but that’s too low level little detail for this post.
The VSQL_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 – VSQL_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
By the way, why do I keep saying “binary” execution plan and in double quotes? It’s because I want to emphasize that the real execution plan that Oracle executes is not in the text form like we see on the screen, the text is just generated for humans, for troubleshooting reasons. The execution plan is not a real executable binary (as in oracle.exe) either, it’s not directly fed to the CPUs for execution. The physical execution plan in the library cache child cursor is a bunch of opcodes, object_ids and pointers for defining the hierarchy and order of rowsource execution. It’s the SQL execution engine, which then loops through these opcodes, decodes them and knows what to do (which rowsource function to call) next.
So, as we’ve seen above, some predicates with complex AND/OR conditions chained together are displayed as INTERNAL_FUNCTION() by DBMS_XPLAN.DISPLAY_CURSOR and V$SQL_PLAN as they are unable to decode (unparse) the execution plan info fully.
Using the good old EXPLAIN PLAN
There’s some good news though! The good old EXPLAIN PLAN command can unparse (some of) these complex predicates properly! As the EXPLAIN PLAN operation parses the given SQL again, in a special, more instrumented way, it has more information at hand apparently (and it uses more memory too). Or it could just be that whoever wrote V$SQL_PLAN, didn’t write the piece of code for unparsing more complex predicates 😃
Check this output, where I’m using the explain plan command instead of just running the SQL:
SQL> EXPLAIN PLAN FOR
SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 293 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| T | 374 | 10472 | 293 (1)| 00:00:04 |
---------------------------------------------------------------------------
2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')
AND "OBJECT_TYPE"='TABLE')
It’s a miracle! The INTERNAL_FUNCTION is gone and all the predicate values are shown correctly. EXPLAIN PLAN command was very useful here.
So, while I usually don’t use the EXPLAIN PLAN command as explain plan can lie to you, then whenever I see an INTERNAL_FUNCTION in the DISPLAY_CURSOR/V$SQL_PLAN/SQL Monitor output, I run an explain plan command for the same query in hope of quickly finding out what the predicates in there really are.
Ok, it’s 2:30am here yet again – off to sleep! 😃
