SEEING RESULTS OF EXPLAIN PLAN? CAN'T SEE IT

> in sqlplus environment, I typed: set autotrace on, and got the
following
> error:
> SP2-0613: Unable to verify PLAN_TABLE format or existence
> SP2-0611: Error enabling EXPLAIN report
> SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is
> enabled
> SP2-0611: Error enabling STATISTICS report

i DO THE FOLLOWING, WHICH WORKS
Run utlxplan.sql in the schema you want to autotrace (creates the
table).
Run plustrce.sql as sys (creates the role).
grant plustrace to the schema you want to autotrace.

THEN i GO BACK & TYPE THE FOLLOWING:

SET LINES 8000
SET AUTOTRACE ON
ANALYZE TABLE ACTION_SET COMPUTE STATISTICS;

SQL >TABLE  ANAYLZED

wHER IS THE ' THE 'EXPLAIN' PLAN '  AND ALL IS SEE IS  'TABLE

HOW CAN I SEE RESULTS?

regards


 
EtravelsAsked:
Who is Participating?
 
pennnnCommented:
You generate explain plans for DML statements (select, insert, update, delete). Your example with the analyze command doesn't produce an explain plan.

If you are asking how to see the explain plan generated for a certain query, then run the following command:
select * from table( dbms_xplan.display );

Example:
SQL> explain plan for select * from your_table;
Explained.

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

This will give you the explain plan for the query you want (in this case - "select * from your_table").
Or you can just do what you've done in your example - set autotrace on - and then run the query you want explained.
Or if you want just the explain plan without actually running a query then you can run:
SQL> set autotrace traceonly explain
-- Then run your query and it will show you the execution plan.

Hope that helps!
0
 
jwittenmCommented:
from the $ORACLE_HOME/rdbms/admin directory, log into sqlplus as the user you want to do the explains and run '@utlxplan'.  This will create the explain_plan table for user sys.  
0
 
jwittenmCommented:
Sorry, didn't finish reading.  Please ignore!
  I'll take another look at it.
0
 
jwittenmCommented:
At this point, I'm not sure what the issue is.  Are you still not able to set autotrace on?  Are you not able to see the statistics?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.