My Oracle Support Banner

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.

Symptoms

Performance problem is seen when using LOBs during one of the following operations:

 Create LOB and then INSERT data into the LOB

  - or -
 
 UPDATE LOB

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


First we determined what load was including the following simple assessment based Cost analysis of Time:
During this period of time your USER IO is 14 times higher than SYS_TIME which would include HWM overhead



Changes

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

Cause

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
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.