Home » SQL & PL/SQL » SQL & PL/SQL » doubt about explain plan
icon5.gif  doubt about explain plan [message #363210] Fri, 05 December 2008 06:37 Go to next message
raja.or.king
Messages: 24
Registered: November 2008
Location: GJ 5
Junior Member
In one of the book, I read that EXPLAIN PLAN command may give different result from the actual execution plan.

is it so?
Re: doubt about explain plan [message #363216 is a reply to message #363210] Fri, 05 December 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: doubt about explain plan [message #363330 is a reply to message #363216] Fri, 05 December 2008 18:35 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There are many things that affect an execution plan, and some of those things are parameters that can be set at the session level. It is possible that your application has different parameter settings to the session where you are performing Explain Plan.

In v10 and later, Oracle can perform dynamic sampling of tables at run time - this can make the SQL choose a different plan on two separate executions.

There are lots of subtle things that can happen to make two executions of the same SQL have different plans. They happen infrequently, but you can never GUARANTEE that the plan you see from running Explain Plan is the one the application is using.

The only sure way to tell is to extract the actual plan used from the V$SQL_PLAN view or to use SQL Trace and TK*Prof.

Ross Leishman
Re: doubt about explain plan [message #363356 is a reply to message #363210] Sat, 06 December 2008 00:54 Go to previous message
raja.or.king
Messages: 24
Registered: November 2008
Location: GJ 5
Junior Member
Idea
Thanks all
Previous Topic: Impace of statistics collection
Next Topic: How to Fetch data on the basis of Unicode Characters
Goto Forum:
  


Current Time: Mon Feb 10 11:46:59 CST 2025