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@freelists.org genom Ghassan Salem
Skickat: fr 2006-04-21 18:05
Till: Johan Eriksson
Kopia: oracle-l@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@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
>
>
>