I don't use explain plan. There are just too many reasons why explain
plan will come up with a plan that is different from the one that is
actually being executed. You mentioned one: bind variable type
mismatch. explain plan treats all bind variables as of type varchar2.
Another reason is that explain plan does not peek and bind values as
the parse for actual execution does. In my presentation "What is new
in Oracle 9i" I show a sql where I get 3 different plans - one from
explain plan, another one with a frequently occurring bind value and
yet another one with an infrequently occurring bind value.
At 09:44 PM 10/5/2006, ryan_gaffuri@xxxxxxxxxxx wrote:
We have been running into repeated cases when we join a regular table to a GTT where we get a different explain plan than we get an actual plan in a trace file.
I believe Cary showed a case 1-2 years ago where if you have a column that has an index on a number field and you pass it a varchar in a bind variable you can get the wrong plan.
How often do people see inaccuracies in the explain plan?
Regards
-- //www.freelists.org/webpage/oracle-l
