hi Friends,
3 application update queries on the same table ran into buffer busy waits (with P3 bitmap index block and bitmap block) and enq: TX - row lock contention,last night. This problem wasnt seen earlier anytime. No other issues were seen in the DB , CPU utilisation was <30% on the host. DB version:11.2.0.4.0
The application table and the LOBS have freelist=1.The current_obj# for the buffer busy waits is the LOB segment which is getting updated.
Question is:
1. What does bitmap index block & bitmap block mean?
2. Will the freelist increasing solve the problem from appearing anytime in future. We have upto 10 concurrent updates on this table. Or the root cause can be something else?
ASH:
1st sql:
UPDATE TABLE_1 SET LOB_COL1=:1 , BN=:2 ,
UPDATE_DATE=sysdate WHERE customer_id=:3
2nd sql:
UPDATE TABLE_1 SET LOB_COL2=:1 , BN=:2 ,
UPDATE_DATE=sysdate WHERE customer_id=:3
3rd sql:
UPDATE TABLE_1 SET LOB_COL3=:1 , BN=:2 ,
UPDATE_DATE=sysdate WHERE customer_id=:3
4th sql
From ASH: p3 = 12,11 for buffer busy waits
from AWR:
From Diagnosing buffer busy waits with the ash_wait_chains.sql script (v0.2) | Tanel Poder's Performance & Troubleshooting bl… script i got the below output during that time for the top sql:
