DML LOCKS LIMIT!

[amitstora]

Hi all!

Working on Oracle v8i with OS as NT Forms & Reports v6i.

Query:

1.What should be the number of DML_LOCKS IN INIT.ORA parameter File.

I increased it from 500 to 650 as of now as developers were complaining the errorML LOCKS exceeded.

Would this increased number affect the DB performance.
We have around 40 developers working.

What is the maximum limit to set DML_LOCKS!

2.What should we look for and resolve when suddenly FORMS & REPORTS start running slow!

3.Is TKPROF a DB UTILITY OR OS UTILITY!

Thanks & Regards,

Amit.

[mrvajrala]

This is due to the parallel DML activity on a set of tables. One way to resolve this is , proper scheduling of the JObs/applications.

if that is not possible , run the following query

SELECT resource_name, current_utilization , limit_value
FROM v$resource_limit
WHERE resource_name IN ('dml_locks','enqueue_locks');


You may wish to increase the DML_LOCKS parameter value and not beyond the enqueue_resources.

I think there is some rule of thumb..

dml_locks + 20 < enqueue_resources...

In My DB, enqueu_resources are 1000 and dml_locks are 500