Mr. Wright Mr. Wright - 1 month ago 12
C# Question

Executing Oracle Explain Plan in C#

Normally, executing an "explain plan" for an sql statement results in records inserted into plan_table. This works fine for me when executed via TOAD. My requirement is to execute this via my c# application (Oracle.DataAccess.Client lib). Unfortunately, the same statement that worked on TOAD is not resulting in any records inserted into plan_table when executed in C#.

The peculiar thing is that I know for sure the plan_id sequence is being consumed when executed in C# although no records are inserted into the table. I can confirm this by executing a new explain plan in TOAD and notice that plan_id sequence has skipped based on the number of attempts i made via C#. Notice how it went from 44 to 50.

The reason I need explain plan specifically is that I want to fetch the list of tables and columns involved in a given query BEFORE execution. I considered syntax parsing but it felt like reinventing the wheel given that plan_table records contained exactly the data I wanted.

For all intents and purposes, the same user is logged on TOAD and C# so I am not suspecting limited privileges. I tried with and without committing. I have also tried putting the explain plan in a stored procedure and calling that in C# with the same results. (works as expected on TOAD, but no records inserted on C#). For it to work in a stored procedure, I had to make it EXECUTE IMMEDIATE.

myoracon.Open();
OracleCommand myoracom = myoracon.CreateCommand();
myoracom = new OracleCommand("explain plan set statement_id = 'xd' for select * from employee", myoracon);
OracleTransaction trx;
trx = myoracom.Connection.BeginTransaction();
myoracom.Transaction = trx;
myoracom.ExecuteNonQuery();
trx.Commit();
myoracon.Close();


So, after all this, my question is the following: Why is explain plan in C# not resulting in records inserted into plan_table and what can I do to get it to work?

Thanks in advance!

Answer

did you control the table : TOAD_PLAN_TABLE in which user you run the script on C#

if it does not exists create it manuelly under your c# user :

CREATE TABLE **your_user**.TOAD_PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  OTHER_XML          CLOB
)