Oracle Execution Plan

Question

I am using Oracle 11g and Toad for Oracle. How can I display execution plan for queries? In Sql server management studio execution plan can be displayed as graphical format. Is there any functionality/tool like that on Toad for oracle?

Shu

Answer 1

CTRL-E

Make sure you've ended the query with a semi-colon (and the query above)

Edit:

You need to set-up the TOAD plan table for use. If you think it's already setup on your DB then you may just need to be granted access. Alternatively in my slightly older version of TOAD it's under:

Database --> Administer --> Server Side Objects Wizard. From here you can create the plan table(s) in a schema that you choose.

Courtesan

I have an error. "Ora-02403: plan table does not have correct format" – Piccard

ORA-02403 means that the plan table was created using one version and at a later date the database was upgraded to a version that requires a different plan table. Find the UTLXplan.sql file for your current version, drop the existing plan table, and rebuild it using the script. – Marley

Answer 2

You should create the PLAN_TABLE using a script provided by Oracle which is named UTLXPLAN.SQL and is located in one of the installation folders on the database server.

Then, you should use the EXPLAIN PLAN statement for generating a plan for a SQL statement, like this: EXPLAIN PLAN SET STATEMENT_ID = 'your_identifier_for_this_plan' FOR ... your statement ... ;

Then, you can use either a select from PLAN_TABLE (usually using a hierarchical query) or the DBMS_XPLAN.DISPLAY_PLAN procedure to display the plan. In the same folder where the UTLXPLAN.SQL file is located, there usually exist examples of using this procedure.

Also, in SQL*PLUS you can use the SET AUTOTRACE feature.

Gereron

Answer 3

For TOAD FOR ORACLE

this helped me How do I view the Explain Plan in Oracle Sql developer?, I just write what they did in sql developer and wrote in the toad editor and then execute.

Example

explain plan for select field1, field2 from TABLE_NAME;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Abba

Answer 4

Check that all queries end with a semicolon, put the cursor on the query you want to analyze and hit CTRL-E.

The first time you could get a popup that asks for the name of the plan table, it suggests TOAD_PLAN_TABLE but it's better to use the standard Oracle table PLAN_TABLE that should be already available. So enter PLAN_TABLE in place of TOAD_PLAN_TABLE (do not specify a schema) and hit OK. You should get a message saying that the object already exists: hit OK again to acknowledge it. Now try CTRL-E again and you'll get the explain plan.

To view/change the currently configured plan table name go to menu "View / Toad Options / Oracle General".

Ultravirus