Oracle Logbook

December 5, 2011

Decreasing the interconnect traffic among RAC instances

Filed under: Uncategorized — heliodias @ 3:43 pm

When you access one block trought two or more sessions using different instances, The data must come and back ,  between both instances.

Now imagine that you area inserting data using two instances, what actually happen is that the block must “travel” from one instances to another, consuming time and resource as you may notice below:

INSTANCE1> insert into l3ajuste values (10);

1 row created.

INSTANCE2> insert into l3ajuste values (10);

1 row created.

INSTANCE2> commit;

Commit complete.

INSTANCE1> commit;

Commit complete.

SQL> select n,rowid from l3ajuste;

         N ROWID
———- ——————
        10 AA8H+3AAKAAAAE7AAA
        10 AA8H+3AAKAAAAE7AAB

 As you notice we were using the same block, on of course different rows.

And this is the root of a interconnect traffic when you are using SEGMENT_SPACE_MANAGEMENT=’MANUAL’;

When you instead create the table at a TABLESPACE with the SEGMENT_SPACE_MANAGEMENT=’AUTO’

Oracle is clever enough to make each instance use a different block, allowing us to have more performance since we don´t have the interconnect traffic, as you can see bellow:

SQL> create table l3ajusteB  (n number) TABLESPACE TBS_SEG_SPACE_AUTO;

INSTANCE1> insert into l3ajusteb values (10);

1 row created.

INSTANCE2> insert into l3ajusteb values (10);

1 row created.

INSTANCE2> commit;

Commit complete.

INSTANCE1> commit;

Commit complete.

SQL> select n,rowid from l3ajusteb;

            N ROWID
———- ——————
        10 AA8IAGABXAAB3CsAAA
        10 AA8IAGABXAAB3CzAAA

As you notice , we are not sharing the same block, thus the interconnect traffic , simple doesn´t happen.

The botton line is, if you want to decrease the interconnect traffic among RAC instances, the first step is move your data to automatic space management tablespaces.

Advertisements

5 Comments »

  1. Hi – did you try to set multi free list groups, if you are using manual segment space management + RAC, You should use multifreelists so that each instance will have it’s own free list.

    Could you pls try same test with multifreelist-group and post the results?

    Comment by Jagjeet Singh — December 5, 2011 @ 9:29 pm | Reply

    • Hi JS, thanks for the comments,
      Even though the free list group you by pass the back and forth of blocks flying around the instances,
      you still gonna suffer from contention for find the free block (intersession contention).

      On the other hand, ASSM uses a set of bitmap blocks (BMB) that prevent this second level of contention, recording bits of information among the blocks to show faster how free they are.

      The BMB are even capable to create intermediate levels of BMB making the search for free blocks, incredible fast.

      Comment by heliodias — December 6, 2011 @ 2:15 pm | Reply

  2. Hi – Nice post

    But did you try to create multiple freelists groups, if you are using manual segment space management with RAC, you have to create multiple freelist groups.

    I hope, then oracle will choose diff. block for above example with manual ssm.

    Regards,
    Js

    Comment by Jagjeet Singh — December 6, 2011 @ 8:00 am | Reply

    • The freelist groups will indeed use diffs block.
      I choose present the ASSM due to the
      even though the free list group you by pass the back and forth of blocks flying around the instances,
      you still gonna suffer from contention for find the free block (intersession contention).

      On the other hand, ASSM uses a set of bitmap blocks (BMB) that prevent this second level of contention, recording bits of information among the blocks to show faster how free they are.

      The BMB are even capable to create intermediate levels of BMB making the search for free blocks, incredible fast.

      Comment by heliodias — December 6, 2011 @ 2:26 pm | Reply

      • Thanks for the reply, I agree here, I was just referring to above example.

        >>On the other hand, ASSM uses a set of bitmap blocks (BMB) that prevent this second level of contention, recording bits of information among the blocks to show faster >>how free they are.

        For the above context –
        Another example i would like to share with you. Recently i got chance to work on POV with half rack exadata ( 4 db nodes ) with 1200 online users ( using load runner ), I had contention on one table which was on ASSM as lot of users were inserting on same table [ initrans was already set high 255 ] but still I had contention,
        Then I had to move that table to LMT non-assm and manually added multiple freelists + multi-freelists groups and it did relief LOT of contention.

        That is something against the documentation but i have seen couple of time, if you talk about really HIGH concurrency , non-assm stuf still works great but yes, the may need more space.

        I think putting one test case won’t be difficult to prove my point.

        Comment by Jagjeet Singh — December 6, 2011 @ 4:12 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: