Support Article
DB Contention when using large object in the DML
SA-79287
Summary
When using Larget objects in DMLs, database contention occurs in the applicatoin and the system does not respond.
Error Messages
[WebContainer : 26] [ ] [ ] [ ] (web.impl.WebStandardImpl) ERROR - : Problem retrieving input data:
java.io.IOException: Async IO operation failed (3), reason: RC: 107 Transport endpoint is not connected
at com.ibm.io.async.AsyncLibrary$IOExceptionCache.<init>(AsyncLibrary.java:924)
at com.ibm.io.async.AsyncLibrary$IOExceptionCache.get(AsyncLibrary.java:937)
at com.ibm.io.async.AsyncLibrary.getIOException(AsyncLibrary.java:951)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:705)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
Steps to Reproduce
- Add multiple work tables and one Assign_worklist table.
- Have a large number users (3K concurrent) accessing the assignments from worklist . Contention occurs and the system does not respond
Root Cause
A defect or configuration issue in the operating environment Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation (Doc ID 1532311.1).
Resolution
Perform the following local-change:
- Log in to the database as a sysdba.
- Run the following query:
alter system set "_securefiles_concurrency_estimate"=50 scope=spfile;
- Restart the database instance and application server for the changes to take effect. By increasing this parameter, less concurrency contention is expected on the securefiles.
- Recreate any table that contains securefiles especially the contended table (recreating the contended table is necessary since the hidden parameter '_securefiles_concurrency_estimate' is picked up for securefiles setting only during creation'.
- To reduce the number of concurrent requests, use a Composite Range Hash partition to partition the table affected by TX - contention.
Published May 11, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.