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.
>>
>> --
>> 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
I'd be really interested in seeing what the block traffic on the memory
interconnect was.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jul 06 2006 - 14:13:14 CDT
