Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

Part Number B28310-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

Monitoring Performance

Monitoring database performance is covered in detail in Oracle Database Performance Tuning Guide. Here are some additional topics with details that are not covered in that guide:

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.

The utllockt.sql script displays, 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 this script file is operating system dependent.

A second script, catblock.sql, creates the lock views that utllockt.sql needs, so you must run it before running utllockt.sql.

See Also:

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. A discussion of these views and their role in instance tuning is contained in Oracle Database Performance Tuning Guide.

Performance Monitoring Data Dictionary Views

This section lists some of the data dictionary views that you can use 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

See Also:

Oracle Database Reference for detailed descriptions of these views