Insert Into a LOB has poor performance; DIRECTIO READs in Top 5 Wait Events (Doc ID 1291494.1)

Last updated on JUNE 12, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 12-Jun-2016***

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


SYS_TIME             30,105
USER_TIME           444,698   << this is where the time is spent


So we have confirmed that the USER application/query is the major CPU Time consumer

Comment: The fact that there is some HWM contention is expected due to the operations performed
- However, HWM contention showed as a small overhead when compared to the DIRECT Read operations as seen in the AWR Top 5 Waits section

Example:


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
                                                 Avg   %Total
                                                wait    Call
Event                         Waits     Time(s) (ms)    Time   Wait Class
------------------------------------------ ----------- ------  ------
CPU time                                836             54.5
gc current block busy        4,094      144      35      9.4   Cluster
RMAN backup & recovery I/O  14,853      139       9      9.0   System I/O
direct path read           106,702      117       1      7.6   User I/O
gc current block 3-way      12,382       52       4      3.4   Cluster


A Second AWR did not show any HWM enqueue contention either:
-------------------------------------------------------------------------------

RAC Statistics DB/Inst: abc
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
                                            Avg    %Total
                                           wait     Call
Event                   Waits    Time(s)   (ms)     Time  Wait Class
------------------------------ ------------ ----------- ------ ------
CPU time                 1,123                     59.2
direct path read       388,889     448       1     23.6   User I/O <-------
gc current block busy    3,608     149      41      7.9   Cluster
log file parallel write 29,667      40       1      2.1   System I/O
gc current block 3-way   8,836      32       4      1.7   Cluster
-------------------------------------------------------------



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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms