Global Enqueue Services Deadlock detected - issue

[sivakumarocp]

Hi Tim,

We are getting "Global Enqueue Services Deadlock detected. More info in file" on one of our 3 node RAC running on 10.2.0.4
upon checking the corresponding trace file found below sql statement causing the problem.

Code: Select all

INSERT INTO FABTRKG.FAB_LOT_ATTRIBUTE
(lot_id,corr_item_no,corr_item_seq_no,corr_item_desc,user_id,lot_attr_value,updated_datetime)
VALUES
(:lot_id, :corr_item_no, :corr_item_seq_no, :corr_item_desc, :user_id, :lot_attr_value, :updated_datetime)


When I googled for this error, found this bug is due to below two reasons,

1. No index on foregin key which is refering to primary key on FABTRKG.FAB_LOT_ATTRIBUTE table.
2. Bitmap indxes.

When I check, FABTRKG.FAB_LOT_ATTRIBUTE table doesnot have any foreign key created on it.

Code: Select all

SQL> SELECT a.table_name, c.column_name, b.table_name AS CHILD_TABLE, d.column_name, b.R_CONSTRAINT_NAME
  2  FROM dba_constraints a, dba_constraints b, dba_ind_columns c, dba_cons_columns d
  3  WHERE a.constraint_type = 'P' AND a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME AND b.CONSTRAINT_TYPE = 'R' AND a.table_name = c.table_name AND a.constraint_name = c.index_name AND b.CONSTRAINT_NAME = d.constraint_name AND a.table_name = 'FAB_LOT_ATTRIBUTE' and a.owner='FABTRKG';

no rows selected


But it has one bitmap indexes created on CORR_ITEM_DESC column

Code: Select all

SQL>SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='FAB_LOT_ATTRIBUTE' AND OWNER='FABTRKG';

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
FAB_LOT_ATTRIBUTE_IDX1         NORMAL                      VALID
FAB_LOT_ATTRIBUTE_PK           NORMAL                      VALID
FAB_LOT_ATTRIBUTE_BIDX1        BITMAP                      VALID

SQL> SELECT INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='FAB_LOT_ATTRIBUTE' AND TABLE_OWNER='FABTRKG';

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
FAB_LOT_ATTRIBUTE_IDX1         LOT_ID
FAB_LOT_ATTRIBUTE_IDX1         CORR_ITEM_DESC
FAB_LOT_ATTRIBUTE_IDX1         LOT_ATTR_VALUE
FAB_LOT_ATTRIBUTE_IDX1         UPDATED_DATETIME
FAB_LOT_ATTRIBUTE_PK           LOT_ID
FAB_LOT_ATTRIBUTE_PK           CORR_ITEM_NO
FAB_LOT_ATTRIBUTE_PK           CORR_ITEM_SEQ_NO
FAB_LOT_ATTRIBUTE_BIDX1        CORR_ITEM_DESC

8 rows selected.


Below are the cound and key count of FABTRKG.FAB_LOT_ATTRIBUTE table.

Code: Select all

SQL> select count(*) from fabtrkg.fab_lot_attribute;

  COUNT(*)
----------
  23275006

SQL> select count(distinct CORR_ITEM_DESC) from fabtrkg.fab_lot_attribute;

COUNT(DISTINCTCORR_ITEM_DESC)
-----------------------------
                        11930


beased on above key value I understood that it has correct cardinality value (0.05%)

What I understood sofar about the bitmap indexes is, DML operations against bitmap indexed column are expensive and it should have less cardinality values (i.e distinct keys)
here cardinality value is low but DML operations are happening more on bitmap indexed column (CORR_ITEM_DESC).

My question is, if more DML operation happend on bitmap indexed column then will it cause Deadlock?

Can I give suggestion to Application team to remove this bitmap index from this table? but still i couldn't come for the conclusion on this issue.

[Tim...]

Hi.

The issue about bitmap indexes only being useful for low cardinality columns is a myth. They care actually pretty useful for variety of situations. Richard Foote does a neat presentation where he makes the case that bitmap indexes should be the only type of indexes used in a data wharehouse.

The issue about bitmap indexes being bad for tables with large amounts of DML is true. They are not designed to be used on high transactions tables.

Cheers

Tim...

[sivakumarocp]

Hi Tim,

Today also we got the same alert but its for DELETE statement.

Code: Select all

DELETE FROM FABTRKG.FAB_LOT_ATTRIBUTE
WHERE (LOT_ID = :LOT_ID AND CORR_ITEM_NO = :CORR_ITEM_NO AND CORR_ITEM_SEQ_NO = :CORR_ITEM_SEQ_NO)


In this statement doesn't have bitmap indexed column (CORR_ITEM_DESC) but eventhen we got the GLobal enque deadlock alert. in this case suggesting application team to remove Bitmap index is worth?

[Tim...]

Hi.

This is the same table as before, so there is a bitmap index on the table. Even though the column is not referenced in the DML, the removal of a row requires the index to be updated. Remember, all inserts and deletes will require the index to be amended. Updates that affect the indexed row will require the index to be amended.

Cheers

Tim...

[sivakumarocp]

Hi Tim,

So removing Bitmap index from FABTRKG.FAB_LOT_ATTRIBUTE table will resolve this deadlock issue?

[Tim...]

Hi.

I can't guarantee that, but having a bitmap index on table with high volumes of DML is a bad idea, so you should do it anyway. There are locks associated with bitmap indexes, so it is likely it will fix the issue.

Cheers

Tim...

[sivakumarocp]

Hi Tim ,

For this Deadlock issue we opened SR with oracle and they suggested to increase INI_TRANS settings for both table and indexes and we increased to 25 but eventhen we are getting same issue again!

we are hitting the deadlock while below query runs.

Code: Select all

DELETE
INSERT INTO FABTRKG.FAB_LOT_ATTRIBUTE
(lot_id,corr_item_no,corr_item_seq_no,corr_item_desc,user_id,lot_attr_value,updated_datetime)
VALUES (:lot_id, :corr_item_no, :corr_item_seq_no, :corr_item_desc, :user_id, :lot_attr_value, :updated_datetime)

DELETE FROM FABTRKG.FAB_LOT_ATTRIBUTE
WHERE (LOT_ID = :LOT_ID AND CORR_ITEM_NO = :CORR_ITEM_NO AND CORR_ITEM_SEQ_NO = :CORR_ITEM_SEQ_NO)


Code: Select all

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
FAB_LOT_ATTRIBUTE_IDX1         NORMAL                      VALID
FAB_LOT_ATTRIBUTE_PK           NORMAL                      VALID
FAB_LOT_ATTRIBUTE_BIDX1        BITMAP                      VALID

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
FAB_LOT_ATTRIBUTE_IDX1         LOT_ID
FAB_LOT_ATTRIBUTE_IDX1         CORR_ITEM_DESC
FAB_LOT_ATTRIBUTE_IDX1         LOT_ATTR_VALUE
FAB_LOT_ATTRIBUTE_IDX1         UPDATED_DATETIME
FAB_LOT_ATTRIBUTE_PK           LOT_ID
FAB_LOT_ATTRIBUTE_PK           CORR_ITEM_NO
FAB_LOT_ATTRIBUTE_PK           CORR_ITEM_SEQ_NO
FAB_LOT_ATTRIBUTE_BIDX1        CORR_ITEM_DESC


Bitmap index has been created on CORR_ITEM_DESC column but above delete statement does not have this bitmap indexed column (CORR_ITEM_DESC) but I am not sure why we are getting Deadlock issue while above DELETE statement runs!

2. If really bitmap index is the problem, I can suggest application team to remove bitmap index but If i remove this index how do I find what kind of index we can create on this column? (my database runing on 10.2.0.4)

3. Is there any query to find Foreign key which is refrencing to a table.

[Tim...]

Hi.

1) The bitmap index is not being referenced by the delete, but the rows it indexes are still being deleted, so it is still having to update the index bitmap to take account of the rows that have been deleted. Using bitmap indexes on systems running DML is typically considered really bad. They are better for static data, snot volatile data. So data warehouses yes. OLTP no.

1a) Deleting data is really not a great idea if the proportion of data is large. Better to drop a partition, or create a new table with out the data and swap the table names.

2) If this is a warehouse and the data is typically static, you could drop the index, delete the data and recreate the index. If this is an OLTP system, or a warehouse that has lots of DML, then you should remove the bitmap index and consider building a regular b*tree index, which will cope fine with DML.

3) See:

http://www.oracle-base.com/dba/script.p ... olumns.sql
http://www.oracle-base.com/dba/script.p ... le=fks.sql

Cheers

Tim...