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 <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.
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 V$SQL_PLAN, the predicate section comes from ACCESS_PREDICATES and FILTER_PREDICATES columns. But when I query the V$SQL_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 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
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! :)
