【MOS】Index Rebuild Is Hanging Or Taking Too Long (文件 ID 272762.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 9.2.0.4 [Release 8.1.7 to 9.2]
Information in this document applies to any platform.

SYMPTOMS

 .

CHANGES

 .

CAUSE

 .

SOLUTION

(AuthWiz 1.2) Created from <<TAR:3405564.995>>

APPLIES TO

Oracle Server - Enterprise Edition - Version: 9.2.0.4
This problem can occur on any platform.

SYMPTOMS

Problem:
========
- Online Index rebuild takes a long time.
- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

Symptoms:
=========
Performance issues while rebuilding very large indexes.
- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.
- Issuing index rebuild ONLINE statement => finishes in about an hour.
- This behavior of ONLINE index rebuilds makes it a non-option for large tables 
as it just takes too long to scan the table to rebuild the index. The 
offline may not be feasible due to due to the 24/7 nature of the database.
- This may be a loss of functionality for such situations.
- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter
hanging behavior indefinitely (or more than 6 hours). 

DIAGNOSTIC ANALYSIS:
--------------------
We can trace the sessions rebuilding the indexes with 10046 level 12.
Comparing the IO reads for the index-rebuild and the index-rebuild-online 
reveals the following:

-ONLINE index rebuilds
It scans the base table and it doesn't scan the blocks of the index.

-OFFLINE index rebuilds
It scans the index for the build operation.

- This behaviour is across all versions.

TEST CASE:
----------
--connect as scott
--
sqlplus scott/tiger
--
--create some dummy table from the dba_objects view
--
create table objects as select * from dba_objects;
--
--create an index on the table
create index object_idx on objects(object_id,object_name);
--
--check out the file_id and block_id for the table and index
--
set linesize 150
set pagesize 4444
col segment_name format a40
select segment_name,file_id,block_id from dba_extents where owner = 'SCOTT' 
and segment_name like 'OBJECT%';
--
--trace the ONLINE index rebuild first
--
alter session set events '10046 trace name context forever, level 12';
alter index object_idx rebuild online;
--
--exit here and pull up the trace file. You'll see the reads
--for the cursor representing the cursor performing the rebuild that they
--are scanning the file and blocks belonging to the --base table
--called OBJECTS
--
exit
--
--Log back in and retry the same with the OFFLINE index rebuild
--
sqlplus scott/tiger
--
--get the new block_ids because the index has been rebuilt since we did this 
--last time
--
set linesize 150
set pagesize 4444
col segment_name format a40
select segment_name,file_id,block_id from dba_extents where owner = 'SCOTT' 
and segment_name like 'OBJECT%';
--
--trace and rebuild the index
--
alter session set events '10046 trace name context forever, level 12';
alter index object_idx rebuild;
--
--exit out and check the trace file again. You'll see that we read the index 
--blocks for the rebuild

On analyzing the trace file generated, we will notice that there are lots of
'db file scattered read' wait events.

CAUSE

Cause/Explanation
=============
When you rebuild index online,
- it will do a full tablescan on the base table.
- At the same time it will maintain a journal table for DML data, which has 
changed during this index rebuilding operation. 
So it should take longer time, specially if you do lots of DML on the same table,
while rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle will grab
the index in X-mode and rebuild a new index segment by selecting the data from 
the old index. So here we are
- not allowing any DML on the table hence there is no journal table involved 
- and it is doing an index scan
Hence it will be pretty fast.

FIX

Solution/Conclusion:
===========
- The ONLINE index rebuild reads the base table, and this is by design.
- Rebuilding index ONLINE is pretty slow.
- Rebuilding index offline is very fast, but it prevents any DML on the base table.

REFERENCES

 - Online Index Rebuild Scans The Base Table And Not The Index

---

REFERENCES

 - ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

.......................................................................................................................................................................................................................................................................................................... ● 本文來自於MOS轉載文章，(文件 ID  272762.1) ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● QQ群：230161599  微信群：私聊 ● 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/ ● 小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● QQ群： 230161599   微信群：私聊 ● 聯絡我請加QQ好友(642808185)，註明新增緣由 ● 版權所有，歡迎分享本文，轉載請保留出處 .......................................................................................................................................................................................................................................................................................................... 手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的資料庫技術。