Re: Sequences and RAC -- Inputs Required

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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.

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_at_iflexsolutions.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jun 01 2004 - 05:03:54 CDT