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 - 03:18:49 CST
