TOADS Explain plan

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • raviva
    New Member
    • Jan 2008
    • 19
    #1

    TOADS Explain plan

    Hi,

    I want the explain plan. But when I clicked on the explain plan it says ORA-02404: specified plan table not found. I investigated on net and some books. I was asked to load the utlxplan.sql. I did that but of no use. I also changed the name of the plan table in TOAD, still no use. New thing I heard recently is to run TOADPREP.SQL. But this script is not available on our server($ORACLE_ HOME/rdbms/admin). Can someone please suggest me what to do in order to get the explain plan.

    Thanks,
    Tags: None
    • Dave44
      New Member
      • Feb 2007
      • 153
      #2
      Can you query the plan table by itself? you should be able to run something like
      select * from plan_table
      and get no rows returned. if not you either havent created the table or dont have privileges to use it.

      another thing to check is in the properties if toad has called the table TOAD_PLAN_TABLE rather than just plan_table.

      the other thing you are calling (once you have explained a query) is this
      select * from table(dbms_xpla n.display);

        Comment

        • QVeen72
          Recognized Expert Top Contributor
          • Oct 2006
          • 1445
          #3
          Hi,

          First Create a Plan_Table, use the above said utility or you can run this :

          [code=oracle]
          CREATE TABLE PLAN_TABLE (
          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),
          OPTIMIZER VARCHAR2(255),
          SEARCH_COLUMNS NUMBER,
          ID NUMBER(38),
          PARENT_ID NUMBER(38),
          POSITION NUMBER(38),
          COST NUMBER(38),
          CARDINALITY NUMBER(38),
          BYTES NUMBER(38),
          OTHER_TAG VARCHAR2(255),
          PARTITION_START VARCHAR2(255),
          PARTITION_STOP VARCHAR2(255),
          PARTITION_ID NUMBER(38),
          OTHER LONG,
          DISTRIBUTION VARCHAR2(30)
          );
          [/code]

          Regards
          Veena

            Comment

            • raviva
              New Member
              • Jan 2008
              • 19
              #4
              Thank you very much Veena it worked!!

              regards,
              Ravi

              Originally posted by QVeen72
              Hi,

              First Create a Plan_Table, use the above said utility or you can run this :

              [code=oracle]
              CREATE TABLE PLAN_TABLE (
              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),
              OPTIMIZER VARCHAR2(255),
              SEARCH_COLUMNS NUMBER,
              ID NUMBER(38),
              PARENT_ID NUMBER(38),
              POSITION NUMBER(38),
              COST NUMBER(38),
              CARDINALITY NUMBER(38),
              BYTES NUMBER(38),
              OTHER_TAG VARCHAR2(255),
              PARTITION_START VARCHAR2(255),
              PARTITION_STOP VARCHAR2(255),
              PARTITION_ID NUMBER(38),
              OTHER LONG,
              DISTRIBUTION VARCHAR2(30)
              );
              [/code]

              Regards
              Veena

                Comment

                Previous template Next
                Working...