RE: db file sequential read wait event and Insert performance

Indexes are used by other part of application so we can't drop them from this testing. Is there any options that I can try to reduce these waits.

(Anyhow if we drop these indexes insert of 12m ROWS only takes 5 min with 250M rows already in table)  

From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com] Sent: Thursday, February 28, 2008 1:34 PM To: Harvinder Singh
Cc: oracle-l_at_freelists.org
Subject: Re: db file sequential read wait event and Insert performance  

Harvinder,  

it sounds like it's the updating of the indexes that's taking all your time now. oracle has to read some blocks in order to determine where in the index tree to put the new values and that's your sequential read.  

If the app is not in use yet, drop the indexes and recreate them using PQ after the load is done. That's the easiest way.  

Finn  

On 2/28/08, Harvinder Singh <Harvinder.Singh_at_metratech.com> wrote:

Hi,  

We are doing a benchmark testing of the application and currently focus on area where we insert about 12M rows in 1 table in 1 batch and commit every 50k rows, Inserts are done by application using OCI calls. When we started and tables were empty the batch was taking about 6:30 min and now with tables having 200M rows the batch is taking about 30 min (5 times more).

Following is the configuration

Oracle 10.2.0.3 <http://10.2.0.3/> , 2 node RAC cluster on Linux AS. Each node has 8 CPU's and 32GB RAM and db_buffers are allocated 12GB and Shared_pool to 3 GB on both the machine.

We are using ASM (stripe size 1mb default), ASSM, and local extent management.  

Table has 22 columns and 4 indexes, 2 column primary key, 2 (3 column indexes) and 1 (1 column index). Table is range partitioned on date column and currently has 30 partitions and all the inserts are going to all the 30 partitions, we already done all the filtering at application layer so that there is very less internodes communication and both the RAC nodes are inserting into 15 separate partitions.

I checked the wait events and db file sequential read is now consuming about 70% of total time and most of the time is spent on both the 3 column indexes of the table. What options can we try or what should be the sequence of steps to follow to reduce these waits and increase the insert performance.  

Thanks

--Harvinder
       

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 28 2008 - 13:30:18 CST