Re: Insert performance and autoextend

From: Mark D Powell <>
Date: Thu, 26 Jun 2008 07:24:33 -0700 (PDT)
Message-ID: <>

On Jun 25, 6:16 pm, joel garry <> wrote:
> 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."
> jg
> --
> is bogus.

Pratap, Joel provided or asked for the right information to help you with your issue.

I would like to know, how you measured the fullness of the data files?

  • Mark D Powell --
Received on Thu Jun 26 2008 - 09:24:33 CDT

Original text of this message