Re: SQL tuning nightmare - db file sequential reads

Mladen Gogala wrote:

> What you have shown is consistent with the reasonable expectation. You are
> doing single block reads aka "db file sequential read" and your plan shows
> that you're using the index. Unfortunately, you're accessing only indexes,
> as the consequence of the /*+ index_ffs(s_asset, s_asset_bt_w1_x) */ . Get
> rid of that hint and you will probably perform fewer db file scattered
> read events, also known as "multiblock reads". In the end, multiblock
> reads should be faster. Your INDEX_FFS hint was probably a trick to make
> "NOT EXISTS" predicate work faster. The "NOT EXISTS" can sometimes be
> re-formulated as minus, which I find easier to understand and optimize.
>
> --
> http://www.mgogala.com

I opened an iTar with Oracle not ago with a similar case, in which I reported that my query ran for over 4 hours using all the indexes that I expected. No table scan at all, and I had no hint in my query.

I am running 10.1.0.4 on HP-Itanium, a 3-node RAC data warehouse.

After days of frustration, I set the OPTIMIZER_INDEX_COST_ADJ parameter and db_file_multiblock_read_count to values that would cause tablescans. The query finished in 6 minutes.

What is frustrating is that the indexes were created to help queries/reports to run fast, and in most cases these indexes did just that. But then all of a sudden for certain, albeit a small number of queries, they are the bottleneck. When the users all use BusinessObjects, it's tough to require them to use HINTS and change session parameters.

JH Received on Wed Jul 05 2006 - 09:26:08 CDT