Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL tuning nightmare - db file sequential reads

SQL tuning nightmare - db file sequential reads

From: <joshic75_at_gmail.com>
Date: 4 Jul 2006 08:02:03 -0700
Message-ID: <1152025323.596622.207890@m79g2000cwm.googlegroups.com>


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
WAIT_TIME SECONDS_IN_WAIT
----- ------------------------------ ---------- ---------- ----------
---------- ---------------
   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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US