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:  

1. One cannot develop on a smaller machine and expect to have the same

   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-l