Re: Insert performance and autoextend

From: joel garry <>
Date: Wed, 25 Jun 2008 15:16:05 -0700 (PDT)
Message-ID: <>

On Jun 25, 2:59 pm, wrote:
> Hi,
> We have a process that inserts a few million records in a table (It is
> not a direct path load). In the entire process the max time the
> process waits is for the event "db file sequential read".
> The data files in the database are totally full (Space remaining shows
> as 0 for every file for the data tablespace) but are autoextensible
> with increment_by = 1. Is this small increment_by the reason of the
> wait event and hence poor performance?
> Regards,
> Pratap

db file sequential read generally refers to random access of a file. Yes, having a small increment will make things worse, but if you have indices on the table, you are probably seeing the waits for that, and if you are not using locally managed extents, then you may also be seeing the effects of updating the dictionary tables every time the autoextend happens. Of course, the extend pounds upon the I/O and will definitely make the index reads and writes wait, and reading the file headers will be shown in this wait.

So, which exact version are you on (to 4 decimals), are you using LMT, which tuning tools do you have available, how long does the process take, how big of a window do you have to do it, what does your primary key look like, how many indices do you have, can you disable and rebuild them later, which OS? Please look at the explain plan and a statspack, too.

From the performance tuning guide: "This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes."


-- is bogus.
Received on Wed Jun 25 2008 - 17:16:05 CDT

Original text of this message