Re: SQL tuning nightmare - db file sequential reads

Mladen Gogala wrote:
> On Wed, 05 Jul 2006 07:26:08 -0700, Jining Han wrote:
> Unfortunately, in the environments with mixed application types, which is
> the vast majority of the environments, this type of optimization doesn't
> work nearly as well, so you don't want RBO-type of optimization. You want
> the intelligence of the CBO which is much better at discerning the proper
> approach.

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.
I'v used first_rows_1 succesfully for an oltp app, and as it had also reports, for each one of them all_rows was added. Most of the time all plans were created ok. Yea I had to gave some explicit full hints somewhere because of bad data distribution and lack of desire to create histograms.

Gints Plivna
http://www.gplivna.eu/ Received on Wed Jul 05 2006 - 16:25:29 CDT