Re: CBO problem

  • From: Nirmalya Das <nirmalya@xxxxxxx>
  • To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • Date: Mon, 17 Jul 2006 10:27:12 -0700

My queries do include bind variables....

Because of the problem I stated, I flush the shared pool once in a while and the
problem goes away.....


But this is a very expensive operation and of course NOT a permanent fix....

What alternatives do we have.....

Is Column Histograms creating the trouble? Should I turn it off?

Quoting Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>:

My guess is that it has nothing to do with IOT and only indirectly with
automatic statistics gathering. Unless you changed the default method_opt, the
10g automatic statistics gathering job will gather histograms on all columns
used in predicates (at least that is an improvement over the "for all indexed
columns" nonsense). If your queries include bind variables you could see the
result of different plans being generated due to bind variable peeking at parse
time - and then that plan being used for all other BV until the plan ages out or
gets invalidated.


Quoting Nirmalya Das <nirmalya@xxxxxxx>:


Some queries (which involves some Index Organized Tables) runs like a charm for some time and once in a while will a get a bad execution plan and will exceed the CPU limit assigned in the user profile.

We use the "Automatic Statistics Gathering" to collect statistics.

--
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com


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


Other related posts: