How to view the execution plan in Oracle?

1 year ago

Olivia Parker

1 minute

In Oracle, you can view the execution plan using the following methods: 1. Use the EXPLAIN PLAN statement: Add EXPLAIN PLAN FOR before the SQL statement, then use SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) to display the execution plan. For example:

EXPLAIN PLAN FOR SELECT * FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. Enable AUTOTRACE feature: You can enable the AUTOTRACE feature in tools like SQL Developer to display the execution plan. Just select the “Enable Autotrace” option when executing SQL statements in the tool.

3. Use SQL Trace: You can enable SQL Trace to collect the execution information of SQL statements, including the execution plan. Use the following statement to enable SQL Trace:

ALTER SESSION SET SQL_TRACE = TRUE;

Following that, utilize tools like tkprof to analyze the trace file, which contains information about the execution plan. Regardless of the method used, the execution plan will be displayed in the query results, showing the sequence of operations, types of operations, and the indexes used.