Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Advice on a heavy DML "status" or "queue" like tables and RAC - global cache busy

Advice on a heavy DML "status" or "queue" like tables and RAC - global cache busy

From: Marquez, Chris <cmarquez_at_collegeboard.org>
Date: Tue, 26 Jul 2005 18:04:18 -0400
Message-ID: <B30C2483766F9342B6AEF108833CC84E05BD5EB9@ecogenemld50.Org.Collegeboard.local>

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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 26 2005 - 17:06:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US