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'));
Is this the
correct wayfor having the execution plan (not the explain plan)?
What are other ways for having the correct execution plan?(The one
that optimizerhas 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.
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:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂