Currently Being Moderated

The theory

You can peform DML statements while you create or rebuild an index object online, but in the beginning and in the end the table is locked for a very short moment.
If you have a still ongoing DML operation which is not commited or rolled back you can run into a lock situation.

 

 

The documentation

Quote from the SQL documentation:
"Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index"

 


Quote from the Administrator Guide:
"While you can perform DML operations during an online index build, Oracle recommends that you do not perform major/large DML operations during this procedure. This is because while the DML on the base table is taking place it holds a lock on that resource. The DDL to build the index cannot proceed until the transaction acting on the base table commits or rolls back, thus releasing the lock."

The proof


This example table locks while an CREATE INDEX ONLINE are created on a 6.40 system with Oracle 10.2.0.2.0 on SLES 10.

 

1) The test table BALDAT

 

desc SAPDAT.BALDAT;
Name                           Null     Type
------------------------------ -------- ----------------
MANDANT                        NOT NULL VARCHAR2(9)
RELID                          NOT NULL VARCHAR2(6)
LOG_HANDLE                     NOT NULL VARCHAR2(66)
BLOCK                          NOT NULL VARCHAR2(18)
SRTF2                          NOT NULL NUMBER(10)
CLUSTR                         NOT NULL NUMBER(5)
CLUSTD                                  LONG RAW()

 



2) Create a lock situation with oracle default setting

 

Session 1:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
82

 

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('001');
1 row created.

 

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (68,69,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
82                     TM   17414                  0                      3                      0                      0
82                     TX   262158                 4225                   6                      0                      0

 


Session 2:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
69

 

SQL> CREATE INDEX SAPDAT.BALDAT_TEST ON SAPDAT.BALDAT(MANDANT) ONLINE;

 

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (68,69,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
69                     TM   51437                  0                      4                      0                      0
69                     DL   17414                  0                      3                      0                      0
69                     DL   17414                  0                      3                      0                      0
69                     TM   17414                  0                      2                      4                      0
82                     TX   262158                 4225                   6                      0                      0
82                     TM   17414                  0                      3                      0                      1

 


Session 3:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
68

 

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('002');

 

Session 3 is hanging now!

 

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (68,69,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
68                     TM   17414                  0                      0                      3                      0                     
69                     DL   17414                  0                      3                      0                      0                     
69                     DL   17414                  0                      3                      0                      0                     
69                     TM   17414                  0                      2                      4                      0                     
69                     TM   51437                  0                      4                      0                      0                     
82                     TM   17414                  0                      3                      0                      1                     
82                     TX   262158                 4225                   6                      0                      0                     

 

 

 


3) Create a lock situation with event "10629 trace name context forever, level <n>"

 

Session 1:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
70

 

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('001');
1 row created.

 

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (69,70,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
70                     TX   524313                 4218                   6                      0                      0
70                     TM   17414                  0                      3                      0                      0

 


Session 2:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
82

 

SQL> CREATE INDEX SAPDAT.BALDAT_TEST ON SAPDAT.BALDAT(MANDANT) ONLINE;

 

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (69,70,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
70                     TM   17414                  0                      3                      0                      0                     
70                     TX   524313                 4218                   6                      0                      0                     
82                     TM   51447                  0                      4                      0                      0                     
82                     TM   17414                  0                      2                      0                      0                     
82                     DL   17414                  0                      3                      0                      0                     
82                     DL   17414                  0                      3                      0                      0

 

Session 3:
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
SID
----------
69

 

SQL> INSERT INTO SAPDAT.BALDAT(MANDANT) VALUES ('002');
1 row created.

 

SQL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK from V$LOCK where SID IN (69,70,82) ORDER BY SID;
SID                    TYPE ID1                    ID2                    LMODE                  REQUEST                BLOCK
---------------------- ---- ---------------------- ---------------------- ---------------------- ---------------------- ------
69                     TX   131076                 4054                   6                      0                      0
69                     TM   17414                  0                      3                      0                      0
70                     TM   17414                  0                      3                      0                      0
70                     TX   524313                 4218                   6                      0                      0
82                     TM   51447                  0                      4                      0                      0
82                     DL   17414                  0                      3                      0                      0
82                     DL   17414                  0                      3                      0                      0
82                     TM   17414                  0                      2                      0                      0

 

Session 3 is not hanging and you can still perform DML on the table.

Sapnote #869521 describes the oracle event 10629.

References

Comments

Actions

Filter Blog

By author:
By date:
By tag: