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

From: <dbaplusplus_at_hotmail.com>
Date: 30 Sep 2006 13:34:00 -0700
Message-ID: <1159648440.433102.257270@k70g2000cwa.googlegroups.com>

sybrandb wrote:
> 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

Sorry my buffer cache is 1GB and not 8GB. I have read about keep cacahe and recycle cacahe and do not find them useful in my environment. Received on Sat Sep 30 2006 - 15:34:00 CDT