Re: Sequences and RAC -- Inputs Required
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Jun 2004 11:07:08 +0100
You may be right. Sequences (and any sequentially
generated data) can cause significant problems with
RAC. If you have created the sequences with default
cache sizes (which is 20), then you are likely to see
lots of SQ enqueue waits, and lots of activity as blocks
in the seq$ table are bounced back and forth across the
interconnect.
Setting a large cache size on the sequence is the first
step. alter sequence ..... cache 10000.
Depending on the table and tablespace definitions
for your hot tables, you may also have to modify
the freelist settings on the tables, or rebuild the tables
with multiple freelist groups. If you happen to have
used tablespaces with Automatic Segment Space
Management (ASSM) enabled, then the freelists
and freelist groups are irrelevant. However, multiple
freelists, and ASSM usually wreck the clustering_factor
on sequential indexes, so if this is an upgrade, don't
be surprised if Oracle starts to ignore some of you
indexes.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: <k.sriramkumar@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 01, 2004 10:34 AM
Subject: Sequences and RAC -- Inputs Required
: Hi Gurus,
:
: We are testing our application(OLTP) in 2 node RAC setup(HP UX 11.11)
in 9iR2(9.2.0.4). We are seeing a negative scalability when we move to RAC.
Say if we had x TPS in a 4 CPU box, we are seeing only a .6x-.7x when we
move to a 2 way 4 CPU box.
:
: I have checked the basic RAC setup and the setup looks ok.
:
: Our application relies heavily on sequences and I feel that the sequences
could be the one that is causing the problem. I am planning to cache the
sequences ( with no order)
:
: Have any one of you seen better TPS when moved to RAC?? any pointers for
achieving better TPS in RAC?
:
: Best Regards
:
: Sriram Kumar
:
:
:
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
