What do Enqueue waits stand for?
The Enqueue wait event may be an indication that something is
either wrong with the code (should multiple sessions be serializing
themselves against a common row?) or possibly the physical design (high
activity on child tables with unindexed foreign keys, inadequate
INITRANS or MAXTRANS
values, etc.).
What actions can be taken?
You could check whether you have child tables with unindexed foreign key
constraints, issuing the following statement either as SYS or
SYSTEM user:
SELECT con.owner, con.table_name, co.column_name,
con.constraint_name, i.index_name
FROM dba_constraints con, dba_cons_columns co,
dba_ind_columns i
WHERE con.owner NOT IN ('SYS','SYSTEM')
AND con.constraint_type='R'
AND co.owner NOT IN ('SYS','SYSTEM')
AND i.table_owner NOT IN ('SYS','SYSTEM')
AND con.owner=co.owner
AND con.table_name=co.table_name
AND con.constraint_name=co.constraint_name
AND co.owner=i.table_owner(+)
AND co.table_name=i.table_name(+)
AND co.column_name=i.column_name(+)
AND i.index_name IS NULL;
|
If there are any rows returned, you should create appropriate indexes for
the columns reported that may then be used for the constraints. Be aware
that, if a foreign key constraint consists of multiple columns of a table,
all these columns must be included in ONE index in order for the index to
be used.
To see what indexes exist that could be used for the constraint, just
ommit the last line (index_name IS NULL ).
Since this event also indicates that there are too many DML or DDL locks (or,
maybe, a large number of sequences), increasing the
ENQUEUE_RESOURCES parameter in the init.ora may
help reduce these waits as well. To check the current values, execute the
following statement as SYS or SYSTEM :
SELECT *
FROM v$resource_limit
WHERE resource_name='enqueue_resources'
|
Most important for your decision are the values of the columns
max_utilization , limit_value and
initial_allocation . If the first does not exceed either of the
other two, there's nothing to do for you at this place: according to the
Oracle documentation, Oracle automatically allocates additional enqueues
(additional to initial_allocation ) from the shared pool when
needed, as long as this does not exceed the limit_value .
|