RE: index contention in RAC
- From: "ramick" <ramick@xxxxxxxxxxx>
- To: <salem.ghassan@xxxxxxxxx>, <johan.eriksson@xxxxxxxxxxxx>
- Date: Fri, 21 Apr 2006 09:14:07 -0700
"Real Application Clusters databases do not support the CACHE option with
the ORDER option of CREATE SEQUENCE when the database is mounted in cluster
mode. Oracle cannot guarantee an order if each instance has some sequence
values cached. Therefore, if you should create sequences with both the CACHE
and ORDER options, they will be ordered but not cached."
From Oracle9i Real Application Clusters Administration - Chapter 3 at the
end of the chapter.
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ghassan Salem
Sent: Friday, April 21, 2006 9:05 AM
To: johan.eriksson@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
