AWR TOP 5 Timed Events - DFS lock handle

DFS lock handle

The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.

Oracle is waiting for the lock handle. The lock handle identifies a lock that is currently held. This lock handle is obtained from the lock state on the initial acquisition and may be used to reconstruct the state for conversion or release. This event is called if we are getting a lock and we want to know the lock handle (to identify the lock in future operations like conversions or release). The lock is maintained by the DLM.

Wait Time:
The session waits in a loop until it has obtained the lock handle from the DLM. Inside the loop there is a wait of 0.5 seconds.

Parameter Description
name Lock Name
mode Lock Mode
id1 ID1 (varies depends on Lock name)
id2 ID2 (varies depends on Lock name)

DFS lock handle is a wait event is associated with many different areas of database software code. It is important to identify the lock type, id1,id2 and understand the problem to resolve the root cause. Of course, many of these issues can be a new or already-identified software bugs too.

Mostly the DFS lock handle is associated with Sequences in RAC. When diagnosing issues with sequences, you are likely to observe heavy Global Enqueue Services traffic due to Global TX and TM enqueues, contenTon for the SQ enqueue, waits on “DFS Lock Handle”, and/or high wait Tmes for “row cache lock”. Besides adjusTng the CACHE value for sequences to be higher, you should also consider using DBMS_SHARED_POOL.KEEP to pin the sequence in the shared pool rowcache and reviewing your indexes performance for possible block split contenTon.

Top 5 Timed Events - log file switch (checkpoint incomplete)

Top 5 Timed Events - log file switch (checkpoint incomplete)

Waits for this wait indicates that the process is waiting for the log file switch to complete, but the log file switch is not possible because the checkpoint process for that log file has not completed. You may see this event when the redo log files are sized too small. One has to go below the AWR and look for log swtiches. Ideally the log switches should be 2 or 3 for hour.

The log switches under the Instance Activity Stats show that there are 33 switches happened during an hour which mean every 2 minutes there is switch. We need to increase the size of the online redo log files to get rid of this wait event as well as improve the performance of the database.

Top 5 Timed Events - db file sequential read

Top 5 Timed Events - db file sequential read

The db file sequential read wait event occurs when it has to wait for a single-block I/O read request to complete. Oracle issues single-block I/O read requests when reading from indexes, rollback segments, sort segments, controlfiles, datafile headers and tables (when tables are accessed via rowids).

The db file sequential read and db file scattered read events can be always confusing for everybody, as the sequential read is associated with index read and the scattered read has to do with full table scans. It seems like they would have to be the other way around. The db file sequential read is thus named because it reads blocks into contiguous memory, and the db file scattered read gets its name because it reads multiple blocks and scatters them into the Buffer Cache.

 

Causes and Actions

The following discusses the common causes of db file sequential read waits that result from index reads.

Physical I/O requests for index blocks are perfectly normal and so the presence of the db file sequential read waits do not necessarily mean that there is something wrong with the database or the application. The issue is not index reads, rather it is the waits that are caused by excessive and unwarranted index reads. The performance degradation is magnified by slow I/O subsystem and/or poor datafiles layout. The DBA should be concerned with the average I/O time and SQL statements that spend a lot of time on this wait event.

Newer storage subsystems should offer an average single-block I/O wait that does not exceed 1 centisecond (1/100 second). Query the V$SYSTEM_EVENT view for the average wait of your database. If the average I/O wait far exceeds this allowance, this could mean the storage subsystem is slow or the database files are poorly placed. This is a database issue and the DBA should verify that the system is properly configured by examining the mapping of database files to mount points, and mount points to physical disks and I/O controllers. The idea is to avoid hot spots. Datafiles should be created on LUNs (Disk Logical Unit Number) that are striped over enough disks, and mount points should be distributed across all available I/O controllers and busses.

If the average I/O wait time for the db file sequential read event is acceptable, but the event represents a significant portion of waits in a certain session, then this is an application issue. In this case, the DBA needs to determine which index is being read the most from the P1 and P2 values, identify the SQL statement, generate and examine the explain plan against the SQL predicates, and advise on tuning. Should the query access data through an index? Would a full table scan be more efficient? Does the query use the right driving table? The objective is to reduce both the logical and physical I/O calls, and this can be best achieved through SQL and application tuning.

The introduction of a new index in the database where the Stored Outline feature is not used may cause the optimizer to select a plan that uses the index, and depending on the SQL statement, could result in significant I/O wait time.

Out-of-date statistics can certainly induce the optimizer to generate poor execution plans, which may call for index reads when they shouldn't. Make sure all object statistics are kept up to date.