This parameter defines a percentage of the lock list held by an application that must be filled before the database manager performs lock escalation.
Lock escalation is the process of replacing row locks with table locks, reducing the number of locks in the list. When the number of locks held by any one application reaches this percentage of the total lock list size, lock escalation will occur for the locks held by that application. Lock escalation also occurs if the lock list runs out of space.
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks. If after replacing these with a single table lock, the maxlocks value is no longer exceeded, lock escalation will stop. If not, it will continue until the percentage of the lock list held is less than the value of maxlocks. The maxlocks parameter multiplied by the maxappls parameter cannot be less than 100.
When this parameter is set to AUTOMATIC, it is enabled for self tuning. This allows the memory tuner to dynamically size the memory area controlled by this parameter as the workload requirements change. Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self tuning in order for self tuning to be active.
The value of locklist is tuned together with the maxlocks parameter, therefore disabling self tuning of the locklist parameter automatically disables self tuning of the maxlocks parameter. Enabling self tuning of the locklist parameter automatically enables self tuning of the maxlocks parameter.
Automatic tuning of this configuration parameter will only occur when self tuning memory is enabled for the database (the self_tuning_mem configuration parameter is set to ON).
maxlocks = 2 * 100 / maxappls
Where 2 is
used to achieve twice the average and 100 represents
the largest percentage value allowed. If you have only a few applications
that run concurrently, you could use the following formula as an alternative
to the first formula: maxlocks = 2 * 100 / (average number of applications running
concurrently)
Where 4096 is the number of bytes in a page, 100 is the largest percentage value allowed for maxlocks, and 48 is the number of bytes per lock on a 32-bit system, 80 is the number of bytes per lock on a HP-UX 64-bit system, and 64 is the number of bytes per lock on other 64-bit systems. If you know that one of your applications requires 1000 locks, and you do not want lock escalation to occur, then you should choose values for maxlocks and locklist in this formula so that the result is greater than 1000. (Using 10 for maxlocks and 100 for locklist, this formula results in greater than the 1000 locks needed.)
If maxlocks is set too low, lock escalation happens when there is still enough lock space for other concurrent applications. If maxlocks is set too high, a few applications can consume most of the lock space, and other applications will have to perform lock escalation. The need for lock escalation in this case results in poor concurrency.
You can use the database system monitor to help you track and tune this configuration parameter.