Re: RAC performance and redundancy questions.

<silikon2_at_gmail.com> wrote in message
news:1131490075.302079.9130_at_g47g2000cwa.googlegroups.com...
> Hello. We are running a three node RAC on 10g using a NetApp FAS250
> and three Sunfire 210 servers.
>
> I've got two problems I hope someone can help me with:
>
> (1) If one of the three nodes goes down, the other two continue to
> operate BUT I can only select and update but NOT insert. The DBAs
> can't really seem to explain this. My hunch as a software developer is
> that this might be by design--- I could certainly see a case where one
> might want to be able to select from their database if it were
> partially functional but prevent writes until it was corrected. Is
> this a configuration parameter or something?
>

    A basic requirement for RAC is that a highly concurrent     application has to be designed to ensure separation of     activity. Being able to select, update, and delete but haning     on inserts is possible.

    Inserts TEND to be directed at a very limited number of blocks     (and of table if you are not running ASSM, right hand edge of     index if you have sequence-generated primary keys with a small     cache on the sequence).

    If one instance is recovering for another, then there is a period when     blocks that have been modified by the crashed node have to be pinned,     and their global cache resources locked, whilst the blocks are recovered.

    I believe that every such block has to be recoevered before any of them     are free for use. And this could result in the phenomenon you are seeing.

    (It is possible that some of your other activities are also blocked, but the

    inserts stand out as the primary victim).

> (2) I've run extensive load tests on this database. I have a series of
> transactions simulating a multiple programs from different system that
> I am porting to this. The entire set of transactions is perhaps 20 to
> 30 and I view them automatically for purposes of my testing. I can run
> about 20,000 of these sets an hour from . The weird thing is, I get
> 20,000 whether I run against all three nodes using load balancing via
> tnsnames but I also get 20,000 if I run solely against any one node.
> Does this seem normal? There's nothing expensive exotic about my
> queries, just selects, inserts updates. I'm wondering if it's an issue
> of contention of all the nodes talking to each other because I've
> gotten similar performance on a 4 CPU single box.
>
> Thanks---
>

    It sounds as if you have some serialisation points in your application     that make it impossible for RAC to scale. Sequences defined as     NOCACHE, home-grown sequence tables, (freelists 1 freelist groups 1)     if not running ASSM, right-hand edge problems on indexes that might     have to be reversed, etc.

    The critical question for RAC is 'are there any blocks that will have to spend

    all their time travelling down the interconnect because every wants to change

    them'.

    In 10g, v$segstat collects some segment level statistics that will show     you if you have any segments that have been subject to a lot of global     cache (gc) activity.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005