Re: index contention in RAC
- From: "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
- To: johan.eriksson@xxxxxxxxxxxx
- Date: Fri, 21 Apr 2006 18:05:18 +0200
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:
