index contention in RAC

From: johan Eriksson <johan.eriksson_at_bossmedia.se>
Date: Fri, 21 Apr 2006 16:58:18 +0200
Message-Id: <1145631498.24423.69.camel@erijoh.ad.bossmedia.se>

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

Received on Fri Apr 21 2006 - 09:58:18 CDT