Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: index contention in RAC

RE: index contention in RAC

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 21 Apr 2006 14:00:40 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKEEDNHPAA.mwf@rsiz.com> 





You are just proving that in the dictionary creation ordered, cached is
allowed but that the instance being parallel prevents the cache from taking
place. If you were to "de-RAC" the instance (not merely having one instance
running), then the cache actually being used would re-appear. This is
actually a sane choice by Oracle, since otherwise RACing a database could
break it. That would be bad.



-----Original Message-----



From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]
Sent: Friday, April 21, 2006 12:01 PM


To: Mark W. Farnham; johan.eriksson_at_bossmedia.se; oracle-l_at_freelists.org
Subject: RE: index contention in RAC



Hmm....having never tested my statement, but having heard it from
reliable sources, I believed it was true.



A quick test seems to imply that I was not correct.  A sequence where
DBA_SEQUENCES.ORDER_FLAG='Y' does seem to to maintain caching.



My simple testcase, follows, 9.2.0.6 EE, 4-node RAC Cluster on Solaris 9
w/ VCS providing cluster services.



In the first instance:


SQL> create sequence test_seq start with 1 increment by 1 cache 10
order;



Sequence created.



SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10





1 row selected.




In the second instance:


SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10





Back in first instance:


SQL> select test_seq.nextval from dual;



   NEXTVAL





         1



1 row selected.



SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10





1 row selected.



And in the second:


SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10







And now, still in the second instance:



SQL> select test_seq.nextval from dual;



   NEXTVAL





         2



SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10





And, in the first instance:



SQL> select test_seq.nextval from dual;



   NEXTVAL





         3



1 row selected.



SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10





1 row selected.




And, one last time in the second instance:
SQL>  select test_seq.nextval from dual;



   NEXTVAL





         4



SQL> select sequence_name, order_flag, cache_size from dba_sequences
where sequence_owner='ADDS' and sequence_name = 'TEST_SEQ';


SEQUENCE_NAME                  O CACHE_SIZE


------------------------------ - ----------

TEST_SEQ                       Y         10









So, caching seems to continue, even though I have set the ORDER_FLAG and
I alternate between instances.......




Seeing is believing.....does anyone see a flaw to my logic?



-Mark





--



Mark J. Bobak


Senior Oracle Architect


ProQuest Information & Learning



For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled.  --Richard P. Feynman, 1918-1988




-----Original Message-----



From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Friday, April 21, 2006 11:36 AM


To: Bobak, Mark; johan.eriksson_at_bossmedia.se; oracle-l_at_freelists.org
Subject: RE: index contention in RAC



Excellent point. Also, one more quickie:



If you CAN use multiple sequences (one associated with each instance),
then you might also partition your PK index by instance, so that those
blocks don't need to ping. At least I *think* that Oracle won't need to
ping a block for a partition you're not in, but I haven't tried it.



-----Original Message-----



From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]
Sent: Friday, April 21, 2006 11:34 AM


To: mwf_at_rsiz.com; johan.eriksson_at_bossmedia.se; oracle-l_at_freelists.org
Subject: RE: index contention in RAC



Also, don't forget, for sequences in a RAC database, 'ORDERED' implies
'NOCACHE'.





--



Mark J. Bobak


Senior Oracle Architect


ProQuest Information & Learning



For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled.  --Richard P. Feynman, 1918-1988




-----Original Message-----



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Friday, April 21, 2006 11:26 AM


To: johan.eriksson_at_bossmedia.se; oracle-l_at_freelists.org
Subject: RE: index contention in RAC



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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On
Behalf Of johan Eriksson


Sent: Friday, April 21, 2006 10:58 AM


To: oracle-l_at_freelists.org


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





--



http://www.freelists.org/webpage/oracle-l




--



http://www.freelists.org/webpage/oracle-l






--



http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 21 2006 - 13:00:40 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US