When a blocking txn id for DDL wait event occurs on Oracle 11gR2, 11gr2txn

When a blocking txn id for DDL wait event occurs on Oracle 11gR2, 11gr2txn

This article is the original article, reproduced please indicate the source: http://blog.csdn.net/msdnchina/article/details/44726875

 

In a recent case (oracle db of version 11.2.0.4), we encountered the blocking txn id for DDL wait event.

The current operation is as follows:

create index idx_tab_1 on table_name (column_name)   tablespace xxx  online;

It takes 17 minutes and seven seconds to create an index. Then I generate an awr report for this period of time. The top 10 waiting events are intercepted as follows:

The wait time of blocking txn id for DDL is 1023.1 seconds, 17 minutes and 7 seconds = 1027 seconds. That is to say, the vast majority of the time of the create index statement was waiting.

 

Later, an ash report for this time period was generated. In the ash report, we can also clearly see that the wait event for the create index operation is blocking txn id for DDL, as shown below:

 

 

In the mos Article Alter Table Add Column Command Hangs With Wait Event 'blocking txn id for ddls' (Document ID 1553725.1), it is clear that the waiting Event:

The original article is as follows:

DDL commands require exclusive locks on internal structures. If these locks are not available, the commands return with an "ORA-00054: resource busy" error message.On Oracle 11.2 instead of ora-54, the ALTER TABLE statement will not error out immediately. Instead, it waits indefinitely.Prior to 11g, if these locks are not available, the DDL command will fail with an "ORA-00054: resource busy" error message.

Translation:

DDL commands require exclusive locks in the internal structure. If these locks are not available, the DDL command fails with the "ORA-00054: resource busy" error message returned.

In Oracle 11.2, instead of ora-54, the alter table statement does not return an error immediately, but waits indefinitely.
If these locks cannot be obtained before 11g, the DDL command fails and returns the "ORA-00054: resource busy" error message.