Re: Is there a way to speed up Index Fast Full Scan

dbaplusplus_at_hotmail.com wrote:
> I am on Oracle 9.2.0.5 on HP UNIX 11i. I have several queries, which
> takes 1-2 minutes
> Despite indexes being used. When I look at explain plans (gotten from
> v$sql_plan), here is INDEX FAST FULL SCAN on a large no of rows (137K
> below).
> 0 | SELECT STATEMENT | | |
> | |
> 316 |* 1 | FILTER | |
> | | |
> 317 | 2 | NESTED LOOPS | |
> 94 | 24346 | 14463 (0)|
> 318 | 3 | NESTED LOOPS | |
> 3218 | 722K| 14141 (0)|
> 319 | 4 | NESTED LOOPS | |
> 1495 | 296K| 13842 (0)|
> 320 | 5 | INDEX FAST FULL SCAN | D_1F00C73D80000D01 |
> 137K| 2286K| 68 (0)|
> 321 |* 6 | TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S |
> 1 | 186 | 2 (50)|
> 322 |* 7 | INDEX UNIQUE SCAN | D_1F00C73D80000108 |
> 1 | | |
> Is there any way to reduce response time of such queries? I have
> already tuned my database many times (e.g., it has !GB of
> db_buffer_cacahe * 8K).
>
> I do not have access to source code. so I cannot rewrite queries,
> however I can alter stored outlines. If there any hidden parameters in
> init.ora, willing to try as well.
>
> Any ideas will be appreciated.

1 137k is NOT much
2 Always post the sql statement
3 use dbms_xplan to get the explain plan, you can even use it on life plans
4 Index fast full scan uses the same mechanism as full table scan and is tunable using the same parameter. I leave looking up this parameter as an exercise for you, because you seem to avoid reading manuals at all cost.
5 Likely your problem is not the speed of index_ffs. A buffer cache of 8Gb, even of 1 Gb is clearly ridiculous, and as the cache is protected through cache buffer chains, by cranking up db_buffer_cache beyond the sky, you are *causing* problems with the cache buffer chain latch, consequently making your database *less* scalable.
I would, in your shoes, rather start using the keep cache and the recycle cache. You would gain much more by using that. But above all, you must stop symptom fighting, and start reading those manuals.
Setting hidden parameters is not going to help at all, and is also unsupported.

Why are you using db_block_buffers by the way? Don't you know when you use db_cache_size, you can alter all pools dynamically

-- 
Sybrand Bakker
Senior Oracle DBA