Re: Insert performance and autoextend

On Jun 25, 6:16 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jun 25, 2:59 pm, pratap.deshm..._at_gmail.com 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
> --
> @home.com is bogus.http://www.signonsandiego.com/uniontrib/20080624/news_1c24urrea.html

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 --