8

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.

  • 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
9

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
5

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.

0

This is a very common question, so I decided to turn this answer into an article.

The estimated SQL execution plan

The estimated execution plan is generated by the Optimizer without executing the SQL query. You can generate the estimated execution plan from any SQL client using EXPLAIN PLAN FOR or you can use Oracle SQL Developer for this task.

EXPLAIN PLAN FOR

When using Oracle, if you prepend the EXPLAIN PLAN FOR command to a given SQL query, the database will store the estimated execution plan in the associated PLAN_TABLE:

EXPLAIN PLAN FOR
SELECT p.id
FROM post p
WHERE EXISTS (
  SELECT 1
  FROM post_comment pc
  WHERE
    pc.post_id = p.id AND
    pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

To view the estimated execution plan, you need to use DBMS_XPLAN.DISPLAY, as illustrated in the following example:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))

The ALL +OUTLINE formatting option allows you to get more details about the estimated execution plan than using the default formatting option.

Oracle SQL Developer

If you have installed SQL Developer, you can easily get the estimated execution plan for any SQL query without having to prepend the EXPLAIN PLAN FOR command:

enter image description here

The actual SQL execution plan

The actual SQL execution plan is generated by the Optimizer when running the SQL query. So, unlike the estimated Execution Plan, you need to execute the SQL query in order to get its actual execution plan.

The actual plan should not differ significantly from the estimated one, as long as the table statistics have been properly collected by the underlying relational database.

GATHER_PLAN_STATISTICS query hint

To instruct Oracle to store the actual execution plan for a given SQL query, you can use the GATHER_PLAN_STATISTICS query hint:

SELECT /*+ GATHER_PLAN_STATISTICS */
  p.id
FROM post p
WHERE EXISTS (
  SELECT 1
  FROM post_comment pc
  WHERE
    pc.post_id = p.id AND
    pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

To visualize the actual execution plan, you can use DBMS_XPLAN.DISPLAY_CURSOR:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'))

Enable STATISTICS at the DB connection level

If you want to get the execution plans for all queries generated within a given session, you can set the STATISTICS_LEVEL session configuration to ALL:

ALTER SESSION SET STATISTICS_LEVEL='ALL'

This will have the same effect as setting the GATHER_PLAN_STATISTICS query hint on every execution query. So, just like with the GATHER_PLAN_STATISTICS query hint, you can use DBMS_XPLAN.DISPLAY_CURSOR to view the actual execution plan.

You should reset the STATISTICS_LEVEL setting to the default mode once you are done collecting the execution plans you were interested in. This is very important, especially if you are using connection pooling, and database connections get reused.

ALTER SESSION SET STATISTICS_LEVEL='TYPICAL'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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