Re: autotrace issue

Yassin, no, it isn't normal. Once you have parsed and optimized statement, Oracle will resuse the same plan with utter disregard for the actual value of the bind variables. There is, however, a thing known as "bind value peeking" or a "peeping Tom optimizer" introduced in 9i, but without the lady godiva support. Optimizer looks into the value of the bind variables and uses the values there to query the column histograms. That causes an additional problem: if the first value is not representative for the majority of cases and there is very little you can do about it, short of recalculating statistics for one of the underlying tables, which is likely to influence all queries referencing that table.

BTW, what makes hard parse such an expensive beast is exactly the enormous complexity of CBO. It has to look into all those tables to gather information like num_rows, leaf_blocks, clustering factor, value distribution for all columns that are referenced in the "WHERE" clause and have histograms, to look for the functional index and perform quite a complex task. You want to use bind variables to avoid invoking the optimizer at any time but the time of the very first execution.

On 10/14/2005 07:37:08 AM, Yasin Baskan wrote:
>
>
> Isn't it normal that the plan changes with the actual value in the where
> clause. That is what the optimizer does. You can use a bind variable
> instead of that literal and see what the plan is.

-- 
Mladen Gogala
http://www.mgogala.com


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