Re: SQL tuning nightmare - db file sequential reads

<joshic75_at_gmail.com> wrote in message
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.
>

As others have pointed out, the row from v$session_wait shows a current wait only if the STATE = 'WAITING', otherwise is shows the last wait that occurred.

In your case you have (probably) had a very short wait for a read 897 seconds ago and have been using CPU ever since.

Your query is running a "not exists" filter. This means the subquery is being executed for each row acquired from the S_ASSET table with the appropriate date. Assuming the ROWS figure for that plan line reflects your stats, then you are probably running the query about 666,000 times as the optimizer assumes that 5% of the rows checked by a subquery will be returned - (and 33,300 * 20 = 666,000). The query could, quite easily, do a little physical I/O, and then hammer the CPU to death for ages.

You could try to make Oracle do an anti-join on this. Possibly a simple /*+ unnest */ in the subquery would be sufficient (and get rid of the other hints to start with) perhaps with a use_hash(S_ETL_R_IMG_6 ) - also in the subquery - to make it a hash anti-join.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html