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.

What is the SORT_AREA_SIZE set to, the default of 64KB? Have you looked at the wait events associated with the session that is executing these hard coded SQL statements? Setting the system up for a large buffer cache should not be the only tuning method used to troubleshoot performance problems.

Here is an example of what you can do when a performance problem occurs when processing those slow SQL statements. First, find the SID of the session by checking V$SESSION. Before executing the SQL statements, execute this SQL statement, filling in the SID of the session: SELECT
  EVENT,
  TIME_WAITED,
  ROWNUM
FROM
  (SELECT
    EVENT,
    TIME_WAITED
  FROM
    V$SESSION_EVENT
  WHERE
    EVENT NOT LIKE 'SQL*Net %'
    AND EVENT NOT IN ('pmon timer','rdbms ipc message','smon timer','Null event','Streams AQ: qmn slave idle wait','wait for unread message on broadcast channel','jobq slave wait','Streams AQ: waiting for messages in the queue','Streams AQ: qmn coordinator idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator waiting for slave to start','rdbms ipc reply')

    AND SID= sid_of_session_executing_sql   ORDER BY
    TIME_WAITED DESC)
WHERE
  ROWNUM<=10;

The above will retrieve the top 10 wait events for the session - this is the starting point. In the session, execute one of the slow performing SQL statements. Then execute the above SQL statement and compare it with the previous run. The delta values of the wait events should give you a rough idea of what is causing the delay. Note that the above eliminates the wait events that begin with SQL*Net - such events indicate that Oracle is waiting for the client session to make another request. You may be able to use these events to see if the client is the cause of the slow execution.

If the above does not help much, try creating a 10046 trace of the session, then have the session execute the slow SQL statements. By manually reviewing the 10046 trace file, you can determine the exact database objects cauing the slow performance, the exact point when the wait events occurred, the the SQL plan.

As Sybrand stated, the plan that you provided is of limited help - it looks like the database isn't being asked to do anything too stressful.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Sep 30 2006 - 18:09:17 CDT