The correct way for generating “Execution plan” for a PL-SQL statement in Oracle

All we need is an easy explanation of the problem, so here it is.

I’m studying about Explain plan and Execution plan in Oracle and according to what I’ve read so far , The explained plan can differ from the actual plan used during statement execution. So I think execution plans are more useful for optimization purposes in comparison with explain plans.

My question :

How do you usually generate execution plan for a Sql statement? After reading different articles I’m a bit confused now because in each of them a different approach is introduced !
For instance I came across this one :

explain plan 
 set statement_id = 'ex_plan1' for

select phone_number
from employee
where phone_number like '650%';

select PLAN_TABLE_OUTPUT
 from table (DBMS_XPLAN.DISPLAY(STATEMENT_ID=>'ex_plan1'));
  1. Is this the correct way for having the execution plan (not the explain plan)?

  2. What are other ways for having the correct execution plan?(The one that optimizer has chosen to execute the statement).

Thanks in advance

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Answer to your 1st question

NO this is explain plan and not execution plan.

EXPLAIN PLAN command – displays an explain plan for a SQL statement without actually executing the statement.

Answer to your 2nd Question:

Below will show execution plan of last executed SQL in the current session

Select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic'));

Below will show execution plan of using SQL_ID:

Select * from table(dbms_xplan.display_cursor('d1br5x9r1buhz',NULL,'all +predicate +cost'));

You can also use V$SQL_PLAN as ultimately this view is used to generate execution plan by DBMS_XPLAN

Please refer to below links for for clarity on this subject:

Link1
Link2

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply