Old PLAN_TABLE in EBS Vision

While attempting to generate an explain plan in an R12 Vision database, I instead generated the following error:

APPS@R12VIS(11.1.0.7)>set autotrace traceonly explain
APPS@R12VIS(11.1.0.7)>select count(*) from DUAL;

Execution Plan
----------------------------------------------------------
ERROR: an uncaught error in function display has happened; please contact Oracle support
       Please provide also a DMP file of the used plan table PLAN_TABLE
       ORA-00904: "OTHER_TAG": invalid identifier

Well, okay, I wasn't really querying the DUAL table, but it makes the example shorter. Since this was only happening when I was connected as the APPS user, I suspected a private PLAN_TABLE had been created for APPS, and sure enough:

APPS@r12vis(11.1.0.7)>select owner            
  2  , object_name
  3  , object_type
  4  from dba_objects
  5  where object_name like 'PLAN_TABLE%';

OWNER		OBJECT_NAME		       OBJECT_TYPE
--------------- ------------------------------ --------------------
SYS		PLAN_TABLE$		       TABLE
PUBLIC		PLAN_TABLE		       SYNONYM
APPS		PLAN_TABLE		       TABLE

It's not too unusual to have a private PLAN_TABLE in a schema, even in a post-9i database. Oracle has definitely kept utlxplan.sql up to date in 11g. What was surprising to me was the structure of the table:

APPS@r12vis(11.1.0.7)>desc plan_table;
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 STATEMENT_ID								    VARCHAR2(30)
 TIMESTAMP								    DATE
 REMARKS								    VARCHAR2(80)
 OPERATION								    VARCHAR2(30)
 OPTIONS								    VARCHAR2(30)
 OBJECT_NODE								    VARCHAR2(128)
 OBJECT_OWNER								    VARCHAR2(30)
 OBJECT_NAME								    VARCHAR2(30)
 OBJECT_INSTANCE							    NUMBER(38)
 OBJECT_TYPE								    VARCHAR2(30)
 SEARCH_COLUMNS 							    NUMBER(38)
 ID									    NUMBER(38)
 PARENT_ID								    NUMBER(38)
 POSITION								    NUMBER(38)
 OTHER									    LONG

There are a lot of fields missing from that table. A quick reference check reveals that this is not a 9i version of the PLAN_TABLE, not even an 8i version, but a vintage Oracle 7.3 PLAN_TABLE. This is actually pretty cool, since it implies that Oracle's been eating its own dog food with respect to upgrading its demo instance. The Vision database clearly has a long and storied history. ;-)

The solution to my original problem, of course, is to just drop the old plan table. If you'd rather keep a static PLAN_TABLE around for the APPS schema, it's still a good idea to drop it and recreate it with the 11g utlxplan.sql.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*