Oracle Explain Plan

When investigating performance, an explain plan can be a useful tool to understand what steps are costing the most and therefore hurting performance the most.

Syntax for generating the explain plan

explain plan for 
/* provide the body of the select/update/insert/merge statement here*/
;

If executed successfully, you will see output "plan FOR succeeded."

Syntax for retrieving the explain plan

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This command retrieves the last explain plan. You can uniquely identify an explain plan using the Plan Hash Value.

Interpreting the explain plan itself is not trivial. But in some cases there are clear problems that can be a starting point for figuring why something is slow.