Re: autotrace issue

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: yasbs@xxxxxxxxxxxxxx
  • Date: Fri, 14 Oct 2005 12:06:11 +0000

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


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

Other related posts: