SQL tuning nightmare - db file sequential reads
- From: joshic75@xxxxxxxxx
- Date: 4 Jul 2006 08:02:03 -0700
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.
.
- Follow-Ups:
- Re: SQL tuning nightmare - db file sequential reads
- From: Jonathan Lewis
- Re: SQL tuning nightmare - db file sequential reads
- From: Anand Rao
- Re: SQL tuning nightmare - db file sequential reads
- From: Michel Cadot
- Re: SQL tuning nightmare - db file sequential reads
- From: Mladen Gogala
- Re: SQL tuning nightmare - db file sequential reads
- From: DA Morgan
- Re: SQL tuning nightmare - db file sequential reads
- From: Steve Howard
- Re: SQL tuning nightmare - db file sequential reads
- Prev by Date: Re: how to get data from a perl script with pl/sql
- Next by Date: rwclient works, rwrun doesn't
- Previous by thread: how to get data from a perl script with pl/sql
- Next by thread: Re: SQL tuning nightmare - db file sequential reads
- Index(es):
Relevant Pages
|
