Re: SQL tuning nightmare - db file sequential reads

> 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.