Queue tables and buffer busy waits
Mladen Gogala
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.
Jonathan Lewis
"Mladen Gogala" <mgo...@no.address.invalid> wrote in message
news:i98hg8$bvi$1...@solani.org...
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
joel garry
wrote:
> "Mladen Gogala" <mgog...@no.address.invalid> wrote in message
Looks like the best practices for queue ownership:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10705/best_gen.htm#STREP516
>
> 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 Lewishttp://jonathanlewis.wordpress.com
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/blogs/utb/2010/05/mexico-burns-46-tons-of-marijuana/
John Hurley
# 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/You_Probably_Dont_Need_RAC.pdf
citation?
( It should be on Moans Nogood site somewhere ... )
Mladen Gogala
> $ 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])
Thanks Joel. This confirms what I already knew. DBMS_AQ doesn't perform
well across RAC.