8.2 Monitoring Performance

«« Previous
Next »»

Monitoring performance includes monitoring locks and wait events and querying a set of data dictionary views.

1. Monitoring Locks

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either user objects, such as tables and rows, or system objects not visible to users, such as shared data structures in memory and data dictionary rows.

Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.

A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks.

Oracle Database is designed to avoid deadlocks, and they are not common. Most often they occur when transactions explicitly override the default locking of the database. Deadlocks can affect the performance of your database, so Oracle provides some scripts and views that enable you to monitor locks.

To monitor locks:
  1. Run the catblock.sql, which creates lock views.
  2. Run the utllockt.sql script, which uses the views created by catblock.sql to display, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for.
The location of the script files is operating system dependent.

2. About Monitoring Wait Events

Wait events are statistics that are incremented by a server process to indicate that it had to wait for an event to complete before being able to continue processing. A session could wait for a variety of reasons, including waiting for more input, waiting for the operating system to complete a service such as a disk write, or it could wait for a lock or latch.

When a session is waiting for resources, it is not doing any useful work. A large number of waits is a source of concern. Wait event data reveals various symptoms of problems that might be affecting performance, such as latch contention, buffer contention, and I/O contention.

Oracle provides several views that display wait event statistics.

3. Performance Monitoring Data Dictionary Views

You can query a set of data dictionary views to monitor an Oracle Database instance.

These views are general in their scope. Other views, more specific to a process, are discussed in the section of this book where the process is described.

View Description
V$LOCK Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch
V$LOCKED_OBJECT Lists all locks acquired by every transaction on the system
V$SESSION_WAIT Lists the resources or events for which active sessions are waiting
V$SYSSTAT Contains session statistics
V$RESOURCE_LIMIT Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
V$LATCH Contains statistics for nonparent latches and summary statistics for parent latches

«« Previous
Next »»