?
Solved

SEEING RESULTS OF EXPLAIN PLAN? CAN'T SEE IT

Posted on 2005-05-11
5
Medium Priority
?
657 Views
Last Modified: 2013-12-11
> 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


 
0
Comment
Question by:Etravels
  • 3
4 Comments
 
LVL 6

Expert Comment

by:jwittenm
ID: 13978934
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
 
LVL 6

Expert Comment

by:jwittenm
ID: 13978941
Sorry, didn't finish reading.  Please ignore!
  I'll take another look at it.
0
 
LVL 6

Expert Comment

by:jwittenm
ID: 13978960
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
 
LVL 11

Accepted Solution

by:
pennnn earned 500 total points
ID: 13979890
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question