How to view execution plan?

There are various methods of displaying execution plan. Here is few of them.

1) Using EXPLAIN PLAN and setting STATEMENT_ID and then display execution plan using PLAN_TABLE.
EXPLAIN PLAN SET statement_id = 'TEST' FOR
select * FROM scott.x;

SELECT LPAD(' ', 2*LEVEL) ||
operation || ' ' ||
options || ' ' ||
object_name plan
FROM plan_table
WHERE statement_id = 'TEST'
CONNECT BY prior id = parent_id AND statement_id = 'TEST'
START WITH id = 0;

PLAN
——————————————————————————–
SELECT STATEMENT
TABLE ACCESS FULL X

2) Using scripts provided by Oracle, it will output the most recent execution plan.
EXPLAIN PLAN FOR select * FROM scott.x;

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2941724873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| X | 1 | 13 | 2 (0)| 00:00:01 |
————————————————————————–

8 rows selected.

3) Using table(dbms_xplan.display) to display the last execution plan.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2941724873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| X | 1 | 13 | 2 (0)| 00:00:01 |
————————————————————————–

8 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s