High Buffer busy waits during log switches
Last updated on SEPTEMBER 18, 2016
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.5 to 22.214.171.124 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 17-Apr-2015
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.
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.
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