Locking Behavior During Index Creation or Index Rebuild (Doc ID 70120.1)
PURPOSE
========
The objective of this article is to explain the locking behavior with
respect to creating and rebuilding indexes in Oracle versions 7.3 to 8.1.
It is also intended to explain the new functionality that is available in
Oracle 8i to resolve the problem of locking tables for extended periods
of time while an index is being created or rebuilt.
SCOPE & APPLICATION
===================
For users requiring locking information when creating or rebuilding indexes.
ORACLE 7.3 - 8.0 LOCKING BEHAVIOR
===================================
Traditionally, Oracle locks the referenced table in Share Mode (S) during
an index build (v$lock => type TM, lmode 4). This prevented any DML to
the table. In an attempt to reduce the time that a table is not accessible
for DML operations, Oracle introduced the "fast rebuild" option. The
recommendation is to schedule recreation of indexes and "fast rebuilds" when
the users are not actively using the system.
Locks
~~~~~
A DDL lock protects the definition of a schema object while that object
is acted upon or referred to by an ongoing DDL operation. Most DDL
operations require exclusive DDL locks to prevent destructive interference
with other DDL operations that might modify or reference the same schema
object.
The DDL operations that require a shared DDL lock are the following
commands:
> CREATE [OR REPLACE] VIEW / PROCEDURE / PACKAGE
> CREATE [OR REPLACE] PACKAGE BODY / FUNCTION / TRIGGER
> AUDIT > NOAUDIT >COMMENT
> CREATE SYNONYM > CREATE TABLE ( with CLUSTER parameter
not included )
DDL operations also acquire DML locks (data locks) on the schema object
to be modified.
CREATE INDEX and ALTER INDEX acquire exclusive DDL Locks on the table the
INDEX is referencing. Exclusive and share DDL Locks last for the duration
of DDL statement execution and automatic commit. During the time the DDL
lock is held, NO DML is allowed on the table.
Syntax
~~~~~~
SQL> CREATE INDEX TESTREBUILD ON EMP(ENAME,JOB);
SQL> ALTER INDEX TESTREBUILD REBUILD;
ORACLE 8.1 (8i) - LOCKING BEHAVIOR WITH OPTION
=========================================================
In Oracle 8i (8.1.x) we can specify that we want DML operations on the table to
be allowed during creation of the index with the ONLINE option.
Restriction: Parallel DML is not supported during ONLINE index building.
If you specify ONLINE and then issue parallel DML statements,
Oracle returns an error.
For very large tables it can take a substantial amount of time to create or
rebuild an index and the table remains offline for any DML. Offline index
creation was the only option prior to Oracle version 8.1. DML access is now
possible on the underlying table of the index build. New syntax has been
added to allow this functionality, Oracle does recommend that very heavy DML
against a table be avoided when creating online indexes.
Locks
~~~~~
With Oracle 8.1, Oracle will hold a Row Share Table Lock (RS) on the table
for the duration of the index build if the key word ONLINE is used. Users
will be able to continue to access the table as normal except for any DDL
operations.
***FYI:
===
Documentation BUG 1394033 in 8.1.6
The 8.1.6 Database Administrator's Guide indicates
that creating an index ONLINE allows others to issue DML on the table.
However, the create index still waits for the lock.
FIX:
===
The Oracle9i Database Administrator's Guide has been changed to reflect
that the DDL for the index build cannot proceed while the base table
is being updated. It must wait until the lock on the base
table is released.
How Oracle Ensures Consistency
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When the ONLINE keyword is specified as a part of an alter index or
create index command a temporary journal table to record changes made to
the base table is created. The journal is an IOT (Index Organized Table)
table type.
A Row Share Table Lock(RS) is held on the table during the index creation.
The index creation process so as to ensure read consistency and avoid any
ORA-1555s adopts a modified read algorithm.
Oracle merges the changes entered in the journal at the end of the index
build process. This merge by which changed rows are incorporated into the
new index, is done while the table is still online.
This is accomplished by scanning the journal table and operating on a per
row basis. Operations are committed every 20 rows. Locked rows are
skipped. Oracle may make multiple passes over the journal table to
process previously locked rows.
Syntax
~~~~~~
SQL> CREATE INDEX TESTONLINE ON EMP(ENAME,JOB) ONLINE;
SQL> ALTER INDEX TESTONLINE REBUILD ONLINE;
SUMMARY
========
7.3 - 8.0 CREATE INDEX and ALTER INDEX REBUILD will lock the table with
a Share Table Lock (S). No DML operations are permitted on the
base table.
8.1 If the ONLINE key word is included as part of the CREATE INDEX or
ALTER INDEX....REBUILD commands, the table is locked with a Row
Share Table Lock (RS). Users will be able to continue to access
the table as normal except for any DDL operations. DML operations
are permitted.
Index Rebuild Is Hanging Or Taking Too Long (Doc ID 272762.1)
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.
==================================================================
Verifying existence of duplicate rows using script in 332494.1
The next script (duplicated_rows.sql) will help you to detect the duplicated rows:
REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/
Example:
create table dup(a numeric, b numeric , c numeric);
insert into dup values (1,1,2); -- <( Duplicated row
insert into dup values (5,6,7);
insert into dup values (2,1,2);
insert into dup values (2,9,2);
insert into dup values (1,1,2); -- <( Duplicated row
commit;
@duplicated_rows.sql
Enter name of table with duplicate rows
Table: dup
Table DUP
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B NUMBER(38)
C NUMBER(38)
Enter name(s) of column(s) which should be unique. If more than
one column is specified , you MUST separate with commas.
Column(s): a,b
1 1
NOTE:
Depending on the circumstances, you may have to delete the duplicate rows and proceed rebuilding the index or drop and recreate the index (or another).
Comments
Post a Comment
Oracle DBA Information