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

Question

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

Answer 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