Oracle Advanced Performance Tuning Scripts

Enqueue Locking Scripts

enqueue_stats.sql

This script looks at X$KSQST, which contains a breakdown of enqueue gets and enqueue waits by lock type. Unfortunately, there is no indication of the relative duration of these waits, so care must be taken when interpreting the output.

Download script for: 7.3, 8.0 or 8.1

enqueue_locks.sql

Most blocking lock detection scripts fail to consider that processes waiting for, but not yet holding, a lock can block other processes that need a conflicting lock on the same resource. To resolve such problems, it is essential to consider the order of waiters.

This script shows all the locks that are held or wanted for each resource, together with the number of seconds since the lock was granted or requested respectively, in order. This script is intended to supplement other blocking lock detection scripts such as Oracle's utllockt.sql.

Download script for: 7.3, 8.0 or 8.1

missing_fk_indexes.sql

This script checks for foreign key constraints on tables with table locks enabled which are not supported by a valid index. A large proportion of blocking lock problems are due to the absence of such indexes.

Download script for: 7.3, 8.0 or 8.1

temp_tablespaces.sql

Using temporary tablespaces that allow PERMANENT contents can be a factor in ST enqueue contention, because every extent allocation for temporary segments in such tablespaces requires the ST enqueue to be held in exclusive mode. To help diagnose such problems, this script shows how many users are assigned to each temporary tablespace, whether PERMANENT or just TEMPORARY contents are allowed, and how much write I/O has been seen against each temporary tablespace.

Download script for: 8.0, or 8.1


Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home