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: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 04 Jul 2006 09:28:53 -0700
Message-ID: <1152030537.302258@bubbleator.drizzle.com>


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

What version of Oracle?
Rerun the EXPLAIN PLAN using DBMS_XPLAN if 9i or greater without the hints and with the hints.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jul 04 2006 - 11:28:53 CDT

Original text of this message

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