OraFAQ Forum: Client Tools » Running Explain Plan in Toad

Home » Other » Client Tools » Running Explain Plan in Toad (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Running Explain Plan in Toad [message #318547]

Wed, 07 May 2008 02:47

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

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

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.

Report message to a moderator

Re: Running Explain Plan in Toad [message #318576 is a reply to message #318556]

Wed, 07 May 2008 03:35

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

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

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

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

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