The new application, recently installed to run against a RAC database (3
nodes, 64 bit linux, Oracle 10.2.0.5) is making heavy use of advanced
queueing. My problem is that the queue tables are incessant source of
contention, suffering from all kinds of buffer busy waits, both local and
global. If I check V$SEGMENT_STATISTIC with the following query,

with stats as (
select owner,object_name,statistic_name,value
from v$segment_statistics
where statistic_name = 'gc buffer busy'
order by value desc
)
select * from stats where rownum<=10
/


The result looks like this:
OWNER OBJECT_NAME STATISTIC_NAME
VALUE
--------------- ------------------------------ --------------------
----------
SYS I_JOB_JOB gc buffer busy
30184683
SYS JOB$ gc buffer busy
10128719
ADBASE PK_PENDING_ALERTS gc buffer busy
7899852
SYS I_JOB_NEXT gc buffer busy
5302448
ADBASE PENDING_ALERTS gc buffer busy
5288135
LOCATIONSERVICE AQ$_MMSRES_MMSAGENT_TABLE_I gc buffer busy
1082715
LOCATIONSERVICE MMSRES_MMSAGENT_TABLE gc buffer busy
1055558
LOCATIONSERVICE SPEECH2TEXT_Q_TABLE gc buffer busy
622833
LOCATIONSERVICE TASKS gc buffer busy
358430
LOCATIONSERVICE DQV2MIN_STARTDATE_IDX gc buffer busy
256124

Now, everything that is not owned by SYS and is not index is a queue
table. The problem is systemic in nature, queue tables are by their very
nature the point of contention. What can be done to alleviate the
contention, short of restricting the queue to a single node only?
Every queue has retention time set to 0. Developers argue that setting
retry_delay to something >0 would be extremely detrimental to performance.





--
http://mgogala.byethost5.com

"Mladen Gogala" <mgogala (AT) no (DOT) address.invalid> wrote


Nothing.

I think there is even a comment in the manuals that if you want to
mix AQ with RAC then the enqueue and dequeue processes for
a single queue table should be constrained to run on the same node.

(I can't remember if this is in the RAC manuals or the AQ manuals -
but it was probably the AQ manuals - because I recall reading at the
same time that AQ and Shared Servers (MTS) don't mix well if you
use the AW polling mechanism.)

On second thoughts - you might look at the possibility of hash partitioning
everything simply to increase the number of possible points of contention -
in case this manages to reduce the contention on any particular point; but
I don't
know if you can do that legally and it might do terrible things with
execution
plans, and it might simply mean you have more points subject to the same
amount of contention.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On Oct 15, 12:32*am, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
wrote:
Looks like the best practices for queue ownership:
http://download.oracle.com/docs/cd/E...n.htm#STREP516

My new favorite error action documentation:

$ oerr ora 10854
10854, 00000, "Sets poll count used for AQ listen code under RAC"
// *Cause: NA
// *Action: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT
NEED TO BE
// TRANSLATED OR DOCUMENTED. IT IS USED ONLY FOR INTERNAL
TESTING.

(Speculation: Fix for Bug 3830972 doesn't perform well across nodes,
but better than a fail. See dbms_aq.listen Works Inconsistently in a
RAC Environment [ID 762715.1])

jg
--
@home.com is bogus.
You load 16 tons, what do you get. 30 tons left over for Cheech and
Chong.
http://www.victoryinstitute.net/blog...-of-marijuana/

Mladen:

# The new application, recently installed to run against a RAC
database (3 nodes, 64 bit linux, Oracle 10.2.0.5) is making heavy use
of advanced queueing.

Sounds more like you are using advanced levels of heavy queueing ...
due to RAC and a lack of testing the application in the production
environment perhaps.

Can you move it to a non RAC environment for a while until you get it
tested and appropriately configured/changed in a RAC environment?

Maybe a good argument for the old http://www.my-idconcept.de/downloads...t_Need_RAC.pdf
citation?

( It should be on Moans Nogood site somewhere ... )

On Fri, 15 Oct 2010 09:08:46 -0700, joel garry wrote:


Thanks Joel. This confirms what I already knew. DBMS_AQ doesn't perform
well across RAC.



--
http://mgogala.byethost5.com