Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.

share|improve this question
1  
"if there is any sql script which can be used as utility for this" what type of utility are you looking for? –  Derek Downey May 31 '12 at 15:41
2  
Many SQL development tools have very easy-to-use GUIs to get execution plans. Oracle SQL Developer has one. Do you have access to any such tools, or do you specifically want to do this from scripts? –  FrustratedWithFormsDesigner May 31 '12 at 15:52
    
I do have Oracle SQL Developer but would like to know scripts. –  user419534 May 31 '12 at 17:11
2  
If you have followed a course with Jonathan Lewis, then you will know that there are something like 8 different ways to get an execution plan out of Oracle. Please upvote this comment if you want me to add an answer listing all the different methods. –  Colin 't Hart May 22 '13 at 7:33

2 Answers 2

You can use the package DBMS_XPLAN:

SQL> explain plan for select * from dual;

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     2 |     2 |
|   1 |  TABLE ACCESS FULL   | DUAL        |     1 |     2 |     2 |
--------------------------------------------------------------------
Note: cpu costing is off
share|improve this answer
    
Good show... +1 –  Leigh Riffel May 31 '12 at 15:40

The EXPLAIN PLAN statement displays execution plans chosen by the optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement execution plan is the sequence of operations that the database performs to run the statement.

EXPLAIN PLAN FOR ...

Replace the ... with your SQL Statement. After running this run the following to view the plan:

SELECT * FROM table(dbms_xplan.display);

For more information see the Overview or Usage sections in the Oracle 11g Performance Tuning Guide.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.