O'Reilly logo

Oracle Internals: An Introduction by Steve Adams

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required