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

dbaplusp..._at_hotmail.com wrote:
> 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

>

I am uisng db_block_buffers, just db_cache_size. Received on Sat Sep 30 2006 - 15:47:45 CDT