Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cost Based Optimizer
On 05/19/2005 10:41:24 PM, Larry Elkins wrote:
> Wolfgang and others,
>=20
> Assuming 9i or higher, and work area size policy of auto and pga aggregat=
e
> target set, could this cause a plan change depending upon the workload on
> the machine?=20
Larry, according to what I know, the statement plan depends entirely on the=
statistics
(with histograms, of course) and availability of usable indexes. That is th=
e working=20
hypothesis and I haven't yet found any evidence to the contrary, not even i=
n version 10g.
The only way I can envision that Oracle is going to change the plan, depend=
ing on the machine
utilization is by changing the statistics, depending on the machine utiliza=
tion. That=20
narrows things down to dynamic sampling. Dynamic sampling does not collect =
system statistics,
as far as I know. The only way to do what you say would be for CBO to dynam=
ically sample=20
system statistics and change CPUSPEED. I am not aware of any situation in w=
hich optimizer
would do so. In addition to that, applications are often based on the parti=
cular execution plan
for the critical statements. Having a feature like that would result in hav=
ing applications
behaving unpredictably. I honestly do not see that happening.
--=20
Mladen Gogala
Oracle DBA
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 19 2005 - 23:46:12 CDT
![]() |
![]() |