Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
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: 52
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
Report message to a moderator
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: 619
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.
Report message to a moderator
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: 52
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: 15244
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
If it is not in your rdbms/admin directory then it does not exist for your version.

Regards
Michel
Report message to a moderator
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: 52
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
Report message to a moderator
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: 30
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: 52
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: 19
Registered: April 2008
Location: New Hampshire
Junior Member
Try here (Tuning Guide).

And here (ORAFAQ).
Report message to a moderator
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: 52
Registered: May 2006
Location: Mumbai
Member
Thanks drewsmith70,
Actually, I needed a link like this ...thanks a ton.


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

[ Syndicate this forum (XML) ] [ ]

Current Time: Sat May 17 05:38:44 CDT 2008

Total time taken to generate the page: 0.04682 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.