Is there a way to speed up Index Fast Full Scan

From: <dbaplusplus_at_hotmail.com>
Date: 30 Sep 2006 12:14:15 -0700
Message-ID: <1159643655.826721.148750@i42g2000cwa.googlegroups.com>

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. Received on Sat Sep 30 2006 - 14:14:15 CDT