Re: SQL tuning nightmare - db file sequential reads

Hi Charu,

It would be naive to make a statement like this,

"The hardware is quite powerful with 24 CPUs, 16GB RAM , SAN storage etc. So I doubt that it will be a problem at the OS level."

you have to be absolutely sure that there is no problem at OS level before you rule it out completely. just because you have a 24 CPU box with 16GB RAM, it doesn't mean you have to have queries running at the speed of light.

did you get good or resonable performance earlier or before this problem started? what was the response time for this query earlier? do you have any stats that you can share with us?

what about data? was there an increase in data volume? did you load data? did you collect statistics?

are there multiple processes changing the same data at the same time?

your explain plan show about 33K rows for the FFS...

what is the SAN config? how many disks? RAID level, etc..

are you using UFS / VxFS or RAW volumes. are you using Async IO? i remember Solaris 8 had quite a bit of issues with Async IO.

kernel configuration plays a very important role too.

you have also mentioned multiple databases having the same issue? could they be all connected to the same SAN or residing on the same disks?

its good to discuss first_rows_n and OICA but you primarily need to ensure that the infrastructure to support it is in place.

cheers
anand

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.
Received on Wed Jul 05 2006 - 23:38:42 CDT