High Buffer busy waits during log switches

(Doc ID 1577905.1)

Last updated on FEBRUARY 18, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 17-Apr-2015

Symptoms

When the log switches happen the instance experiences High buffer busy waits for DML transactions. 

AWR report shows the sudden Buffer Busy waits spiking up
Top 5 Timed Foreground Events
Event                           Waits            Time(s)                         Avg wait (ms)          % DB time  Wait Class
db file sequential read  1,214,478       4,843                                 4                      37.27       User I/O
DB CPU                       2,581                                                                              19.87 
buffer busy waits          598                1,552                                 2596                 11.95       Concurrency
direct path read temp   98,197            651                                    7                      5.01         User I/O
log file sync                 26,889            147                                    5                      1.13         Commit

 

ASH Reports shows High Buffer Busy waits on DML statements.
Top SQL with Top Events
SQL ID             % Event Top                                   Row Source                                          % RwSrc                 SQL Text 
zzzzzzzzzzzz    buffer busy waits 3.13               ** Row Source Not Available **               3.13                       INSERT INTO TABLE_xxxxx (O... 
zzzzzzzzzzzz    enq: TX - index contention 1.01 ** Row Source Not Available **               1.01                       INSERT INTO xxxxxxx (O... 
wwwwwwww   buffer busy waits 2.64              ** Row Source Not Available **               2.64                       INSERT INTO TABLE_wwwwww(O...

 

The manual log switches "alter system archive log current" or implicit log switch can cause this high buffer busy waits spike.

 

Changes

The instance uses a product called 'SafeNet Luna' for their HSM which is a key store for external TDE module for the tablespace encryption.  

Safenet is configured for HA, which means that one of the 'Safenet Servers' is actually at a remote WAN-Based location - so depending on availability of the local Safenet server, a request may be redirected to go over the WAN.

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