Advice on a heavy DML "status" or "queue" like tables and RAC - global cache busy
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Jul 2005 18:04:18 -0400
We have a table and app design that is using a small table (width and length)
as a "STATUS" or "FLAG" table.
Normally all things work fine, but when we have mass DML surges (INSERT) this
queuing mechanism...the database starts to have problems.
Further, is the event that we run RAC "ACTIVE-ACTIVE" (normally we do not), the
problem only gets worse...hung database and sessions...the thing just chokes.
I suspect that RAC "ACTIVE-ACTIVE" is just bouncing these blocks back and forth
like a professional "ping-pong" match. The load on our (Linux) server can go
from 5 to 150 in seconds and tons of "global cache busy" waits.
Personally, I don't believe the true "ACTIVE-ACTIVE" RAC under heavy DML (on
non-logical app partitioning) is possible, but I'm looking for advice to
improve our situation, both form the RAC perspective and *simple* application
change we could make to improve the process.
We have rebuilt one table and up'ed the INITRANS parameter...hoping that will
help?
In our case these are massive inserts (thousands, not millions at a time).
Then later a batch process deletes these rows one at a time...possibly when
more inserts are happening.
My developers are (correctly) questioning why (our) Oracle can not handle mass
inserts...it should!?
PS Everyday my belief in RAC shrinks. After 7 years of OPS-RAC at three
different Oracle shops, I have never seen "ACTIVE-ACTIVE" RAC work, nor used
regularly for *very* busy mission critical db's/apps.
Chris Marquez
Oracle DBA
Other related posts:
- » Advice on a heavy DML "status" or "queue" like tables and RAC - global cache busy
