Truncate table command running slow with DFS Lock Handle in RAC (Doc ID 2168689.1)

Last updated on AUGUST 14, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

In 11.2.0.4.8 database, there is a huge performance issue while truncating tables and mostly (99%) waiting on "DFS lock handle"

AWR report ==>

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DFS lock handle 1,354 841.8 622 39.3 Other
DB CPU 661 30.8
SQL*Net more data to client 62,087 319.4 5 14.9 Network
rdbms ipc reply 195 301.4 1546 14.1 Other
log file sequential read 86,428 244.9 3 11.4 System I/O
control file sequential read 183,280 65.3 0 3.0 System I/O
local write wait 20,962 32.7 2 1.5 User I/O

 

 

 DFS lock handle is blocked by CKPT process ==>

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (xxxxx)
os id: 7892
process id: 52, oracle@node1
session id: 1977
session serial #: 3217
}
is waiting for 'DFS lock handle' with wait info:
{
p1: 'type|mode'=0x43490005
p2: 'id1'=0x12
p3: 'id2'=0x2
time in wait: 0.440500 sec
timeout after: never
wait id: 917
blocking: 0 sessions
wait history:
* time between current wait and wait #1: 0.000089 sec
1. event: 'DFS lock handle'
time waited: 0.000181 sec
wait id: 916 p1: 'type|mode'=0x43490005
p2: 'id1'=0x12
p3: 'id2'=0x3
* time between wait #1 and #2: 0.000091 sec
2. event: 'DFS lock handle'
time waited: 0.000279 sec
wait id: 915 p1: 'type|mode'=0x43490005
p2: 'id1'=0x12
p3: 'id2'=0x1
* time between wait #2 and #3: 0.000269 sec
3. event: 'db file sequential read'
time waited: 0.000244 sec
wait id: 914 p1: 'file#'=0xb1
p2: 'block#'=0x1810a
p3: 'blocks'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (lpcrtaa.lpcrtaa1)
os id: 23609

process id: 44, oracle@node1 (CKPT)
session id: 1673
session serial #: 1
}
which is not in a wait:
{
last wait: 0.091421 sec ago
blocking: 1 session
wait history:
1. event: 'control file parallel write'
time waited: 0.000106 sec
wait id: 777347 p1: 'files'=0x2
p2: 'block#'=0x3
p3: 'requests'=0x2
* time between wait #1 and #2: 0.001475 sec
2. event: 'control file sequential read'
time waited: 0.000185 sec
wait id: 777346 p1: 'file#'=0x0
p2: 'block#'=0x14
p3: 'blocks'=0x1
* time between wait #2 and #3: 0.000026 sec
3. event: 'control file sequential read'
time waited: 0.000210 sec
wait id: 777345 p1: 'file#'=0x0
p2: 'block#'=0x11
p3: 'blocks'=0x1
}

 

Changes

The customer upgrade the database to 11.2.0.4 from lower 11.2

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