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

Home -> Community -> Usenet -> c.d.o.server -> Re: 65% io wait on batch insert

Re: 65% io wait on batch insert

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 28 Oct 1998 13:56:43 +0200
Message-ID: <7170tu$cm2$1@hermes.is.co.za>


Nir Dagan wrote in message <3636C972.F668509F_at_exlibris.co.il>...
>I'm using a batch job to insert a lot of rows into a table with 1 index
>in Oracle 7.3.3 server on SunSolaris2.5.1. It seems like all my CPU
>resources were directed to iowait (65%). And the job itself gets only 9%
>CPU.
Not a lot of info to go on. Waiting for i/o can be a symptom of various "problems". Also waiting for i/o is a bit of a misnomer as it only indicates that there are outstanding i/o requests in the kernel. There can also be an i/o problem if the CPU stats shows 1% waiting for i/o and 99% busy. So don't put too much faith into the waiting for i/o stats - IMHO it only indicates that Oracle is doing large volumes of disk access.

The fastest way to see what's happening with the insert batch session is to look at the V$SESSION_EVENT and V$SESSION_WAIT tables.

If you have a fairly large table and it's indexed, then a batch insert can be extremely slow. For example if the index is unique then Oracle needs to do multiple index range scans in the index to check for a possible duplicate values before inserting the data. Let's say the index is 20MB in size and you're inserting 20,000 rows. This means that Oracle has to process the 20MB index for every single insert - and this works out to processing at least a GB or two of index data in total. The end-result is that Oracle spends 99% of the time reading index data and 1% time inserting data for example.

Look at the results of the V$ system tables above - I'm sure you will find the answer there. And possibly the only way to increase the performance of your batch insert will be to disable indexes, constraints and triggers on the table you're inserting.

Unfortunately the requirements between on-line processing and batch processing are often in conflict. In this case you may want to consider using a staging table for loading the batch data and manipulate it there for a "clean insert" with database referential integrity disabled in the destination table.

regards,
Billy Received on Wed Oct 28 1998 - 05:56:43 CST

Original text of this message

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