Re: SQL tuning nightmare - db file sequential reads
- From: "Anand Rao" <panandrao@xxxxxxxxx>
- Date: 5 Jul 2006 21:38:42 -0700
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@xxxxxxxxx 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.
.
- References:
- SQL tuning nightmare - db file sequential reads
- From: joshic75
- SQL tuning nightmare - db file sequential reads
- Prev by Date: Re: Announcing Yellowfin Version 2.5 upgrade release for Oracle based users.
- Next by Date: Re: Perm Job Opportunity: Oracle BI Dataware House Developer Position - Chicago, IL
- Previous by thread: Re: SQL tuning nightmare - db file sequential reads
- Next by thread: Re: SQL tuning nightmare - db file sequential reads
- Index(es):
Relevant Pages
|
