Re: Insert performance and autoextend

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 27 Jun 2008 09:27:36 -0700 (PDT)
Message-ID: <8efdbcdd-791a-4155-b83f-b216fdf7eeb9@z72g2000hsb.googlegroups.com>


On Jun 27, 12:03 pm, pratap.deshm..._at_gmail.com wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

I do not know what query Toad uses to identify space usage but numerous posters in the past have not always looked at the best view for determing their true space utilization.

Extending the tablespace size would help if the issue was an excessive number of file extends during the load processing but otherwise would be unlikely to help unless the new files were on additional physical disks and the eventually spreading out of the data onto those disk helps IO performance.

The full Oracle version was not posted and there are some known issues related to ASSM depending on your version. Potentially you could scan metalink for them and look at your statistics to see if you match any of the known issues.

Running a short, maybe 5 minute, statspack snapshot during the insert task should help you determine if any unusual problems appear to exist on your system.

HTH -- Mark D Powell -- Received on Fri Jun 27 2008 - 11:27:36 CDT

Original text of this message