How to check Execution plan of a SQL statement ( EXPLAIN PLAN ) in Oracle

In Oracle, you can check the execution plan of a SQL statement using various methods. Here are some common approaches:

1. EXPLAIN PLAN Statement: The EXPLAIN PLAN statement is used to display the execution plan that Oracle Database follows to execute a SQL statement. You can use it to examine the estimated execution plan without actually executing the statement.

Here's a basic example:

EXPLAIN PLAN FOR SELECT * FROM your_table WHERE your_condition; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

After executing these commands, you can view the execution plan using the DBMS_XPLAN.DISPLAY function.

2. AUTOTRACE: The AUTOTRACE feature in SQL*Plus or SQL Developer allows you to automatically display the execution plan and statistics for a SQL statement after it's executed. You can enable AUTOTRACE by executing the following command before running your SQL statement:

SET AUTOTRACE ON;

After executing your SQL statement, Oracle will display the execution plan and statistics automatically.

3. DBMS_XPLAN.DISPLAY_CURSOR: This function can be used to display the execution plan of a SQL statement that is currently cached in the cursor cache. You can specify the SQL ID or SQL handle of the statement you want to inspect.

Here's an example:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALL'));

Replace 'your_sql_id' with the SQL ID of the statement you want to check.

4.Enterprise Manager (EM) Database Express: If you're using Oracle Enterprise Edition, you can use Enterprise Manager Database Express (EM Express) to view execution plans graphically and analyze SQL statements.

These methods allow you to examine the execution plan of SQL statements in Oracle, helping you understand how the database executes your queries and optimize them for better performance.