EXPLAIN PLAN FOR Anomaly

First of all apologies to anyone who lands here expecting some interesting execution plan analysis… This is about name resolution for the EXPLAIN PLAN command in comparison to everything else I’m aware of.

Wth the usual disclaimer of: I might have missed something…¬†EXPLAIN PLAN is not affected by the “ALTER SESSION SET CURRENT_SCHEMA” command.

This little investigation all started with a report of “explain plan” not working in PL/SQL Developer. On digging into it we discovered 3 PLAN_TABLE tables in the database plus a couple of synonyms. The names have been changed to protect the innocent, so to speak. Anyway it looked something like this:


SQL> select owner, object_name, object_type from dba_objects where object_name = 'PLAN_TABLE';

OWNER                          OBJECT_NAME OBJECT_TYPE
------------------------------ ----------- -------------------
PUBLIC                         PLAN_TABLE  SYNONYM
APP_SCHEMA                     PLAN_TABLE  TABLE
USER01                         PLAN_TABLE  SYNONYM
SYSTEM                         PLAN_TABLE  TABLE
SYS                            PLAN_TABLE  TABLE

With the PUBLIC synonym pointing to SYSTEM.PLAN_TABLE and the synonym owned by USER01 pointing to APP_SCHEMA.PLAN_TABLE. Just to spice things up, a logon trigger used ALTER SESSION SET CURRENT_SCHEMA to APP_SCHEMA for pretty much all database users. After a quick test I confirmed that things were definitely not working as expected via SQL*Plus as shown below:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining op
JServer Release 9.2.0.8.0 - Production

MARTINNASH@orcl> explain plan for
  2  select * from dual;

Explained.

MARTINNASH@orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name           | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|-------------------------------------- NOT THE QUERY I JUST RAN --------------------------------------|
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  <------------ TEXT REMOVED ------------>

Note: cpu costing is off

Remember the logon trigger changes my current schema to APP_SCHEMA.

MARTINNASH@bi20p> alter session set current_schema = martinnash;

Session altered.

MARTINNASH@bi20p> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------

Note: rule based optimization

I traced the use of EXPLAIN PLAN FOR and found (taken from 11g rather than 9i):

insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)

But as demonstrated below (again 11g rather than 9i) EXPLAIN PLAN FOR doesn’t appear to honour changing schema using ALTER SESSION SET CURRENT_SCHEMA.

I created a user MNASH and created a PLAN_TABLE in MNASH’s schema using $ORACLE_HOME/rdbms/admin/utlxplan.sql. This was in addition to the PUBLIC synonym PLAN_TABLE for the SYS.PLAN_TABLE$ temporary table created by default.

SQL> explain plan for 
  2  select * from dual;      

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

All cool and working as expected. The following shows that not only are the plan records written to MNASH.PLAN_TABLE, but PLAN_TABLE is resolved to MNASH.PLAN_TABLE.

SQL> select count(*) from sys.plan_table$;

  COUNT(*)
----------
         0

SQL> select count(*) from mnash.plan_table;

  COUNT(*)
----------
         2

SQL> select count(*) from plan_table;

  COUNT(*)
----------
         2

SQL> 

However, if I change my current schema to that of another user and run EXPLAIN PLAN then use DBMS_XPLAN it does not give my execution plan…

SQL> alter session set current_schema = scott;

Session altered.

SQL> select sys_context('userenv','session_user') username
  2       , sys_context('userenv','current_schema') schema from dual;

USERNAME   SCHEMA
---------- ----------
MNASH      SCOTT

SQL> explain plan for
  2  select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE

Looking at where EXPLAIN PLAN FOR has written the rows reveals that the PUBLIC synonym PLAN_TABLE that points to SYS.PLAN_TABLE$ is not “seen” by EXPLAIN PLAN FOR, but is seen by the SELECT statements.

SQL> select count(*) from sys.plan_table$;

  COUNT(*)
----------
         0

SQL> select count(*) from mnash.plan_table;

  COUNT(*)
----------
         4

SQL> select count(*) from plan_table;

  COUNT(*)
----------
         0

So the PLAN_TABLE that is resolved first by EXPLAIN PLAN FOR is not the same PLAN_TABLE that is resolved first by standard SQL including DBMS_XPLAN, which is defined with “invoker rights”.

SQL> select * from table(dbms_xplan.display('mnash.plan_table'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Obviously this is not something that is going to be affecting loads of people, after all you’d not only have to be using ALTER SESSION SET CURRENT_SCHEMA, but you’d also have to have created multiple PLAN_TABLE tables in your database (and use them). That said if you do find yourself in the same situation then hopefully this will explain what is going on.

If anyone can explain why/how EXPLAIN PLAN FOR doesn’t see itself as running in the “CURRENT_SCHEMA”/”SESSION_SCHEMA” then I’d be very interested to know.