DML_LOCKS

Property	Description
Parameter type	Integer
Default value	Derived: 4 * TRANSACTIONS
Modifiable	No
Range of values	20 to unlimited; a setting of 0 disables enqueues
Basic	No
Real Application Clusters	You must set this parameter for every instance, and all instances must have positive values or all must be 0.

A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.

The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.

Enqueues are shared memory structures that serialize access to database resources. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you should be aware of the following restrictions when you set you DML_LOCKS to 0:

· You cannot use DROP TABLE, CREATE INDEX statements

· You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE

· Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0

Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter.

Note:

1. Setting DML_LOCKS=0 in a Parallel Server environment can help performance as global DML locks are not required. From Oracle 7.2+ onwards DML locks can be disabled on a per table basis with the command ALTER TABLE ... DISABLE/ENABLE TABLE LOCK.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘DML_LOCKS’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
dml_locks	11636	TRUE	FALSE	FALSE	FALSE	FALSE	dml locks - one for each table modified in a transaction

Oracle initializatoin parameters