Insert Into a LOB has poor performance; DIRECTIO READs in Top 5 Wait Events
(Doc ID 1291494.1)
Last updated on APRIL 06, 2022
Applies to:Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Performance problem is seen when using LOBs during one of the following operations:
Create LOB and then INSERT data into the LOB
- or -
Reviewing the AWR many seem to support that the problem is due to HWM Enqueue Contention:
- This is actually the direct result of the update LOB Index and Table operations if extent allocation or walking the LOB index is required (leading to an eventual update)
What is often overlooked however, is heavy User activity including DIRECT READs as a more significant statistic
Here is an example from a SR created originally as LOB HWM enqueue contention
During this period of time your USER IO is 14 times higher than SYS_TIME which would include HWM overhead
None known, however, this is most likely to be relevant if you have a high load and either LOB create and inserts or updates are occurring
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document