Home » SQL & PL/SQL » Client Tools » Running Explain Plan in Toad (Oracle 9i)
Running Explain Plan in Toad [message #318547] Wed, 07 May 2008 02:47 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am first time trying to run explain plan.
I run it on sql prompt and it only gave the message EXPLAINED. I was not able to see the details.
I tried in TOAD version 7.5.2. It said run TOADPERP.sql.
I did that too.
Now when I am running explain plan for a selected sql, it only shows the Explain Plan window with no output but only 'TSMSql' written there in second window.

Please advice as how to get the output from explain plan.

Thanks,
Soni
Re: Running Explain Plan in Toad [message #318556 is a reply to message #318547] Wed, 07 May 2008 03:11 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Dont trust toad , do it in SQL*PLUS

ex.
SQL> explain plan for select 'x' from dual;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1388734953

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

8 rows selected.
icon4.gif  Re: Running Explain Plan in Toad [message #318576 is a reply to message #318556] Wed, 07 May 2008 03:35 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I did that too but it gives the below error.
SQL> select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display)
                               *
ERROR at line 1:
ORA-00904: invalid column name


Do I need some previlege...if yes then what.
Or does it means that the package DBMS_XPLAN is not getting executed. I have tried running this using system/manager as well as scott/tiger..... but it didn't.

I looked into the folder C:\oracle\ora90\rdbms\admin for the package $ORACLE_HOME/rdbms/admin/dbmsxpln.sql but its not in that folder.

I do not know why its not there ..... Can anyone send me the package dbmsxpln.sql present in Oracle 9i.
I will run it and then try to run Explain Plan.

Thanks in advance,
Soni

[Updated on: Wed, 07 May 2008 04:27]

Report message to a moderator

Re: Running Explain Plan in Toad [message #318592 is a reply to message #318576] Wed, 07 May 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is not in your rdbms/admin directory then it does not exist for your version.

Regards
Michel
Re: Running Explain Plan in Toad [message #318594 is a reply to message #318592] Wed, 07 May 2008 04:35 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Yes, I googled and found that dbmsutil is for oracle 9i and dbms_xplan for oracle 10g.
I had already mentioned in my post that I am using 9i only. But the answer given by 'tahpush' suggested to run
select * from table(dbms_xplan.display);

It confused me. Anyway, but now I am in the same position, please suggest me as how to run explain plan...my question is as per my first post.

Thanks,
Soni
Re: Running Explain Plan in Toad [message #318599 is a reply to message #318547] Wed, 07 May 2008 04:41 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
First confirm you are getting the explain plan output at
SQL*Plus Prompt.

SQL> set autotr ( here you can press return to see the syntax)

sql>set autotrace on explain

sql>select sysdate from dual;

You need to create plan_table to store explain plan (utlxplan.sql)

explain plan set statement_id='XXX' for
select sysdate from dual;

Then you can acess plan_table for statement_id='XXX' or oracle supplied utlxplp.sql

Regards,

MSMallya

[Updated on: Wed, 07 May 2008 04:55]

Report message to a moderator

Re: Running Explain Plan in Toad [message #318628 is a reply to message #318599] Wed, 07 May 2008 06:19 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Thank you so much msmallya,
I can access the explain plan now.
As this is first time I was able to run explain plan...feeling good.
Can you guide me as how to understand the various values I got from explain plan. How to understand them and compare.

[Updated on: Wed, 07 May 2008 06:21]

Report message to a moderator

Re: Running Explain Plan in Toad [message #319300 is a reply to message #318547] Fri, 09 May 2008 12:45 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Try here (Tuning Guide).

And here (ORAFAQ).
Re: Running Explain Plan in Toad [message #319449 is a reply to message #319300] Sun, 11 May 2008 07:30 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Thanks drewsmith70,
Actually, I needed a link like this ...thanks a ton.


Regards,
Soni
Previous Topic: TOAD query that Mirrors the AUD: Interface CIP report
Next Topic: TOAD Formatter Plus v4.8.8
Goto Forum:
  


Current Time: Sat Dec 10 01:22:56 CST 2016

Total time taken to generate the page: 0.08340 seconds