The LOCKS configuration parameter sets the initial size of the internal lock table. If the number of locks allocated by sessions exceeds the value of LOCKS, the database server increases the lock table by doubling its size. Each time that the lock table overflows (when it is greater than the size of LOCKS), the database server doubles the size of the lock table, up to 15 times. Each time that the database server doubles the size of the lock table, it allocates no more than 100,000 locks. After the fifteenth time that the database server doubles the lock table, it no longer increases the size of the lock table, and an application needing a lock receives an error. For more information on how to determine an initial value for the LOCKS configuration parameter, see LOCKS.
To monitor the number of times that applications receive the out-of-locks error, view the ovlock field in the output of onstat -p. You can also see similar information from the sysprofile table in the sysmaster database. The following rows contain the relevant statistics.
Every time the database server increases the size of the lock table, it places a message in the message log file. You should monitor the message-log file periodically and increase the size of the LOCK parameter if you see that the database server has increased the size of the lock table.
The lock table can hold up to 9,500,000 locks, which is the maximum value for the LOCKS parameter (8,000,000) plus 15 dynamic allocations of 100,000 locks each. However, a very large lock table can impede performance. Although the algorithm to read the lock table is efficient, you incur some cost for reading a very large table each time that the database server reads a row. If the database server is using an unusually large number of locks, you might need to examine how individual applications are using locks.
First, monitor sessions with onstat -u to see if a particular user is using an especially high number of locks (a high value in the locks column). If a particular user uses a large number of locks, examine the SQL statements in the application to determine whether you should lock the table or use individual row or page locks. A table lock is more efficient than individual row locks, but it reduces concurrency.
One way to reduce the number of locks placed on a table is to alter a table to use page locks instead of row locks. However, page locks reduce overall concurrency for the table, which can affect performance.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]