DOES ONLINE INDEX REBUILD NEED DOWNTIME AND LOCK? WHAT IS THE DIFFERENCE BETWEEN OFFLINE AND ONLINE?
Does index rebuild using online clause works without any locks or require a lock to process the rebuild? Most of the DBA’s has this misconception that, index rebuilding in online mode execute without any blockings between DML. Online mode gives freedom for the database with near or zero downtime which means any DML transactions can acquire exclusive lock on the table even after an index rebuild is executed without interruptions but DDL(Online rebuild) cannot progress during modification of data in the table until commit. So, if the index is holding many records, then calculate the maintenance duration accordingly. If the DBA is concerned about faster index maintenance rather than table downtime, then offline rebuild is efficient and if it is about table downtime, then online rebuild is efficient.
Note: Table downtime means prevention of application from modifying the rows in the table using DML(Insert, Update, Delete)
The only independence that is available in online mode is that, online rebuild operation waits and cope up with the other DML transaction to complete its work and then start the rebuild process in between when getting a chance post DML commit.
In offline mode, below error is reported immediately if other DML clutch the table lock.
“ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired“
Both offline and online index rebuild require lock at some point but the difference is,
- Online rebuild require “Row share mode” lock and offline rebuild require “Share mode nowait”
- Online rebuild is slower and offline rebuild is faster
- Online rebuild may cause temporary performance issues in heavy DML application and offline rebuild require complete table downtime
Online method works with these steps
- Create a snapshot table in the form of “SYS_JOURNAL_N”
- Create an unique IOT index for “SYS_JOURNAL_N” table
- Populate the newly cloned index with the defragmented records with sorted key
- Populate the original index with the defragmented records with sorted key
- Drop the “SYS_JOURNAL_N” table after rebuild
- Above all, if PGA is not sufficient to sort the records, then processing spill to temporary tablespace.
[oracle@staxhost ~]$ grep -a "SYS_JOURNAL" staxdb_ora_30481_IRO_tkp.trc
--Table creation phase
create table "SYS"."SYS_JOURNAL_76683" (C0 NUMBER, opcode char(1), partno
number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE
"SYSTEM"
"SYS"."SYS_JOURNAL_76683"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE
0 0 0 TABLE ACCESS FULL SYS_JOURNAL_76683 (cr=0 pr=0 pw=0 time=3 us starts=1)
--Index creation phase
CREATE UNIQUE INDEX "SYS"."SYS_IOT_TOP_76710" on
"SYS"."SYS_JOURNAL_76683"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE
"SYSTEM" STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT) NOPARALLEL
--Populate the clone index
insert into ind_online$ (obj#,type#,flags)
values
(:1,:2,:3)
--Populate the original index
INSERT INTO indrebuild$(obj#, dataobj#, ts#, file#, block#, pctfree$,
initrans, maxtrans, compcols, flags)
VALUES
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
--Update the clone index
UPDATE ind_online$ SET flags=:2
WHERE
obj#=:1
--Update the original index
UPDATE indrebuild$ set dataobj#=:2, ts#=:3, file#=:4, block#=:5, pctfree$=:6,
initrans=:7, maxtrans=:8, compcols=:9, flags=:10
WHERE
obj#=:1
--Drop table phase
drop table "SYS"."SYS_JOURNAL_76683" purgeFirst session does the following and does not commit the transaction.
kIsH@STAX<>select sid from v$mystat where rownum < 2;
SID
----------
94
kIsH@STAX<>create table dbobj as select * from dba_objects;
Table created.
kIsH@STAX<>insert into dbobj select * from dba_objects;
73462 rows created.
kIsH@STAX<>create index idxobj on dbobj(object_id);
Index created.
kIsH@STAX<>insert into dbobj select * from dba_objects;
73463 rows created.Second session rebuild the index online and waiting for concurrent access to the table.
kIsH@STAX<>select sid from v$mystat where rownum < 2;
SID
----------
1
kIsH@STAX<>alter index idxobj rebuild online;
<======== waiting for lock on the table dbobjFirst session acquired TX lock. User left the command line and gone for a coffee.
Here session 2 waits on enq: TX – row lock contention due to uncommitted INSERT statement
kIsH@STAX<>col sid for 99999
kIsH@STAX<>col event for a20
kIsH@STAX<>col blocking_session for 99999999
kIsH@STAX<>col sql_id for a20
kIsH@STAX<>select sid,event,sql_id,blocking_session from v$session where sid=1;
SID EVENT SQL_ID BLOCKING_SESSION
------ -------------------- -------------------- ----------------
1 enq: TX - row lock c ffnqahhcsy8g0 94
ontention
kIsH@STAX<>select sid,event,sql_id,blocking_session from v$session where sid=94;
SID EVENT SQL_ID BLOCKING_SESSION
------ -------------------- -------------------- ----------------
94 SQL*Net message to c 2nshzv6a0h4p2
lientSince the DML statement ‘INSERT’ acquired Row-X (exclusive) lock on the table which prevents concurrent sessions to access the object on shared mode and exclusive mode. Though the column BLOCKING_OTHERS shows “Not Blocking”, still DDL commands are not allowed to proceed due to change in the structure of the index.
kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>col OWNER for a20
kIsH@STAX<>col NAME for a20
kIsH@STAX<>select SESSION_ID,OWNER,NAME,MODE_HELD,MODE_REQUESTED,BLOCKING_OTHERS from dba_dml_locks;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE BLOCKING_OTHERS
---------- -------------------- -------------------- ------------- ------------- ----------------------------------------
1 SYS DBOBJ Row-S (SS) None Not Blocking
1 SYS SYS_JOURNAL_76683 Share None Not Blocking
94 SYS DBOBJ Row-X (SX) None Not Blocking
Lock type shows that “INSERT” statement has acquired exclusive lock with enqueue and prevents the SMON process from cleaning up the sort segments which need to be cleaned up for index rebuild.
kIsH@STAX<>select TYPE,NAME,DESCRIPTION from v$lock_type where type='SS';
TYPE NAME DESCRIPTION
-------------------- ------------------------------ --------------------------------------------------------------------------------
SS Sort Segment Ensures that sort segments created during parallel DML operations aren't prematu
rely cleaned up
kIsH@STAX<>select TYPE,NAME,DESCRIPTION from v$lock_type where type='SX';
TYPE NAME DESCRIPTION
-------------------- ------------------------------ --------------------------------------------------------------------------------
SX Spare Enqueue 1 Spare enqueue 1 for one-off backports
Use oradebug tool to trace the hang progress.
kIsH@STAX<>oradebug setospid 3944
Oracle pid: 34, Unix process pid: 3944, image: oracle@staxhost (TNS V1-V3)
kIsH@STAX<>oradebug unlimit
Statement processed.
kIsH@STAX<>oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_4290.trc
kIsH@STAX<>--wait for some time
kIsH@STAX<>oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_4290.trc
kIsH@STAX<>--wait for some time
kIsH@STAX<>oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_4290.trc
kIsH@STAX<>exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0Wait chains show that index rebuild is waiting on enq: TX – row lock contention, even though index rebuild is executed with online clause. Until, the insert statement is commited, the index rebuild wait to acquire the lock
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'CPU or Wait CPU'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0xcc111f32
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (staxdb.staxdb)
os id: 3944
process id: 34, oracle@staxhost (TNS V1-V3)
session id: 1
session serial #: 39412
module name: 1 (sqlplus@staxhost (TNS V1-V3))
pdb id: 1 (CDB$ROOT)
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580004
p2: 'usn<<16 | slot'=0x20007
p3: 'sequence'=0x7e3
time in wait: 227 min 17 sec
timeout after: never
wait id: 4460
blocking: 0 sessions
current sql_id: 3880962692
current sql: alter index idxobj rebuild online
short stack: ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+223<-__sighandler()<-semtimedop()+14<-skgpwwait()+187<-ksliwat()+2192<-kslwaitctx()+200<-ksqcmi()+27366<-ksqgtlctx()+3424<-ksqgelctx()+838<-ktuGetTxForXid()+324<-ktaddlvisprocres()+828<-ktaddlvis()+1222<-kdicrws()+29979<-kdicdrv()+681<-opiexe()+25075<-opiosq0()+4494<-kpooprx()+287<-kpoal8()+824<-opiodr()+1202<-ttcpip()+1218<-opitsk()+1900<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main
wait history:
* time between current wait and wait #1: 0.003004 sec
1. event: 'index (re)build lock or pin object'
time waited: 0.000006 sec
wait id: 4459 p1: 'namespace'=0x1
p2: 'lock_mode'=0x3
p3: 'pin_mode'=0x3
* time between wait #1 and #2: 0.000215 sec
2. event: 'reliable message'
time waited: 0.000638 sec
wait id: 4458 p1: 'channel context'=0x82ab5518
p2: 'channel handle'=0x82a9f038
p3: 'broadcast message'=0x82c54638
* time between wait #2 and #3: 0.001611 sec
3. event: 'index (re)build lock or pin object'
time waited: 0.000021 sec
wait id: 4457 p1: 'namespace'=0x4
p2: 'lock_mode'=0x3
p3: 'pin_mode'=0x3
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (staxdb.staxdb)
os id: 4290
process id: 70, oracle@staxhost (TNS V1-V3)
session id: 94
session serial #: 13032
module name: 1 (sqlplus@staxhost (TNS V1-V3))
pdb id: 1 (CDB$ROOT)
}
which is on CPU or Wait CPU:
{
last wait: 0.000197 sec ago
blocking: 1 session
current sql_id: 0
current sql: <none>
short stack: ksedsts()+426<-ksdxfstk()+58<-ksdxdocmdmultex()+5961<-ksdhng_request_msg_process_phase_diagn()+1534<-ksdhng_request_msg_process()+933<-ksdhng_graph_build_local()+361<-ksdhng()+961<-ksdxfhng()+676<-ksdxen_int()+3381<-opiodr()+1202<-ttcpip()+1218<-opitsk()+1900<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+243<-0x5541F689495641D7
wait history:
1. event: 'Disk file operations I/O'
time waited: 0.000031 sec
wait id: 1759 p1: 'FileOperation'=0x8
p2: 'fileno'=0x0
p3: 'filetype'=0x8
* time between wait #1 and #2: 0.000036 sec
2. event: 'SQL*Net message from client'
time waited: 6 min 54 sec
wait id: 1758 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000006 sec
3. event: 'SQL*Net message to client'
time waited: 0.000005 sec
wait id: 1757 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
If index is rebuild offline, then an error is directly thrown with ORA-00054 because the table need to be locked in SHARE MODE NOWAIT mode because of NOWAIT clause used internally.
kIsH@STAX<>alter index idxobj rebuild;
alter index idxobj rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
To summarize,
- Both offline and online index rebuild need to lock the table no matter the method used.
[oracle@staxhost ~]$ egrep 'LOCK TABLE' /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_30460_IR.trc
LOCK TABLE FOR INDEX "IDXOBJ" IN SHARE MODE NOWAIT
[oracle@staxhost ~]$ egrep 'LOCK TABLE' /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_30481_IRO.trc
LOCK TABLE FOR INDEX "IDXOBJ" IN ROW SHARE MODE
[oracle@staxhost ~]$
- Index Rebuild offline uses Index Fast Full scan and Index Rebuild online uses Full table scan
--Index Rebuild Offline
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD NON UNIQUE IDXOBJ (cr=293873 pr=651 pw=651 time=250883 us starts=1)(object id 0)
293833 293833 293833 SORT CREATE INDEX (cr=293845 pr=651 pw=0 time=140768 us starts=1)
293833 293833 293833 INDEX FAST FULL SCAN IDXOBJ (cr=293845 pr=651 pw=0 time=105728 us starts=1)(object id 76683)
--Index Rebuild Online
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD NON UNIQUE IDXOBJ (cr=5740 pr=0 pw=651 time=180561 us starts=1)(object id 0)
293849 293849 293849 SORT CREATE INDEX (cr=5693 pr=0 pw=0 time=76729 us starts=1)
293849 293849 293849 TABLE ACCESS FULL DBOBJ (cr=5693 pr=0 pw=0 time=14613 us starts=1 cost=1544 size=1101930 card=220386)
- Online rebuild is slow
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 2 0 0
Execute 2 0.18 0.25 652 293867 1040 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.18 0.25 652 293869 1040 0
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 3 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 5.07 9.29
PGA memory operation 130 0.00 0.00
index (re)build lock or pin object 4 0.00 0.00
asynch descriptor resize 1 0.00 0.00
db file scattered read 25 0.00 0.00
direct path write 166 0.00 0.06
reliable message 3 0.00 0.00
enq: RO - fast object reuse 1 0.00 0.00
db file sequential read 1 0.00 0.00
enq: CR - block range reuse ckpt 1 0.00 0.00
log file sync 1 0.00 0.00
Offline rebuild is fast
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 1 0 0
Execute 2 0.11 0.19 2 5739 1130 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.11 0.19 2 5740 1130 0
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 4.73 7.34
PGA memory operation 136 0.00 0.00
index (re)build lock or pin object 10 0.00 0.00
reliable message 5 0.00 0.00
direct path write 166 0.00 0.08
enq: RO - fast object reuse 2 0.00 0.00
db file sequential read 2 0.00 0.00
enq: CR - block range reuse ckpt 2 0.00 0.00
log file sync 1 0.00 0.00
- Do not schedule online rebuild activity during heavy DML hours of database since rebuild operation should wait for a long time until the DML commit.
- If good machine power is available like CPU and MEMORY , then go for parallel online index creation and also make sure that other critical DML operations are not impacted due to resource exhaustion. Use parallel_degree_limit to a less value say CPU / 3 if CPU_COUNT > 3 to limit the parallel or use resource manager plan. By default, parallel_degree_limit is set to CPU capacity which is not always good.
kIsH@STAX<>show parameter parallel_degree_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_limit string CPU
Since offline rebuild require downtime, this activity is too easy without any hassles except that if it is difficult to get business approval.