RE: index contention in RAC
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <johan.eriksson@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 21 Apr 2006 11:25:51 -0400
Two quick things:
1) IF you have a way to direct service requests that are candidates to make
these inserts to a particular instance, and IF that is not an absurdly large
fraction of your users such that you effectively put all load on a single
instance, then that will help.
2) You mentioned that you use a sequence to generate the PKs. Oracle stores
sequences very densely. If you've done something like ordered nocache on the
sequence, that is a fabulous way to make the block of seq$ holding your
sequence ping like crazy. If you build your database so seq$ has only one
row per block, and if it is acceptable to you to have a sequence associated
with each instance to create the PK (tacking on the instance number,
perhaps, appropriately shifted so the resulting ranges are disjoint), then
you can prevent this, as well as "false pinging" due to any other sequences
stored in the same block as your sequence needing to ping (even though the
data is not relevant to the insert process, the block images are.) If you
can't build seq$ to hold only one row per block, then an alternative is to
create "junk" sequences between the creations of the real instance oriented
sequences in sufficient quantity to burn up the block space. Since the
"junk" sequences are never used, they can't generate "false pings."
These two quickies are by no means exhaustive and may or may not be relevant
to your case. For example, if you really need the exact order of creation of
rows regardless of the inserting instance, the sequence ping is required
unless you have an absolutely guaranteed clock coordination between the
nodes (which would also present its own overhead.)
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of johan Eriksson
Sent: Friday, April 21, 2006 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: index contention in RAC
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:
