Re: Question on gc buffer busy waits

  • From: David Fitzjarrell <oratune@xxxxxxxxx>
  • To: "Mark.Bobak@xxxxxxxxxxxx" <Mark.Bobak@xxxxxxxxxxxx>, "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, "krishna.setwin@xxxxxxxxx" <krishna.setwin@xxxxxxxxx>
  • Date: Mon, 17 Sep 2012 08:35:56 -0700 (PDT)

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 
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:
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@xxxxxxxxxxxx>
To: "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>; 
"krishna.setwin@xxxxxxxxx" <krishna.setwin@xxxxxxxxx> 
Cc: Oracle List <oracle-l@xxxxxxxxxxxxx> 
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,

From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Andrew Kerber [andrew.kerber@xxxxxxxxx]
Sent: Sunday, September 16, 2012 23:09
To: krishna.setwin@xxxxxxxxx
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@xxxxxxxxx> wrote:

> Hi,
> 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..
> --

Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'



Other related posts: