Hello all!
This is a very interesing one… what about database taking too long or even showing locks that don’t really exists?
I faced some weird situations related to the size of DBMS_LOCK_ALLOCATED.
After some research, seems root cause is descripted in MOS DBMS_LOCK_ALLOCATED Table Keeps Growing Eventually Depleting Tablespace (Doc ID 1054343.6).
The issue is:
– DBMS_LOCK_ALLOCATED is the table that keep all locks on database.
– This table keeps growing even though many of the locks are probably no longer used.
– Upon checking the EXPIRED column, it reveals that the locks’ expiration date is a year or more in the future.
Besides the mentioned “storage space issue” in MOS Doc, there is also the situation that we have actually 1073741823 “lockhandlers” available. And what if this ‘not releasing’ cause we run out of handlers? Unlike, but possible.
Having a big table can also cause bad performance on DBMS_LOCK.REQUEST, that is the basic mechanism for locks (passing the handler, lockmode, timeout and release instructions).
This was my situation. Found in trace that this command was taking too long:
SELECT DBMS_LOCK.REQUEST('1079914179107991417983', 6, 1) FROM DUAL;
And, after a while, returning 1. Checking on documentation for return values:
0 Success
1 Timeout
2 Deadlock
3 Parameter error
4 Already own lock specified by id or lockhandle
5 Illegal lock handle
(https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lock.htm#i997212)
The solution?
Delete entries for locks that are no longer being used with the following SQL (connect using sys user):
DELETE dbms_lock_allocated
WHERE lockid NOT IN (SELECT id1 FROM V$LOCK);
COMMIT;
The nested sub-query will return a list of id numbers for locks that are currently held in the database instance.
Any locks listed in the DBMS_LOCK_ALLOCATED table that are not currently held in the database instance will be deleted.
* Using delete, the space allocated by the table will remain. In order to reclaim the space, either do an ALTER TABLE MOVE or TRUNCATE (be carefull with this one).
In case it becomes frequent, consider creating a periodic routine that will clean out the DBMS_LOCK_ALLOCATED table on a daily, weekly, or monthly basis.
An additional cares:
1. Please avoid doing it on transactional period. Creating locks on this table is possible/probably during procedure and may affect the possibility of create new locks on database.
2. Avoid doing it on RAC (use of gv$, for example). Bug 2624130 can make this delete very slow.
– The only way to guarantee would be getting a table lock in exclusive mode over dbms_lock_allocated and then do the delete the fastest possible which apparently is by using the NOT EXISTS.
– The lock on the table is to prevent that new rows are inserted into dbms_lock_allocated while doing the delete.
DELETE dbms_lock_allocated WHERE NOT exists (SELECT id1 FROM GV$LOCK_NH where lockid=id1 and type='UL') / commit;
I really hope you to not be in this situation, but if so, hope it helps. 🙂
