Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL tuning nightmare - db file sequential reads

Re: SQL tuning nightmare - db file sequential reads

From: DA Morgan <>
Date: Thu, 06 Jul 2006 12:13:14 -0700
Message-ID: <>

Jining Han wrote:
> 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.
>> --

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

I'd be really interested in seeing what the block traffic on the memory interconnect was.

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Thu Jul 06 2006 - 14:13:14 CDT

Original text of this message