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.
