Re: index contention in RAC
- From: "Anand Rao" <panandrao@xxxxxxxxx>
- To: Johan.Eriksson@xxxxxxxxxxxx
- Date: Sat, 22 Apr 2006 10:17:50 +0530
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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx genom Ghassan Salem
> Skickat: fr 2006-04-21 18:05
> Till: Johan Eriksson
> Kopia: oracle-l@xxxxxxxxxxxxx
> Ã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@xxxxxxxxxxxx> 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
> >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
Other related posts:
