Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sequences and RAC -- Inputs Required

Re: Sequences and RAC -- Inputs Required

From: Jonathan Lewis <>
Date: Tue, 1 Jun 2004 11:07:08 +0100
Message-ID: <00e201c447c0$3319ac70$7102a8c0@Primary>

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.


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st

: Hi Gurus,
: We are testing our application(OLTP) in 2 node RAC setup(HP UX 11.11)
in 9iR2( 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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jun 01 2004 - 05:03:54 CDT

Original text of this message