Friday, 14 December 2012

Explain Plan : How to get execution plan of a query in Oracle?


What is Explain Plan?


One of the task of optimizer is to generate the query execution plan. Execution plan tells, how oracle is going to process that query. Oracle provides a tool to get the query execution plan called Explain Plan. You can use this to analyse the query and tune it to improve performance.



How to get query execution plan?

Follow these steps to get the execution plan. Use "explain plan for" command with the sql statement for which you want to generate the plan. This will store the execution plan in the table "PLAN_TABLE". This table is created during oracle installation. If it's not there execute UTLXPLAN.SQL using sys user to create the table  or ask your dba to create that for you. One thing to note here is that only one execution plan is stored in the plan table. Every time you execute explain plan command, old entries are deleted and new entries are created.

SQL> explain plan for select * from users, groups;

Explained.


Now we have the execution plan in our plan table, we will use dbms_xplan package to format the contents of the PLAN_TABLE. This will print the execution plan in more readable and easy to understand format. 

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 381906587

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     6 |   456 |     8   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|        |     6 |   456 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | GROUPS |     2 |    76 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |        |     3 |   114 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | USERS  |     3 |   114 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

15 rows selected.


Using explain plan we can see how query is going to be executed internally. If we have used any hints we can see whether optimizer is going to use it or not?

No comments:

Post a Comment

back to top