db file sequential read wait event and Insert performance
- From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 28 Feb 2008 11:38:43 -0500
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, 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
Other related posts:
