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 -> Re: SQL tuning nightmare - db file sequential reads

Re: SQL tuning nightmare - db file sequential reads

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 4 Jul 2006 20:06:27 +0200
Message-ID: <44aaae24$0$29807$626a54ce@news.free.fr>

<joshic75_at_gmail.com> a écrit dans le message de news: 1152025323.596622.207890_at_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.
|

From doc. about v$session_wait.seconds_in_wait:

<quote>
If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait ...
WAIT_TIME = duration of last wait
</quote>

Your last wait time lasted 1cs and it started 897s before.

Regards
Michel Cadot Received on Tue Jul 04 2006 - 13:06:27 CDT

Original text of this message

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