Re: Queue tables and buffer busy waits
Date: Fri, 15 Oct 2010 09:08:46 -0700 (PDT)
Message-ID: <df1c2d0b-d2e3-4b62-8ea1-f47700e24695_at_x35g2000prh.googlegroups.com>
On Oct 15, 12:32 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Mladen Gogala" <mgog..._at_no.address.invalid> wrote in message
>
> news:i98hg8$bvi$1_at_solani.org...
>
>
>
> > 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
>
> 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.)
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
-- _at_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/Received on Fri Oct 15 2010 - 11:08:46 CDT