Different Information Provided by v$sql_plan and explain plan (Doc ID 734133.1)

Last updated on JULY 14, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Different Information Provided by v$sql_plan and explain plan for the same select statement.

Wrong information is provided through v$sql_plan (notice the "MGR"=PRIOR NULL from v$sql_plan and "MGR"=PRIOR "EMPNO" from EXPLAIN PLAN ).

DIAGNOSTIC ANALYSIS:

SQL> conn scott/tiger
Connected.

SQL> SELECT level, ename, prior ename AS manager
  FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;

SQL> SELECT operation, access_predicates, filter_predicates
  FROM v$sql_plan , v$sql
  where v$sql.SQL_TEXT like '%scott.emp%'
  and v$sql_plan.SQL_ID=v$sql.SQL_ID;


OPERATION |ACCESS_PREDICATES |FILTER_PREDICATES
------------------------------|------------------------------|------------------------------
SELECT STATEMENT | |
----------------------------------------------------------------------------------------------
CONNECT BY |"MGR"=PRIOR NULL |
----------------------------------------------------------------------------------------------
FILTER | |"MGR" IS NULL
----------------------------------------------------------------------------------------------
TABLE ACCESS | |
----------------------------------------------------------------------------------------------
HASH JOIN |"MGR"=PRIOR NULL |

SQL> DELETE plan_table;

SQL> EXPLAIN PLAN FOR SELECT level, ename, prior ename AS manager
FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;
Explained.

SQL> SELECT operation, access_predicates, filter_predicates FROM plan_table;

OPERATION |ACCESS_PREDICATES |FILTER_PREDICATES
------------------------------|------------------------------|------------------
SELECT STATEMENT | |
--------------------------------------------------------------------------------
CONNECT BY |"MGR"=PRIOR "EMPNO" |
--------------------------------------------------------------------------------
FILTER | |"MGR" IS NULL
--------------------------------------------------------------------------------
TABLE ACCESS | |
--------------------------------------------------------------------------------
HASH JOIN |"MGR"=PRIOR "EMPNO" |
--------------------------------------------------------------------------------
CONNECT BY PUMP | |
--------------------------------------------------------------------------------
TABLE ACCESS | |
--------------------------------------------------------------------------------
TABLE ACCESS | |
--------------------------------------------------------------------------------

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms