Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Question - High I/O per Insert

RE: Performance Question - High I/O per Insert

From: Mark Strickland <mstrickland_at_drugstore.com>
Date: Mon, 11 Oct 2004 10:10:48 -0700
Message-ID: <63A70CA32CEF354892F78EADB13D3297030D54DA@seaems005c>


Thanks. The data are sorted by date_stamp and there is one index that is prefixed on that column. The other four are not and I expect there is a lot of inserting out of sync with the clustering factor. There is no primary key and no unique indexes.  

Mark  


From: ryan_gaffuri_at_comcast.net [mailto:ryan_gaffuri_at_comcast.net] Sent: Monday, October 11, 2004 10:03 AM
To: Mark Strickland; Graeme.Farmer_at_mincom.com Cc: Mark Strickland; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: RE: Performance Question - High I/O per Insert  

your stating that your load is taking longer as you add more data? you are also getting 'sequential read waits'. Are any of these indexes unique indexes or primary key indexes? those require a lookup for every insert.  

also, i think its possible to have higher IO if you are inserting out of sync with the clustering factor. It means there will be more index maintenance involved.  

don't think it has anything to do with partitioning. I haven't noticed anything related to this in the past.    

> No, no triggers. I believe the high I/O is due to index maintenance.
> When I remove the indexes or run the load with direct path, the load
> screams. Thx!
>
>
> Mark
>
>
>
>
>
> ________________________________
>
> From: Graeme.Farmer_at_mincom.com [mailto:Graeme.Farmer_at_mincom.com]
> Sent: Sunday, October 10, 2004 5:52 AM
> To: Mark Strickland
> Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
> Subject: RE: Performance Question - High I/O per Insert
>
>
>
>
> Any triggers on the table that refer to data found in the current
week's
> partition? This is a classic cause of high I/O per insert.
>
> Graeme
>
> oracle-l-bounce_at_freelists.org wrote on 29/09/2004 05:27:28 AM:
>
> > This is a follow-up to a question I posed earlier, but I have more
> info.
> > A SQL*Loader conventional path load inserts rows into a table
> > partitioned by week. On the first day of a new week, the load runs
as
> > efficiently as expected, but as the week goes on, the load goes more

> and
> > more slowly. There is a single local index on each partition and the

> > blevel is 2. I'm scratching my head. I realize I'm not providing
> much
> > info, but can anyone tell me why loading into a partition might get
> > slower as the partition fills? Thx!
> >
> >
> > Mark Strickland
> >
> >
> >

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 11 2004 - 12:07:14 CDT

Original text of this message

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