SV: index contention in RAC

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