Re: Question on gc buffer busy waits
Date: Mon, 17 Sep 2012 08:35:56 -0700 (PDT)
Message-ID: <1347896156.22759.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
No one has mentioned remastering -- since all nodes are actively updating this same table it's likely that blocks updated by one node will also need to be updated by another node which leads to remastering (moving, or 're-owning' the blocks from one node to another). If this table is heavily updated it could be a contributing factor. Normally dynamic remastering is a good thing; in this case it may not be as it may be occurring too frequently causing throughput slowdowns. There are undocumented parameters that control remastering (node affinity):
PARAMETER DESCRIPTION DFLT ------------------------------------------------------------ ---------------------------------------------------------------------- --------- _affinity_on = TRUE enable/disable affinity at run time TRUE _enable_default_affinity = 0 enable default implementation of hard affinity osds TRUE _enable_midtier_affinity = TRUE enable midtier affinity metrics processing TRUE _gc_affinity_locking = TRUE if TRUE, enable object affinity TRUE _gc_affinity_locks = TRUE if TRUE, get affinity locks TRUE _gc_affinity_ratio = 50 dynamic object affinity ratio TRUE _gc_flush_during_affinity = TRUE if TRUE, flush during affinity TRUE _gc_tsn_undo_affinity = TRUE if TRUE, use TSN undo affinity TRUE _gc_undo_affinity = TRUE if TRUE, enable dynamic undo affinity TRUE _lm_drm_max_requests = 100 dynamic remastering maximum affinity requests processed together TRUE _lm_enable_aff_benefit_stats = FALSE enables affinity benefit computations if TRUE TRUE _lm_local_hp_enq = TRUE use static file affinity for HP enqueue mastership TRUE _lm_num_pt_buckets = 8192 number of buckets in the object affinity hash table TRUE _lm_num_pt_latches = 128 number of latches in the object affinity hash table TRUE _lm_single_inst_affinity_lock = FALSE enable single instance affinity lock optimization TRUE _lm_use_tx_tsn = TRUE use undo tsn affinity master as TX enqueue master TRUE _midtier_affinity_clusterwait_threshold = 100 cluster wait threshold to enter affinity TRUE _midtier_affinity_goodness_threshold = 2000 goodness gradient threshold to dissolve affinity TRUE _parallel_cluster_cache_pct = 80 max percentage of the global buffer cache to use for affinity TRUE
I haven't played with them; an excellent blog post on this topic is found here:
http://orainternals.wordpress.com/2010/03/25/rac-object-remastering-dynamic-remastering/
that explains what you might set to improve the situation.
You may also wish to speak with Oracle support (and hopefully get a good technician) about this issue. David Fitzjarrell
From: "Bobak, Mark" <Mark.Bobak_at_proquest.com> To: "andrew.kerber_at_gmail.com" <andrew.kerber_at_gmail.com>; "krishna.setwin_at_gmail.com" <krishna.setwin_at_gmail.com> Cc: Oracle List <oracle-l_at_freelists.org> Sent: Sunday, September 16, 2012 11:57 PM Subject: RE: Question on gc buffer busy waits
Totally agree w/ what Andrew said. You are going to need to better characterize the workload and the problem.
One other comment, regarding reverse key indexes. They're probably better than nothing, but, if you are licensing the partitioning option, you'll get far better results by hash partitioning your indexes, rather than rebuilding as reverse key. If you do decide to hash partition, a few points to consider would be: you can hash partition indexes, even if the underlying table is not partitioned, the number of hash partitions should always be a power of 2 (2,4,8,1,6,32, etc), and, a reasonable starting point for number of hash partitions would be the minimum power of 2 that's greater than or equal to the number of nodes in the cluster.
Hope that helps,
-Mark
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber [andrew.kerber_at_gmail.com] Sent: Sunday, September 16, 2012 23:09
To: krishna.setwin_at_gmail.com
Cc: Oracle List
Subject: Re: Question on gc buffer busy waits
Those may work, but it may also simply be a problem with application design. It could also be a problem with indices that are not sufficiently selective, or many other problems. We cant really tell from your description, at a high level the problem is caused by two different cluster nodes access the same data at the same time. One solution would be to partition your workload more effectively so that one set of data is only accessed from one node.
On Sun, Sep 16, 2012 at 8:24 PM, Krishna <krishna.setwin_at_gmail.com> wrote:
> Hi,
> 11.2.0.3.0
> RAC - 4 node
>
> we are facing "gc buffer busy acquire waits" in our database
> intermittently. This is an OLTP system.
> we are getting lot of blocking on our system. for example: 54 seconds in DB
> Time is spent waiting for TX lock
>
> we are updating same table from all services that are on all nodes..this is
> our primary table..
> the primary key on the table is auto-generated.
>
> 1. I updated all stats
> 2. re-indexed indexes..
>
> Did anybody face the same problem???
>
> will creating a reverse key index on primary solve our problem?? (table has
> abt 2 million records)
>
> Thanks in advance..
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 17 2012 - 10:35:56 CDT