Re: Insert performance and autoextend

On Jun 26, 3:24 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

The database is 10.2.0.3.0 and we are using LMT with uniform size and auto segment management. We are on Solaris. We have quite a few indexes on the table and that could have been the issue. We added a few files to the tablespace but it did not help the performance. I just happened to look at TOAD to see how much space is left. Received on Fri Jun 27 2008 - 11:03:12 CDT