Re: SQL tuning nightmare - db file sequential reads

Mladen Gogala wrote:
> On Thu, 06 Jul 2006 02:20:03 +0000, Mladen Gogala wrote:
>
> > I'm not really
> > sure what it does and how exactly does it affect CBO calculations.
>
> Actually, this is a lie. I do know very well what it does, but my point is
> that it
> a) Isn't documented
> b) FIRST_ROWS_N parameter affects mostly joins and is far from trivial.
>
> This parameter <FIRST_ROWS_N> first computes the expected number of rows
> for the query, than calculates the percentage of the total number of rows
> which N represents. Then, the optimization is restarted and this
> percentage is applied to all the cardinalities in the query (histograms,
> statistics and alike).
>
> Jonathan Lewis explained that to me in a private communication when I was
> asking questions about his book. Oracle didn't do such a good job
> documenting this. I hate using things which I do not understand. The only
> exception would be "beam me up Scottie" device which would rid me of @#$!
> I-95 in the morning. Unfortunately, I'm ready and willing, but the device
> is not available yet.

Yea in the introduction of the first chapter in his book Jonathan mentions it, although in very few words. It is also important to distinguish between first_rows and first_rows_n, these are two different optimizer modes and at least dont expect them both work the same way.
With all that I can say that at least for me first_rows_n usually works. I'm not eager to get to know always every single detail, although it would be nice of course to be able to get that info somewhere in Oracle docs. On the other hand if you use optimizer mode all_rows then expecting wide use of indexes and range scans is a bit strange, because the very name "all_rows" somehow contradicts this. So yea I'm a bit of a half blind man walking behind first_rows_n but I'v also been in Jonathan's seminar and have his book, quickly scanned it and got an at least an overview of what's going on. Yea and of course got only overview not because book haven't details but because I scanned it too fast.

Gints Plivna
http://www.gplivna.eu Received on Thu Jul 06 2006 - 02:22:06 CDT