Oracle 参数 DML_LOCKS 官方解释，作用，如何配置最优化建议
本站中文解释
DML_LOCKS参数用于设置DML的提交行锁资源的数量，当一个用户锁定某一行记录时，其他用户可以有行锁定的资源数量。
正确设置DML_LOCKS参数的方法是，通过检查系统的使用情况，根据服务器的性能来判断，如果服务器的性能非常好，可以将DML_LOCKS参数设置的更高，反之则设低。
官方英文解释
DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction.
| Property | Description |
|---|---|
|
Parameter type |
Integer |
|
Default value |
Derived: 4 * |
|
Modifiable |
No |
|
Modifiable in a PDB |
No |
|
Range of values |
20 to unlimited; a setting of 0 disables enqueues |
|
Basic |
No |
|
Oracle RAC |
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). The DML_LOCKS 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 INDEXstatements -
You cannot use explicit lock statements such as
LOCK TABLE IN EXCLUSIVE MODE -
Enterprise Manager cannot run on any instances for which
DML_LOCKSis set to0
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.
See Also:
-
Oracle Database
Concepts for a discussion of lock and enqueue resources needed for parallel DML -
Oracle Database
Concepts for more information on data concurrency
