0585-0586

Previous | Table of Contents | Next

Page 585

they can proceed. Unfortunately, the only sure way to resolve this problem is to detect them as they occur and to deal with them individually. Oracle recommends two ways to avoid deadlocks:

• Applications should acquire locks in the same order.
• Always use the lowest necessary lock. For example, don't lock an entire table when only a row needs to be updated.

To resolve a deadlock, you must kill one of the processes ”or both ”at either the database or operating system level.

Checking Locking Situation

Oracle provides a utility script that checks the current lock state of the database. This script, utllockt.sql, provides a tree that shows what locks are held and what processes are waiting. It is

SQL> @$ORACLE_HOME/rdbms/admin/utllockt 

You can perform a query on the DBA_WAITERS to determine which sessions are waiting on locks and the sessions that hold them. It does not show all the sessions holding locks ”only the ones that cause wait states. This query enables you to view only the sessions that might cause locking problems:

select waiting_session, holding_session, lock_type, mode_held, mode_requested from dba_waiters / 

Other views provide additional locking information. The information that each one shows is

DBA_BLOCKERS	Sessions that have another session waiting on a lock and are not in a wait status themselves
DBA_DDL_LOCKS	DDL locks held and requested within the database
DBA_DML_LOCKS	DML locks held and requested within the database
DBA_LOCKS	All locks held or requested within the database
DBA_WAITERS	Sessions that are waiting for database locks and what session is currently holding the lock
V$ACCESS	Locked database objects and the sessions that are accessing them
V$LOCK	Database locks
V$SESSION_WAIT	Database sessions that are waiting

Unlike other performance tuning and optimizing operations, monitoring locks is usually reactive. Locks are not a problem until a deadlock or similar event occurs. Locking is generally stable and requires less DBA interaction than other performance-tuning tasks .

Page 586

Summary

Performance tuning is the art of balance raw statistics with intuition and experience to arrive at the best possible solution. Entire volumes of books have been written on this topic.

In this chapter, you learned some of the fundamental concepts of performance tuning. You learned how to extract and analyze memory and disk space to resolve contention . You saw guidelines and scripts that you can use to check the performance of a database.

Oracle responds differently on each platform, and the examples presented in this chapter are configured for a UNIX environment. You must determine how much of this material applies to your own site.

Previous | Table of Contents | Next