Re: slow insert performance

If your problem is the 'free block' anomoly, you could force a direct path load as a quick test. (Keeping in mind that you would have to do a backup.)

insert /*+ append parallel(xxx) */ into xxx select /*+ parallel(xxx2) */ * from xxx2;

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c4gmpp$1ef$1_at_titan.btinternet.com>...
> Note in-line
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland http://www.index.is/oracleday.php
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Daud" <daud11_at_hotmail.com> wrote in message
> news:f0bf3cc3.0404010035.64ed421_at_posting.google.com...
> > I ran statspack and the most wait is on:
> > db file sequential read.
> > It is by far the biggest compared to the others.
> >
>
> It is possible that most of your reads are
> used to locate the correct index leaf blocks
> for each row - so a review of the indexes on
> the table may be in order, with the aim of
> eliminating redundant indexes.
>
> Another possibility is that your big table has
> had a lot of randomly scattered deletes that
> (due to particular value of pctused/pctfree,
> or the use of ASSM) have left lots of table
> blocks with enough room for just one row
> to be inserted. So each row of your insert
> forces Oracle to read a single table block,
> and insert a row. (This type of freespace
> distribution is not common for tables - but
> it is possible)
>
> > I think the problem is because the db buffer cache is too tiny. It is
> > only 120Mb now. Agree?
> >
>
> If the root cause is excessive indexing, a larger
> buffer may help.
>
> If the root cause is the 'free block' anomaly I
> describe in the second para, then I wouldn't
> expect a larger buffer to make any difference.
>
> > Daud
> >
> > "DJ" <nospamplease_at_goaway.com> wrote in message
> news:<_RD9c.477$I8.211_at_newsfe1-gui.server.ntli.net>...
> > > "Daud" <daud11_at_hotmail.com> wrote in message
> > > news:f0bf3cc3.0403280905.6b6af04d_at_posting.google.com...
> > > > I have a big table (over 50 million rows). Insert into the table is
> > > > very slow. Doing
> > > > insert into xxx
> > > > select * from xxx2
Received on Thu Apr 01 2004 - 10:53:33 CST