Oracle Internals: An Introduction

Reference

This section contains a quick reference to the parameters, events, statistics, waits, and APT scripts mentioned in Chapter 4.

Parameters

Parameter	Description
_DISTRIBUTED_LOCK_TIMEOUT	Timeout for assumed deadlocks on distributed transactions. Defaults to 60 seconds.
_ENQUEUE_HASH	The size of the enqueue hash table.
_ENQUEUE_HASH_CHAIN_LATCHES	The number of latches used for access to the enqueue hash table. Defaults to the CPU count.
_ENQUEUE_LOCKS	The number of enqueue lock structures.
DML_LOCKS	The size of the DML locks fixed array. Where possible, DML locking should be disabled to reduce locking overheads and the risk of blocking locks.
ENQUEUE_RESOURCES	The size of the enqueue resources array.
PARALLEL_TRANSACTION_RESOURCE_TIMEOUT	Timeout for assumed deadlocks between the branches of a parallel transaction and another transaction in release 8.0.
TEMPORARY_TABLE_LOCKS	This parameter is obsolete in Oracle8. It does still exist in release 8.0, but setting it has no effect.

Events

Event	Description
60	This is the enqueue deadlock detection error. In cases of recurrent, mysterious deadlock problems, you may need to take a systemstate dump on this event to diagnose the interactions causing the deadlocks.
370	This event is signaled for assumed buffer cache deadlocks, and can be used for investigating severe buffer locking contention, using processstate dumps.
4020	This is the library cache deadlock detection error. With a systemstate dump on this event, you will be able to see what happened. Without it, you will never know.
4021	This is the library cache assumed deadlock timeout error. This timeout is needed because the library cache deadlock detection mechanism is not exhaustive, lest it be too expensive. Once again, this error is normally caused by ad hoc DDL.

Statistics

Statistic	Source	Description
enqueue conversions	V$SYSSTAT	Local enqueue conversions.
enqueue deadlocks	V$SYSSTAT	Local enqueue deadlocks detected and broken.
enqueue releases	V$SYSSTAT	Local enqueue releases.
enqueue requests	V$SYSSTAT	Local enqueue requests.
enqueue timeouts	V$SYSSTAT	Aborted local enqueue operations.
enqueue waits	V$SYSSTAT	The number of enqueue operations that waited. Not the number of waits.
exchange deadlocks	V$SYSSTAT	Number of local buffer deadlocks assumed. The statistic name reflects the fact that index block exchanges are one possible cause of such deadlocks.

Waits

Event	Description
buffer busy due to global cache	Waits to acquire a local buffer lock on a buffer that is locked for a global cache operation, such as a ping.
buffer busy waits	Waits for a local buffer lock on a buffer that is locked in an incompatible mode.
buffer deadlock	Assumed deadlocks while waiting for a local buffer lock.
enqueue	These are waits for both local and global enqueues.
library cache load lock	This wait is seen if two sessions attempt to load (not reload) the library cache information for an object simultaneously. Simultaneous reloads cause library cache pin waits instead.
library cache lock	Waits to reference a library cache object that is in flux.
library cache pin	Waits to modify a library cache object that is in flux.
row cache lock	Waits to obtain either a local row cache enqueue or a row cache instance lock.
write complete waits	Waits for a buffer lock on a block that is part of a normal write batch.

APT Scripts

Script	Description
enqueue_locks.sql	Shows enqueue locks held and wanted in the order requested.
enqueue_stats.sql	Shows the breakdown of enqueue gets and waits by type.
fixed_table_hwms.sql	Shows the high-water mark usage for the fixed tables under Oracle7. This can be used to check whether your settings for the corresponding initialization parameters are inadequate or overly generous. U nder Oracle8, use V$RESOURCE_LIMIT instead.