Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: index contention in RAC

Re: index contention in RAC

From: Anand Rao <panandrao_at_gmail.com>
Date: Sat, 22 Apr 2006 10:17:50 +0530
Message-ID: <d70710370604212147w14b80e2do429e1cd5466efe02@mail.gmail.com>


Johan,

There are some very good points in this thread made by many contributors.

we do similar things for our application (Telecom Billing) on a 4-node RAC cluster. try and implement a mechanism within the application where you can direct inserts from a particular node to a particular table partition. this can help reduce some of the block pinging across the nodes. having 1 sequence for each node is indeed the best option for this scenario, especially when you have large inserts per second. caching a large number of sequences is surely a good idea.

also, 10g R2 has resource affinity at object level + file level and based on some very stringent conditions. If you don't have 100s of partitions, can you distribute each index partition to a particular tablespace (which maps to a single datafile)? not sure about ASM, totally new to it... in any case this could be a administration hassle for a VLDB.

Hence, if you are inserting data at a very high rate per second,

the chances of your index and table partition being associated with a particular instance increases, atleast according to the Resource Remastering principles!

local indexes will help in this case but i wouldn't blindly recommend them as it is very application dependant and performance/scalability factors have to be considered.

Even without the help of 10g R2 features, we have effectively reduced index block contention to a significant extent using some of the methods mentioned all along this thread and our customers mainly use Oracle RAC EE 9205.

cheers
anand

On 22/04/06, Johan Eriksson <Johan.Eriksson_at_bossmedia.se> wrote:
>
> Hi
>
> (and thanks to all the other answers I so far has got, I will take time
> this weekend and test the sugguestions)
>
> You are right, I hadn't test the script I included, I thought I copied the
> correct create índex into the mail. But the create statement I used in the
> DB created an unique index.
>
> I have tried without the second index/constraint and that didn't make much
> of a difference.
>
> /johan
>
>
> -----Ursprungligt meddelande-----
> Från: oracle-l-bounce_at_freelists.org genom Ghassan Salem
> Skickat: fr 2006-04-21 18:05
> Till: Johan Eriksson
> Kopia: oracle-l_at_freelists.org
> Ämne: Re: index contention in RAC
>
>
> Johan,
> Are you sure of your description? I mean when I try your script, it gives
> an
> error on the
> first 'alter table add constraint ' statement. The PK you're creating
> should
> use a unique index, and the index you create a local does not have ts1 in
> it, and so cannot be unique and local.
> Also, you're creating a second unique constraint, that will create a
> global
> unique index, hence you'll get much more contention on it's blocks
> (depending on the values you put in n2, n3 and n4.
>
> rgds
>
> On 4/21/06, johan Eriksson <johan.eriksson_at_bossmedia.se> wrote:
> >
> > Hi
> >
> > we have a table that looks like this:
> >
> > CREATE table t (
> > n1 NUMBER NOT NULL,
> > n2 NUMBER NOT NULL,
> > n3 NUMBER NOT NULL,
> > n4 NUMBER NOT NULL,
> > ts1 TIMESTAMP NOT NULL,
> > ts2 TIMESTAMP NOT NULL,
> > n5 NUMBER(19,4) DEFAULT 0 NOT NULL,
> > n6 NUMBER(19,4) DEFAULT 0 NOT NULL,
> > n7 NUMBER(19,4) NOT NULL,
> > n8 NUMBER(19,4) NOT NULL,
> > n9 NUMBER(19,4) NOT NULL,
> > n10 NUMBER(19,4) NOT NULL,
> > b1 BLOB,
> > b2 BLOB,
> > s1 VARCHAR2(128),
> > s2 VARCHAR2(128)
> > )
> > partition by range(ts1)
> > subpartition by hash(n1)
> > subpartitions 16
> > (partition P_YMAX values less than (MAXVALUE) )
> > ;
> >
> > create index idx_pk_n1 on t(n1)
> > tablespace index_test logging reverse local
> > /
> >
> > alter table t add constraint pk_n1 primary key(n1)
> > /
> >
> > ALTER TABLE t
> > ADD CONSTRAINT UQ_n3 UNIQUE (n2, n3, n4)
> > /
> >
> >
> > Our machines are AMD64 running RHEL 4 and we have 2 nodes in the RAC,
> > storage is ASM, the blocksize of the db is 8K, databaseversion is
> > 10.2.0.1. The usage of the tables will be mostly inserts and not that
> > much querying.
> > At the moment I am testing with approx 200 concurrent users, all just
> > doing inserts (which is the scenario we expect), the clients connects
> > with jdbc (trough hibernate)
> > The tablespaces are locally managed and ASSM.
> >
> > The problem we have are large amounts of GC buffer busy, since the
> > primary key is generated by an sequence I have made the index reverse to
> > eliminate some of the buffer busy events and that helped alot but the
> > major waiting is still on gc buffer busy and I want to know if there is
> > more I can do to minimize/eliminate this?
> >
> > /johan
> >
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 21 2006 - 23:47:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US