Re: Transaction Gradually Slows to a crawl! (please help)

badshah2000_at_hotmail.com (Mike Harris) wrote in message news:<a3e27c56.0305271521.1c481750_at_posting.google.com>...
> Hi, I am running Oracle 8.1.7 on W2K Professional (SP/3). Athlon
> 1400MHz, 768MB RAM.
>
> note: redo logs are DISABLED for faster updates (it's a staging
> machine)
> I start a transaction
> I do an insert in a table (it's not an overly wide table, about 30
> fields wide) which results in about 12000 new rows.
> I then start a process (within the same transaction) which updates
> each new row doing some more DB processing.
> As the number of rows being updated hits about 7000 mark, database
> starts to slow down. I am sure that it's gradually slowing down, but
> that's when I start noticing it. When it has updated about 8000, it's
> realy slow. And when it's close to 8500 mark, it's realy slooooww.
> Then it goes so slow that I haven't seen it finish all 12000 rows (my
> patience neven seems to side with me).
>
> What gives? In every single case, I ended up killing the session. And
> that started oracle in a looooong clean up cycle. It is pure hell. Can
> you please recommend a faster way to deal with this transaction hell?
> Yes, all of it has to be inside a transaction. Any parameters I can
> play with? More RAM? ???

My guess is at the 7000 mark, you have used up all free space that was created in the blocks when you inserted, so each row that is updated has to go through extreme calisthenics to find somewhere to put the data. Search on metalink, ixora.com.au or jlcomp.uk to find scripts to elucidate row chaining. You might set PCTFREE much higher for the table before you start.

Also check your alert log for any errors such as "Checkpoint not complete" that might indicate you need more redo buffers.

It's possible you might be hitting a limit of the dirty buffers in the SGA, which then have to be written out, if that is the case, more memory and doubling the SGA might very well help. How big are your buffers, anyways?

Do you have indices on the table? Try disabling them.

>
> thanks a lot!
> Mark.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/wed/business/news_1b28peregrin.html