Oracle Index rebuild online – 10g Vs 11g

An index is basically used for faster access to tables. Over a period of time the index gets fragmented because of several DMLs running on table.
When the index gets fragmented, data inside the index is scattered, rows / block reduces, index consumes more space and scanning of index takes more time and more blocks for same set of queries.
To talk in index terminology, we will have a single root block, but as fragmentation increases there will be more number of branch blocks and more leaf blocks. Also the height of index will increase.

To fix the above issue, we go for index rebuild. During index rebuild, the data inside the index is reorganized and compressed to fit in minimum number of blocks, height of the index is reduced to minimum possible level and performance of queries will increase.
Your search becomes faster and your query will read less number of blocks.

There are 2 methods to rebuild the index.

1) Offline index rebuild – alter index <index name> rebuild;
2) Online index rebuild  – alter index <index name> rebuild online;

With offline index rebuild, table and index is locked in exclusive mode preventing any translations on the table. This is most intrusive method and is used rarely in production unless we know for sure that modules are not going to access the table and we have complete downtime.

With online index rebuild, transactions can still access the table and index. Only for very less amount of time the lock is acquired on the table (by index rebuild operation). For rest of the time table and index is available for transactions.
However there is a difference in a way this internal online rebuild mechanism works in 10g and 11g. With 11g things are refined further to minimize the impact.

I will first explain the 10g method of rebuilding the index, after that will see the behavior in 11g

10g behaviour:

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the OIB is in progress and merge all the changes from journal table to complete index build operation.

If I have a table with the structure of (A NUMBER, B NUMBER, C CHAR(100), D NUMBER) and to create index on (A, B, C) column, Oracle would create journal table like:

create table “ORACLE”.”SYS_JOURNAL_18155″ (C0 NUMBER, C1 NUMBER, C2 VARCHAR2(100), opcode char(1), partno number, rid rowid, primary key( C0, C1, C2 , rid )) organization index;

Before 11g, OIB will get in the DML queue to lock the table exclusively while preventing the new DML’s to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it’ll still keep the share lock on the table to prevent any other DDL’s) for DML’s to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add “ROWID” to that list to make it as primary key.

“OPCODE” column represents the type of operation like “I” for Insert and “D” for Delete.
“PARTNO” column represents partition number of the underlying table.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. Any update operation of index key columns would be converted to “DELETE” and “INSERT” in the journal table.

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, it’s reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML’s again to do the final merge and drop the journal table before releasing the mode 6 exclsuive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML’s happen while Oracle is doing the merge, it’ll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

If there is any long running DML before issuing the OIB, Oracle would wait on the long running transaction and it will prevent upcoming DML’s also. This is one of the major problems with OIB. Same thing can happen when it is doing the final merge.

So in the above process lock is acquired 2 times, one during the start of index creation when journal table is created online index rebuild process needs to be take exclusive lock on table to prevent DMLs from changing data. Once journal table is created online index rebuild process will release DML lock and hold a shared lock. Any DMLs happening after this will have entry made into journal table.
Again at the end of the process online index rebuild process will try to take exclusive lock to merge the last block of journal table into the main index.

Following example demonstrate the same:

Create test table and insert huge number of rows

</pre>
SQL>create table test as select * from dba_objects;

Table created.

SQL>insert into test select * from test;

29493 rows created.

SQL>/

58986 rows created.

Like this add more rows till it becomes big

Check the size of table


SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'TEST';

SUM(BYTES)/1024/1024/1024
-------------------------
 .430053711

Create index on the table

SQL>create index I1 on test(OBJECT_NAME);

Index created.

Check the size of index.

SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'I1';

SUM(BYTES)/1024/1024/1024
-------------------------
 .129272461

Now from the session 1, try inserting a record in the TEST table

Session 1 SID: 3204
SQL Text :

SQL>insert into test (owner, object_name) values ('AVDEO','NEW_TEST');
1 row created.
SQL>

From session 2, try rebuilding the index online

Session 2 SID: 3046
SQL text :

alter index I1 rebuild online

From session 3, run another DML command.

Session 3 SID: 3827
SQL Text:

update test set OWNER = ‘DEO2’ where OWNER = ‘DEO’

If we check v$lock table we can see session 2 (online index rebuild) is waiting on session 1 (insert). So unless insert completes, session 2 doing online index rebuild will not get a exclusive lock.


SQL>select
 2      l.SID oracle_id,
 3      decode(TYPE,
 4          'MR', 'Media Recovery',
 5          'RT', 'Redo Thread',
 6          'UN', 'User Name',
 7          'TX', 'Transaction',
 8          'TM', 'DML',
 9          'UL', 'PL/SQL User Lock',
 10          'DX', 'Distributed Xaction',
 11          'CF', 'Control File',
 12          'IS', 'Instance State',
 13          'FS', 'File Set',
 14          'IR', 'Instance Recovery',
 15          'ST', 'Disk Space Transaction',
 16          'TS', 'Temp Segment',
 17          'IV', 'Library Cache Invalidation',
 18          'LS', 'Log Start or Switch',
 19          'RW', 'Row Wait',
 20          'SQ', 'Sequence Number',
 21          'TE', 'Extend Table',
 22          'TT', 'Temp Table', type) lock_type,
 23      decode(LMODE,
 24          0, 'None',
 25          1, 'Null',
 26          2, 'Row-S (SS)',
 27          3, 'Row-X (SX)',
 28          4, 'Share',
 29          5, 'S/Row-X (SSX)',
 30          6, 'Exclusive', lmode) lock_held,
 31      decode(REQUEST,
 32          0, 'None',
 33          1, 'Null',
 34          2, 'Row-S (SS)',
 35          3, 'Row-X (SX)',
 36          4, 'Share',
 37          5, 'S/Row-X (SSX)',
 38          6, 'Exclusive', request) lock_requested,
 39      decode(BLOCK,
 40          0, 'Not Blocking',
 41          1, 'Blocking',
 42          2, 'Global', block) status,
 43      OBJECT_NAME
 44  from    v$locked_object lo,
 45      dba_objects do,
 46      v$lock l
 47  where     lo.OBJECT_ID = do.OBJECT_ID
 48  AND     l.SID = lo.SESSION_ID
 49  /

 ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3204 DML                        Row-X (SX)                               None                                     Blocking             TEST
 3204 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

From the above output we can see that first session is 3204 which is running insert.
Session 2 is 3075 which is running index rebuild and is waiting for “DML share” lock
session 3 is 3900 running update DML and waiting for “DML Row-X” lock

If we see which session is blocking what we see below rows


Logn Ora    SQL/Prev                     OS                                                   Call
><    Sid-Ser-S Time User   Hash       Module            User   Svr-Pgm    Machine      HR Resource           Elap Ctim Locked Object
--------------- ---- ------ ---------- ----------------- ------ ---------- ------------ -- ------------------ ---- ---- --------------------
>  3204,23884-I 0953 ADVAIT 0          SQL*Plus          advait 7924-orac  db-fc-admin- 3  TM:1664558-0       448s 448s 1664558
 < 3075,20427-A 0959 ADVAIT 3645454058 SQL*Plus          advait 15432-orac db-fc-admin-  4                    433s 432s 1664558
 < 3900,30565-A 0959 ADVAIT 4227999514 SQL*Plus          advait 16004-orac db-fc-admin-  3                    408s 408s 1664558

Above output is generated by my custom scripts.

It says that 3204 is parent session and 3204 and 3900 is waiting on 3204.

In case of 10g if we commit session 1 (sid 3204 running insert), it will allow online index rebuild to continue and get the lock.
Session 3 will still continue to wait until session 2 running online index rebuild releases the lock.

After some time session 2 will start with index rebuild, it will take lock for very short period of time and releases the lock.
Session 3 (update DML) will aquire the lock after session 2 (index rebuild) releases the lock.

Session 3 completed while session 2 (index rebuild) does table scan.

SQL>update test set OWNER = 'DEO2' where OWNER = 'DEO'
 2  ;
1 rows updated.
SQL>

Now after session 3 DML completes, we didnt commit session 3. We are waiting for session 2 to complete now.

We can check v$session_longops to see current operation for index rebuild


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 3075;

 SID OPNAME                                                                SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
------ ---------------------------------------------------------------- ---------- ---------- -------------- ---------------
 3075 Sort Output                                                           25011      25011              0              15
 3075 Table Scan                                                            56036      56036              0              61

2 rows selected.

So we can see that v$session_longops that table scan for index rebuild completed. But still the session 2 for index rebuild is hanging.

If we run above query to see the locks it gives below output


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         TEST
 3075 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3900 DML                        Row-X (SX)                               None                                     Blocking             TEST

13 rows selected.

&nbsp;

So sid 3900 is the blocking session. This is session 3 which has run update query but havent commited.
So online index rebuild is waiting to acquire lock second time at the end. This is the time when it has to do the merging.

So after we commit session 3, session 2 doing online index rebuild will acquire the lock and will complete.

11g behaviour:

With 11g, significant changes were introduced to address all these problems.

Oracle will still wait for long running transactions to complete before starting the OIB process, but it will not block the upcoming DML’s. Oracle will not use exclusive table lock (mode 6) for OIB, which will eliminate any DML hang situations.

As soon as OIB process is initiated, Oracle will create IOT journal table and use internal trigger on the underlying table to keep track of the changes.

Once the journal table is created and the long running transactions are completed, Oracle will create the index by reading the table blocks in “CURRENT” mode, which means any committed changes in a block happened before the current SCN are read instead of the old way of accessing the blocks as of journal table creation time. This will virtually eliminate chance of running into rollback segment too small errors.

Oracle will recreate the internal trigger to directly modify the target index structure once the index build is completed (but the merge job is still pending). This means all user sessions will now directly update the target index instead of updating the journal table. Any change coming through would first be checked in the journal table before consolidating the change for the target index (this is best explanied with the example below).

Record “A” was inserted with rowid “RID” before the merge phase and hence it is tracked in the journal table.
Record “A” was deleted during the merge phase. Now the user session will read the journal table by record “A” with rowid “RID” and apply that change to the target index before deleteing the same record. It will also mark that record as consumed (i.e. deleted) in the IOT journal table leaf block.

While the user sessions continue to update the target index with the ongoing changes, OIB session will do the merge of journal table changes into the final index, as there will not be any ongoing DML changes being tracked in the journal table, this process will complete faster than before (i.e. pre 11g).

Lets take an example. In case of 11g we have MYTAB table and MYTAB_IDX index


SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB';

SUM(BYTES)/1024/1024
--------------------
 406.5

SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB_IDX';

SUM(BYTES)/1024/1024
--------------------
 152.5

In this case also do the following activity

From session 1 run a DML (insert statement) – SID 2213
From session 2 run index rebuild online command – SID 2222
From session 3 run a DML (update statement) – SID 2177

The current status in v$lock shows following


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701

Now if you compare it with 10g we see significant differences.

1) We see lot of extra locks in 11g compared to 10g
2) and the most important differences is that in case of 10g if you see last 2 rows in first output of v$lock we see


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

and same in 11g is


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 .
 .
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB

In case of 10g if you see 3075 is the one which is doing an index rebuild and is waiting on initial sid 3204. Also 3rd session (3900) running DML is waiting for “Row-X (SX)” lock.
In case of 11g if you see 2222 is the one which is doing an index rebuild and is waiting on initial sid 2213. Also 3rd session (2177) running DML already got “Row-X (SX)” lock and is not waiting for anything.

So in case of 11g if index rebuild is waiting for initial lock to acquire, it does not block incomming DMLs.

After I commit 1st session I see that none of the session is now blocking index rebuild operation and so it can acquire initial lock


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

My session 3 is already complete and I commited that session now. session 3 had no dependency on any session.

After index rebuild start (its doing table scan), I run one more DML from session 3, but I dont commit. In this case index rebuild will hang again at the end waiting for exclusive lock on table.
Unless session 3 gives the lock index rebuild cannot proceed.

Important change here between 10g and 11g is that if we start 4th session while index rebuild waits for 2nd time for lock and if I commit session 3, in case of 10g index rebuild will get precedence and it will acquire lock blocking session 4.
In case of 11g session 4 will get presedence and will acquire lock for DML, where as index rebuild will wait further until all DML sessions are complete and lock is available for it to acquire. So online index rebuild will prioritize all other sessions before him to acquire locks.

From v$session_longops we can see that tablescan operation completed


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 2222 and opname not like 'RMAN%';

 SID OPNAME                              SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
---------- ------------------------------ ---------- ---------- -------------- ---------------
 2222 Table Scan                          51736      51736              0              12
 2222 Sort Output                         21422      21422              0               7

Now online index rebuild session is waiting for session 3 (SID 2177), which I started which table scan operation was inprogress


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

Unless I commit that session index rebuild will wait. If I start another session now (session 4) and commit session 3, index rebuild will still wait for session 4 to complete.
Like this it can continue to wait until all transactions are done.

Example I started session 4 (SID 2223 ) and ran DML, commited in session 3

now index rebuild session (SID 2222) is waiting for session 4.


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2223 AE                         Share                                    None                                     Not Blocking         MYTAB
 2223 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2223 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

So main difference in case of online index rebuild procedure is online index rebuild process gives precedence to other DML sessions to acquire locks. The process has become less intrusive now.

Hope this helps !!

WARNING: inbound connection timed out (ORA-3136)

Many times I faced this issue “WARNING: inbound connection timed out (ORA-3136)” in alert log and if you are using data guard, then you can see that dgmgrl status will show failed because standby is not able to sync up with primary.

The “WARNING: inbound connection timed out (ORA-3136)” in the alert log indicates that the client was not able to complete it’s authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

Root cause of this issue could be

1. malicious client is flooding database servers with connect requests that consumes resources

2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT=0 in $ORACLE_HOME/network/admin/sqlnet.ora file

Once you set this parameter the warnings in alert log will go away. If you are using data guard, may be you want to enable the databases on which you were getting warnings.

Hope this helps !!