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.

About these ads

6 thoughts on “EXPLAIN PLAN FOR Anomaly

  1. The documentation states
    “If you omit INTO altogether, then the database assumes an output table named PLAN_TABLE in your own schema on your local database.”
    I wonder whether it is connected to SET AUTOTRACE ON, which doesn’t cater for specifying a PLAN_TABLE schema. Using the logon schema rather than current_schema would allow you to use AUTOTRACE if you didn’t have privileges on the plan_table of the current_schema

  2. Thanks for your comment. I’ve just done a bit more digging in trace files to try and find what goes on, but I’m not seeing anything that helps to explain what mechanism Oracle uses to make “insert into plan_table…” not see PLAN_TABLE in the current_schema.

    Below is a cut-down version of the trace file, which doesn’t show any sign of changing schema for the duration of the EXPLAIN PLAN FOR command or the prefixing of the PLAN_TABLE with my username.

    PARSING IN CURSOR #3 len=35 dep=0
    explain plan for select * from dual
    END OF STMT
    =====================
    PARSING IN CURSOR #4 len=586 dep=1
    insert into plan_table
    END OF STMT
    =====================
    PARSING IN CURSOR #2 len=41 dep=1
    SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL
    END OF STMT
    =====================

    I think I’ll have to conclude that there is something happening here that I’m not allowed/supposed/able to see.

    Thanks again for your comment.

  3. Did you have the rest of the parsing line ? The UID element is the relevant component. I did a run in XE. Logged in as ‘GARY’ (uid 40), I switch scheme to HR (uid 33). The uid for the explain plan statement is 33 (so it sees HR’s employees table), but the insert into plan_table is uid 40 (‘GARY’). If I then try to query DBMS_XPLAN, it parses that as uid 33 and fails. AUTOTRACE fails too. It works if I switch the current schema back to my own.

    PARSING IN CURSOR #1 len=35 dep=0 uid=40
    alter session set current_schema=hr

    PARSING IN CURSOR #3 len=40 dep=0 uid=33
    explain plan for select * from employees

    PARSING IN CURSOR #1 len=586 dep=1 uid=40
    insert into plan_table ….

    PARSING IN CURSOR #1 len=39 dep=0 uid=33
    select * from table(dbms_xplan.display)

    • Hi Gary,

      Thanks for pointing me in the right direction. I’ve just looked at the UIDs and see the differing values for the “explain plan for” and “insert into plan_table” statements.

      Cheers,

      Martin

  4. Hi Nash

    To use current_schema when explain the query plan, you need to use the the prefix of your connected user in the dbms_xplan query.

    Example

    If you are conected with sys, and need to see the plan for query tables in schem user1, you can set the cuurent_schema to user1.

    alter session set cuurent_schema=1;

    explain plan for

    /

    Then when query dbms_xplan use the prefix sys on plan_table

    ex.:

    SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(‘SYS.PLAN_TABLE’, NULL,’ALL’));

    I hope that help.

    My best regards

    Cristiano

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s