Author: Burleson
Locking is a very important part of any database system. Database table locks are coordinated through global inter-instance communication for RAC. It is the fact that properly designed applications need not to lock entire tables and therefore table locks can be disabled to improve locking efficiency with minimal adverse side effects.
Disabling Oracle Table Locks:
There are two methods for disabling table locks.
- Disabling table locks for individual tables.
- Setting dml_locks to zero.
Individual Oracle Table Locks:
You can enable or disable the ability to acquire individual table locks. You can use below command to prevent users from acquiring individual table locks. When users attempt to lock tables with disabled locks, they will receive an error.
ALTER TABLE table_name DISABLE TABLE LOCK
You can use below statement to re-enable table locking after a transaction.
ALTER TABLE table_name ENABLE TABLE LOCK
This forces all currently executing transactions to commit before enabling the table lock. The statement does not wait for new transactions to start after issuing the ENABLE statement. The disadvantage of using this statement is that it must be executed for all tables that may experience improper locking.
Oracle table_lock column:
The table_lock column can be queried to determine whether a table in the schema has its table lock enabled or disabled. The table_lock column lies in the user_tables data dictionary table.
The table lock state of other user’s tables can be queried as well if SELECT privilege is on dba_tables. The all_tables views can be used to see the locking state of tables for which a user has been granted SELECT privileges.
Table Locks for entire instance:
The table locks can be set for an entire instance by using the dml_locks initialization parameter. This will disable the DROP TABLE, CREATE INDEX and LOCK TABLE commands.
If the DROP TABLE, CREATE INDEX and LOCK TABLE commands are not needed then dml_locks should be set to zero to minimize lock conversions and achieve maximum performance. DDL statements cannot be executed against tables with disabled locks.
SQL*Loader checks the flag to ensure that there is not a non-parallel direct load running against the same table. Oracle is forced to create new extents for each session by using direct load.
If dml_locks are set to zero on one instance then it must be set it to zero on all instances. If non-zero values are used with the dml_locks parameter then the values need not be identical on all instances.
DDL Statements for maintenance tasks:
In any Oracle database, DDL statements should be used for maintenance tasks not during normal system operations. This will make the frequency of DDL statements should low. You can use global temporary tables or PL/SQL tables instead of permanent tables for reports.
RAC Performance Degradation:
Performance degradation to RAC environment will occur if the application has to create objects frequently. This is because the object creation requires inter-instance coordination. A large ratio of dlm_conflicts to dlm_requests on the dc_object_ids row cache in v$rowcache along with excessive wait times for the row cache lock event in v$system_event indicates that multiple instances in the cluster are issuing excessive amounts of concurrent DDL statements.
Improving Concurrent Object Creation:
In such cases you can improve the object creation performance by setting event 10297 so that it caches object_id values. This will improve concurrent object creation by eliminating the recursive DDL and some of the intra-instance pinging. Add below line to initialization parameter file in order to set event 10297.
event=”10297 trace name context forever, level 1″
Adjusting Caching behavior:
The caching behavior is automatically adjustable internally if the additional level argument is set to one. Otherwise the level can be set to the desired cache size.
