Spotlight on Oracle 10.10 - Release Notes

Enqueue Waits

Enqueue waits occur when a session waits to obtain a lock. In most cases, this occurs because of a lock on a table or row that the waiting session needs to lock or modify. In some circumstances, the lock involved may be an Oracle internal lock. If the database is well tuned and the application design sound, enqueue waits should be negligible. Common causes of excessive enqueue waits are:

• Contention for a specific row in the database. The application design may require that many processes update or lock the same row in the database. One common example of this occurs when primary keys are generated using a sequence table.
• Table locks caused by foreign keys that have not been indexed. If an update is made to a foreign key that has not been indexed, the parent table is subjected to a table lock until the transaction is complete.
• Old-style temporary tablespaces. If the tablespace named as the temporary tablespace has not been identified with the temporary clause, sessions may contend for a space transaction lock.
• The space reserved for transactions within a data block is too small. By default, only one transaction slot for tables, or two for indexes, is allocated when the table or index is created. If additional transaction slots are required, they are created providing there is free space in the block. However, if all transaction slots are in use, and there is no free space in the block, a session that needs to lock a row in the block encounters an enqueue wait. This occurs even if the row in question is not actually updated or locked. This can occur if both PCTFREE and INITRANS were set too low.

Related Topics

Deal With Wait Events