Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL tuning nightmare - db file sequential reads
On Wed, 05 Jul 2006 14:25:29 -0700, Gints Plivna wrote:
>
> Couldn't at least most of these issues be solved with rather simple
> algorithm. Choose instance wide optimizer mode (first_rows_n or
> all_rows) depending on your application type. Then for all other
> statements that needs other optimizer mode give explicit hint. I think
> the problem in rather many databases is that either default choose (for
> 9i) is left (i.e. most of the times it effectively means all_rows) or
> incorrect value is chosen.
Gints, you have a cunning plan but I don't really like "first rows" hint and optimizer mode because it isn't really documented. I'm not really sure what it does and how exactly does it affect CBO calculations. What does it really do, in terms of single block reads, multiblock reads and statistics. Final cost of a SQL statement is calculated as a weighed sum of different costs. What do first rows parameters affect and how? Without knowing precisely what it does, the whole thing looks more like exorcism then like a scientific approach. I mentioned non-existing documentation. This is one such example. Now you intrigued me! This weekend, I'll do some R&D on the first rows hint.
-- http://www.mgogala.comReceived on Wed Jul 05 2006 - 21:20:03 CDT