RE: Question on gc buffer busy waits

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Mon, 17 Sep 2012 01:57:56 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F016C5838AE_at_AAPQMAILBX02V.proque.st>

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

Received on Mon Sep 17 2012 - 00:57:56 CDT