Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: full-scan vs index for "small" tables
[Cary Millsap] Ah, but you should (in the "must" sense) have exactly the same plans in test as you have in production. It is through the manual manipulation of statistics in your test system that you ensure this. (And we wrote Laredo so that we could Ensure it.) The CBO doesn't care how big your tables are and how their data distributions work out. It cares only what you TELL it about your tables, distributions, etc.
Cary isn't it more than just statistics though? Different parameter settings between Dev and Prod will produce different plans as well, no?
Thanks!
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap
Sent: Wednesday, June 28, 2006 11:10 PM
To: oracle-l_at_freelists.org
Subject: RE: full-scan vs index for "small" tables
On 06/28/2006 12:00:17 PM, Cary Millsap wrote:
> > ...need stable sql plans.
>
> But the whole point of the CBO is that execution plan stability is
> inferior to execution plan adaptation to changing circumstances. As
> Jonathan Lewis points out very well, all it takes is the insertion of
a
> single row to make the True Best Plan change from one execution to
> another.
Cary, while True Best Plan(TM) can change from one execution to the
next, there are also some problems that are introduced by this:
execution path on another machine. That makes explain plan statement
practically useless. The same applies to performance testing. This, in
turn, complicates development process immensely.
[Cary Millsap] Ah, but you should (in the "must" sense) have exactly the same plans in test as you have in production. It is through the manual manipulation of statistics in your test system that you ensure this. (And we wrote Laredo so that we could Ensure it.) The CBO doesn't care how big your tables are and how their data distributions work out. It cares only what you TELL it about your tables, distributions, etc.
2) Gathering statistics introduces an element of randomness into production
environment. Some data is loaded into a big table in a disorderly fashion,
it pushes the clustering factor over the limit and the index is no longer
used. Tuning is complex, requires a cooperation from development and is
generally regarded as an exercise in futility, as some future data change
will send us all into an overdrive yet again.
[Cary Millsap] But if such randomnesses are actually present in your system, shouldn't you be thankful that CBO is smart enough to adapt to them? The tacit assumption behind your point seems to be that WE know the best plan, and that plan is always the same. It's not. The best plan for a query on a big table that is loaded in a disorderly fashion may actually be different than the best plan for the same big table loaded a different way. RBO is too dumb to notice. CBO notices, but then we yell at it for being smart. What I think people really complain about is that the plans change sometimes without their being warned about it. Again, this is why we wrote Laredo. Why guess? ...When You Can Know.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 29 2006 - 13:02:48 CDT
![]() |
![]() |