How to view the execution plan in Oracle?

1 year ago
Ava Mitchell
1 minute

There are two methods available in Oracle database to view the execution plan:

1. By using the EXPLAIN PLAN statement, you can add “EXPLAIN PLAN FOR” before your SQL query, then execute the query. The system will generate an execution plan and store it in the PLAN_TABLE table. You can query the PLAN_TABLE table to view the execution plan. Sample code is as follows:

EXPLAIN PLAN FOR
SELECT * FROM table_name WHERE condition;

SELECT * FROM TABLE(dbms_xplan.display);

2. Using DBMS_XPLAN package: You can view the execution plan directly using the DBMS_XPLAN package, without needing to use the EXPLAIN PLAN statement. Below is an example code:

SELECT * FROM table_name WHERE condition;

SELECT * FROM TABLE(dbms_xplan.display_cursor);

You can easily view the execution plan of your SQL queries using either of the two methods, in order to optimize the query performance.

bannerAds