Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

CREATE INDEX ONLINE waiting on TX mode=4

Franck PachotNov 11 2015 — edited Nov 11 2015

Hi,

I have a strange locking issue where a CREATE INDEX ONLINE is waiting for a transaction that did only an inserted a row into the parent table.

Wait is on TX mode=4 (not TM locks) and I've currently reproduced it only on 11.2.0.3 and 11.2.0.4

Easy to reproduce on SCOTT schema:

Session 1:

SQL> set time on

14:54:58 SQL> insert into SCOTT.DEPT(deptno,dname) values (50,'test');

1 row created.

Session 2;

14:55:24 SQL> create index test on SCOTT.EMP (ename) ONLINE;

this is waiting on 'enq: TX - row lock contention':

SQL> select sid,chain_signature from v$wait_chains where blocker_is_valid='TRUE'

       SID CHAIN_SIGNATURE                                                      

---------- ----------------------------------------------------------------------

       603 'SQL*Net message from client'<='enq: TX - row lock contention'

waiting to acquire transaction lock in mode 4:

SQL> select * from gv$lock where sid=603

   INST_ID ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------- ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

         1 00000006CDA05EB8 00000006CDA05F10        603 AE       352577          0          4          0        348          2

         1 00000006CDA06078 00000006CDA060D0        603 DL       779505          0          3          0        308          2

         1 00000006CD9FABE0 00000006CD9FAC38        603 DL       779505          0          3          0        308          2

         1 00007F28001F5028 00007F28001F5088        603 TM       779505          0          2          0        308          2

         1 00007F28001F5028 00007F28001F5088        603 TM       779510          0          4          0        308          2

         1 00000006CD9FA240 00000006CD9FA298        603 OD       779505          0          4          0        308          2

         1 00000006B4F26790 00000006B4F26808        603 TX       589847     711499          6          0        308          2

         1 00000006CDA0ED68 00000006CDA0EDC0        603 TX       655377     996169          0          4        308          0

I've traced 10046 and 10704:

broadcast message=29231196672 obj#=779514 tim=1447251053634045

ksqgtl *** TX-000f001e-000ad97c mode=4 flags=0x10001 timeout=21474836 ***

ksqgtl: xcb=0x6bb322528, ktcdix=2147483647, topxcb=0x6bb322528

        ktcipt(topxcb)=0x0

ksucti: init session DID from txn DID:

ksqgtl:

        ksqlkdid: 0001-003F-0000AFC4

*** ksudidTrace: ksqgtl

        ktcmydid(): 0001-003F-0000AFC4

        ksusesdi:   0001-003F-0000AFC3

        ksusetxn:   0001-003F-0000AFC4

ksqcmi: TX,f001e,ad97c mode=4 timeout=21474836

*** 2015-11-11 15:11:36.566

WAIT #140700792578680: nam='enq: TX - row lock contention' ela= 42932082 name|mode=1415053316 usn<<16 | slot=983070 sequence=711036 obj#=779514 tim=1447251096566283

And there's nothing in V$SESSION current file/block/row. current obj# is EMP. current sql_id is the CREATE INDEX.

If anyone has an idea about:

- why creating an index online has to wait for transactions that modified only parent table

- how to investigate further

- can or cannot reproduce in other versions

Thanks in advance,

Franck.
This post has been answered by Jonathan Lewis on Nov 11 2015
Jump to Answer

Comments

L. Fernigrini

Consider using PL/Scope

PL/Scope Enhancements in Oracle Database 12c Release 2 (12.2) (0 Bytes)It is simpler than parsing code.

User_RI4C6

How its work I admit that I am a beginner in the world of plsql and I told myself that it is possible to do it with regexp

User_H3J7U

it can be for example standard package, ULT_HTTP
UTL_HTTP Constants

L. Fernigrini

undefined (0 Bytes)You need to set some session settings like this:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL, STATEMENTS:ALL';

Then compile the package, and then query the dictionary tables being populated when you compile PL/SQL and have set PLSCOPE settings.
In the article I mentioned there is an example :
image.pnghere you would see that variable L_NUM and L_STR are declared on lunes 6 and 7, and that they reference the NUMBER and CHARACTER datatypes. You can modify the query in the example to filter only "VARIABLE" as TYPE; "DECLARATION" as USAGE and then get the associated REFERENCE row to get the datatype.

1 person found this helpful
L. Fernigrini

Poster mentioned ANY package, documentation exists only for Oracle provided packages.

User_RI4C6

Wow very cool, Thank you very much for the help.!

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 9 2015
Added on Nov 11 2015
#general-database-discussions, #index
4 comments
2,607 views