Operating System - HP-UX
1748058 Members
5696 Online
108758 Solutions
New Discussion юеВ

Re: Oracle ORA-00055 DML locks errors

 
SOLVED
Go to solution
Gino Castoldi_2
Honored Contributor

Oracle ORA-00055 DML locks errors

Hi,

HP-UX Oracle 8.1.7.4 MC/SG OVO NNM

We recently received this error message from our application log file (HP OVO):
"Database: ORA-00055: maximum number of DML locks exceeded".

I looked up this error and it says to increase the number of DML locks and restart the Oracle database. The DML locks are currently set to
100 in the initopenview.ora file.

Is that all we need to do? What would be the recommended value to set DML locks to?
Any performance impact when the DML locks value
is increased?

10 points to any good answer.
TIA, Gino
7 REPLIES 7
Marcel Boogert_1
Trusted Contributor
Solution

Re: Oracle ORA-00055 DML locks errors

Hi there,

You can increase the DML locks value as easy as that, I set them by default to 250. I run the same environment, 8.1.7.4.0

MB.
Marcel Boogert_1
Trusted Contributor

Re: Oracle ORA-00055 DML locks errors

Hi there again,

The DML locks stands for Data Manipulating Locks. It means that there can be 250 simultanious locks to the database. It is meant for commit and rollback. If a user changes a value in a table and doesn't commit the change, the DML lock gets active.

Regards, MB.
Marcel Boogert_1
Trusted Contributor

Re: Oracle ORA-00055 DML locks errors

Gino,

Found this also:

Default Value : 4 * TRANSACTIONS (Derived) of DML locks.
Maybe this could also help.

MB.
Indira Aramandla
Honored Contributor

Re: Oracle ORA-00055 DML locks errors

Hi Gino,

Yes as the above posts mentioned,
00055, 00000, "maximum number of DML locks exceeded" is caused as it ran out of DML lock state objects. To resolve this increase the value of the DML_LOCKS initialization parameter (in your case initopenview.ora file) and warm start the database.

DML_LOCKS is an integer type parameter and the default value is tables referenced for each transaction (derived ├Г  4 * transactions). Since it is a static parameter, when changed the database as to be re-started for the change to be effective. Range os values is 20 to unlimited, and it varies for different releases. (Note: a setting of 0 disables enqueues). There is no maximum value to this parameter. You can set this parameter to any value. Setting this parameter to a very large value will consume more memory within the shared pool.

Here is a short description about DML_LOCKS

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 cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.
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.

Indira
Never give up, Keep Trying
Jean-Luc Oudart
Honored Contributor

Re: Oracle ORA-00055 DML locks errors

Hi Gino,

attached FAQ on lock.
There are lots of pointers to other documents on Metalink, SQL and scripts to monitor locks

Regards,
Jean-Luc
fiat lux
Yogeeraj_1
Honored Contributor

Re: Oracle ORA-00055 DML locks errors

hi gino,

please also read the information found at:
http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c23cnsis.htm#2937

best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Oracle ORA-00055 DML locks errors

Gino,

regarding Marcel's post.
In fact this is a derived (calculated) parameter :
DML_LOCKS= 4 * TRANSACTIONS
TRANSACTIONS= 1.1 * SESSIONS
SESSIONS= 1.1 * PROCESSES + 5
PROCESSES : static parameter, default = 30

I suggest you change the processes value and re-start the instance.

Usually I would size the "processes" parameter with number of expected concurrent connections plus room for growth

Regards,
Jean-Luc
fiat lux