RE: Question on gc buffer busy waits
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, "krishna.setwin@xxxxxxxxx" <krishna.setwin@xxxxxxxxx>
- Date: Mon, 17 Sep 2012 01:57:56 -0400
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@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,
> 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..
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: