SQL tuning nightmare - db file sequential reads

Hi all,

I am running the following SQL statement:

INSERT /*+APPEND*/ INTO S_ETL_I_IMG_6 (ROW_ID, MODIFICATION_NUM, OPERATION, LAST_UPD)
SELECT /*+ index_ffs(s_asset, s_asset_bt_w1_x) */ ROW_ID ,MODIFICATION_NUM ,'I' ,LAST_UPD
FROM S_ASSET WHERE S_ASSET.LAST_UPD > TO_DATE('2006-06-02 02:26:36', 'YYYY-MM-DD HH:MI:SS')
AND NOT EXISTS ( SELECT ROW_ID ,MODIFICATION_NUM ,'I' ,LAST_UPD

FROM  S_ETL_R_IMG_6
WHERE S_ETL_R_IMG_6.ROW_ID = S_ASSET.ROW_ID
AND S_ETL_R_IMG_6.MODIFICATION_NUM = S_ASSET.MODIFICATION_NUM
AND S_ETL_R_IMG_6.LAST_UPD = S_ASSET.LAST_UPD  )

The execution plan for this statement is:

| Id  | Operation                   |  Name             | Rows  |
-----------------------------------------------------------------


|   0 | INSERT STATEMENT            |                   |       |

|   1 |  LOAD AS SELECT             |                   |       |

|   2 |   FILTER                    |                   |       |

|   3 |    INDEX FAST FULL SCAN     | S_ASSET_BT_W1_X   | 33324 |

|   4 |  TABLE ACCESS BY INDEX ROWID| S_ETL_R_IMG_6     |     1 |

|   5 |   INDEX RANGE SCAN          | S_ETL_R_IMG_6_M1  |    26 |

-----------------------------------------------------------------

This query is running inexplicably slow. I think the execution plan is reasonably optimal. However when I query the V$SESSION_WAIT view repeatedly, I am getting the wait event as:

  SID EVENT                                  P1         P2         P3

----- ------------------------------ ---------- ---------- ----------
---------- ---------------
   71 db file sequential read               263      50960          1
       1             897

The p1, p2 and p3 remain the same. Only the SECONDS_IN_WAIT count keeps on increasing in steps of 3 (reached 990 right now).

I am at a loss as what to make of this wait event. Does this mean that Oracle is taking time to read that one block into memory? Does this indicate hot-spots in the disk (I am not very clear of this concept!!)

Could anybody please help in explaining this behaviour and suggest a possible remedy.

Many thanks,
Charu. Received on Tue Jul 04 2006 - 10:02:03 CDT