| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: full-scan vs index for "small" tables
The implicit assumption, I guess, is that data will tend to stabilize over time. For example, your gazillion-row table eventually stays at a gazillion rows and stays there because your data entry and purging rates give you equilibrium. ...And of course, there's the assumption that skew in your data and all that stabilize as well. In these cases, a single set of "representative" statistics can last a lifetime.
For those in whose databases this is not the case, we have dynamic sampling (with a nod to Nuno's point here), stored outlines, and hints that you can store now in tables instead of in the application source code itself.
All combined, I'd argue that this picture is a lot better than what we used to have to endure in the days of RBO. There's just this inconvenience of having to inform the Oracle query optimizer about your data, the process for doing which involves so many complicated decisions that probably few people will really ever do it very well.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Hotsos Symposium 2007 / March 4-8 / Dallas Visit www.hotsos.com for curriculum and schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis Nedzinskas
Sent: Wednesday, June 28, 2006 1:16 PM
To: oracle-l_at_freelists.org
Subject: RE: full-scan vs index for "small" tables
But isn't it such that once you are happy you stop collecting statistics?
Else it is simply a craziness: one may collect statistics when some tables are empty, etc, etc.
Even the collection phase itself is a craziness:
- Suppose you set option not to invalidate cached sql plans. - But CBO is primarally for DW - Which means some sql is literal - Which means this sql may get into cache just in the middle of statistics collection - Which means that CBO will see old statistics for some tables and new statistics for another tables.Imho, this is just crazy.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto
Sent: 28. júní 2006 14:21
Cc: oracle-l_at_freelists.org
Subject: Re: full-scan vs index for "small" tables
Cary Millsap wrote,on my timestamp of 28/06/2006 11:44 PM:
>
> RBO is dramatically inferior to CBO in every case except for the one
> where the operational manager doesn't do a good job of making sure
> that the statistics are a reasonable representation of the production data.
>
Yes Cary, I hear what you say and agree with it. But if a significant portion of the data is dropped overnight and then is re-created, is the ops person then supposed to reset stats with every change in volume in the database? Based on what criteria should (s)he act, then? Dynamic sampling is not always the perfect solution.
The problem I'm having is with the definition of "reasonable representation" of the production data, when such data is highly volatile. Particularly with 9ir2...
-- Cheers Nuno Souto in sunny Sydney, Australia dbvision_at_iinet.net.au -- http://www.freelists.org/webpage/oracle-l Fyrirvari/Disclaimer http://www.landsbanki.is/disclaimer -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 28 2006 - 15:03:02 CDT
 |
 |