Database Reference
In-Depth Information
■
hWM is increased five blocks at a time. Undocumented parameter _bump_highwater_mark controls the
number of blocks to increase. there is no reason to modify this parameter for normal workload, though.
Note
Since HW enqueues are also globalized in a RAC environment, HW enqueue contention can be magnified.
Generally, this problem is not prevalent in ASSM tablespaces. If you encounter HW enqueue contention in an ASSM
tablespace, then that would generally indicate that the object growth rate is higher than the speed of ASSM metadata
manipulation. You might have to either preallocate the extents or partition the object to reduce the severity of HW
enqueue contention.
There are a few common reasons for HW contention:
1.
Heavy insert activity in to a non-partitioned table: Numerous processes insert in one
segment and that segment grows by leaps and bounds, leading to contention issues with
HW enqueues. Partitioning the table is a better practice, so that pressure on HW enqueue
is distributed across many segments.
2.
Indexes splitting and growing faster: Determine if indexes are needed during insert
activity. If so, hash partitioning indexes might be helpful.
3.
Prior to Oracle Database 10g, LOB column segments were extended one block at a time.
This bug has been fixed in Oracle Database version 11g, and so upgrading to the latest
version is useful to resolve the problem if the segment is a LOB segment.
DFS Lock Handle
DFS stands for Distributed File System, an older name from Oracle Parallel Server (predecessor to Oracle RAC)
versions. However, in RAC, this wait event is overused in various contexts. For example, DFS lock handle mechanism
is also used to trigger a predefined action, such as global checkpoint, in an instance background process (both remote
and local instances).
Consider a DDL statement truncating a table T1. Table blocks of T1 can be resident in the buffer cache of any
instance. So, before the truncate statement is successful, buffers belonging to that table must be invalidated
4
from all
instances. This invalidation would require a message to be sent to the remote DBW process, and that communication
mechanism is implemented using DFS lock handle mechanism. Essentially, requesting a lock handle on a lock type
will trigger predefined action in the background process indirectly. While the background process is working to
complete the triggered action, foreground process is instrumented to wait on DFS lock handle wait event. The exact
sequence of messaging between the background processes is version dependent and so, we will see only practical
methods to debug “DFS lock handle” waits.
A resource and lock lurks underneath DFS lock handle mechanism. To understand the root cause of DFS lock
handle waits, you need to identify the resource type and mode requested. In Listing 11-7, a line from a SQL trace file
is shows a wait time of 4.6 ms for
DFS lock handle
wait event. Attribute p1 of the event is set to 1398145029. That
innocuous-looking number can be used to understand more details. Hexadecimal representation of 1398145029 is
0x53560005. The first part of the string 5356 represents the lock type in ASCII format. In this example, the value of 53
maps to ASCII character S, and the value of 56 maps to ASCII character V; therefore, 5356 represents lock type SV. Lock
mode is 0005, which is KJUSERPW mode. Listing 11-7 also provides a SQL statement to convert the value of p1 from
decimal to lock type and mode.
4
Exact terminology is
Checkpoint Range and Invalidate
.
