why buffer busy waits/latch free/buffes chains on selects?

I was running a big insert yesterday on a fairly large table..

smthing like

alter session set sort_area_size=900M;
set transaction use rollback segment big_rbs; insert into my3milrowtable
(select a b c from another_large_table

minus
select a b c from another_large_table)

That particular DB runs in MTS mode, b/c it can have up to 2.5K connections.. after a while my clients stopped being able to connect to the db, I took a look and all MTS servers were busy...

took a look at v$session_waits and saw something like this:

most waits were for 'buffer busy waits' w/ p3=130 on queries like
select count(*) from my3milrowtable where id=:var;

(from Steve Adams' web site)

"
1013 Block is being read by another session and no other  or 130 suitable block image was found, so we wait until the read

           is completed. This may also occur after a buffer cache
           assumed deadlock. The kernel can't get a buffer in a
           certain amount of time and assumes a deadlock. Therefore it
           will read the CR version of the block.

After some time the waits would change to 'latch free', I couldn't translate p1 (address) into a v$latch.address properly, but by looking @ v$waitstat Icould see that it was the buffers chains latch..

BTW, I ended up writing PL/SQL to insert in 50K chunks and commit. .......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email