RE: script to see explain plan

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_at_comcast.net 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

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l