Skip to Main Content
  • Questions

  • Difference between explain and execute plan and actual execute plan

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Saket.

Asked: February 21, 2018 - 4:10 am UTC

Last updated: March 05, 2018 - 3:50 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi, I have often got questions around explain plan and execute plan. As per my knowledge, explain plan gives you the execute plan of the query. But I have also read that Execute plan is the plan which Oracle Optimizer intends to use for the query and actual execute plan might be different; if this is the case then how do I get the ACTUAL EXECUTE PLAN which the optimizer used for executing the query (after execution). Thanks in advance.

and Chris said...

Say you're planning a long car journey tomorrow. You fire up your favourite mapping program and ask it to find the quickest route.

This is your explain plan. It's a prediction of the route you'll take when you leave in the morning.

Tomorrow comes. You get in the car, turn the radio on and hear there's been a massive accident. The first highway you were planning on taking is closed. You don't want to sit in traffic for hours. So you turn to your satnav, asking it to choose a route avoiding this road. You then follow this new path.

The route you drove is your execution plan. The actual actions you took to get from A to B.

Explain = Predication
Execution = Reality

There are lots of reasons the prediction could turn out to be wrong. Tom discusses these at:

https://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

There are many ways to get the execution plan. Often the easiest for sharing your plan on forums like this to do the following:

set serveroutput off

select /*+ gather_plan_statistics */* 
from   your_query;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


If you want to know about other ways, read:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great explanation

Saket Parab, March 05, 2018 - 6:15 am UTC

As always, explained nicely in simple words. Thanks! Chris, could you pls tell me if I can get to know the exact EXECUTION plan for the query before executing the query? if not how can I get the same after the query execution? And can I force the query to follow an execution plan using hints?(I have heard oracle will ignore the hints if it does not find them efficient)
Chris Saxon
March 05, 2018 - 3:50 pm UTC

could you pls tell me if I can get to know the exact EXECUTION plan for the query before executing the query?

Think about it this way:

Can you get the exact route you took in the car before your journey?

If you answered yes to that, you need to start cashing in on your fortune telling powers!

And can I force the query to follow an execution plan using hints?

Ye-es, buuuuut....

Typically you need waaaay more hints than you think you do. For example, this is a simple query with an "obvious" plan:

select * from t
where  x = 1;

--------------------------------------------------------------------------------------------   
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |   
--------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT            |              |       |       |     1 (100)|          |   
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    26 |     1   (0)| 00:00:01 |   
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0014790 |     1 |       |     1   (0)| 00:00:01 |   
--------------------------------------------------------------------------------------------


But to ensure you keep the same plan, you need all these hints:

  /*+                                                                                          
      BEGIN_OUTLINE_DATA                                                                       
      IGNORE_OPTIM_EMBEDDED_HINTS                                                              
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')                                                    
      DB_VERSION('11.2.0.4')                                                                   
      OPT_PARAM('_optimizer_cost_model' 'fixed')                                               
      OPT_PARAM('_fix_control' '6670551:0')                                                    
      ALL_ROWS                                                                                 
      OUTLINE_LEAF(@"SEL$1")                                                                   
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))                                             
      END_OUTLINE_DATA                                                                         
  */


Calling them hints is misleading. They're really directives. Oracle Database has to follow them if it can. If it's "ignoring" a hint, then it's invalid for some reason.

See this presentation from Maria Colgan for more details on this:

https://www.slideshare.net/MariaColgan1/harnessing-the-power-of-optimizer-hints
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.