How do I run the "lock utility scripts" within $ORACLE_HOME/rdbms/admin to detect Lock Contention
Lock Utility Scripts (How to Run)
To run the "lock utility scripts" within the `$ORACLE_HOME/rdbms/admin` directory to detect lock contention in Oracle 13c, follow these steps:
Navigate to the Oracle Home Directory:
First, you need to access the Oracle home directory where your RDBMS is installed. Use the following command in your terminal:
cd $ORACLE_HOME/rdbms/admin
Identify the Lock Utility Scripts:
Oracle provides several scripts in the `$ORACLE_HOME/rdbms/admin` directory to help you detect and analyze lock contention. The key scripts include:
`utllockt.sql`: Provides information about lock contention by showing sessions that are waiting on locks and the sessions holding the locks.
`utllock.sql`: Provides detailed information about all locks held in the database.
`catblock.sql`: Displays a summary of all active locks, waiters, and blockers.
Connect to SQL*Plus:
You need to connect to the database using SQL*Plus as a privileged user (such as `SYSDBA`):
sqlplus / as sysdba
Run the Lock Utility Script:
Depending on your specific needs, run one of the following scripts:
To check for lock contention using `utllockt.sql`:
@utllockt.sql
This script will provide a report showing which sessions are holding locks and which are waiting for them.
To display all locks in the database using `utllock.sql`:
@utllock.sql
This script gives you a detailed report of all locks held in the database.
To run the `catblock.sql` script for a summary of all locks:
@catblock.sql
This script provides a summary of all active locks, highlighting waiters and blockers.
Interpret the Output:
`utllockt.sql` The output will show sessions that are waiting on locks and the sessions holding those locks, which helps identify the source of contention.
`utllock.sql`: Provides detailed information about all the locks, which can be useful for a deeper analysis.
`catblock.sql`: Gives a high-level summary of the locks, helping you quickly identify where contention might be occurring.
Take Action Based on the Findings:
If lock contention is detected:
Investigate the blocking sessions to understand why they are holding the locks.
Consider killing sessions that are causing significant contention if they are not performing essential work.
Review and optimize the application logic or SQL queries to reduce lock contention.
Exit SQL*Plus:
Once done, exit SQL*Plus by typing:
exit
These steps will help you detect and analyze lock contention issues within your Oracle 13c database using the provided lock utility scripts.
There are several lock scripts within $ORACLE_HOME/rdbms/admin that can be used to see lock activity and to detect lock contention.
To install the scripts:
Enter Server Manager (SVRMGRL).
Run catblock.sql.
Then run utllockt.sql.
These scripts enhance the basic Oracle lock views, and allow for the easy detection of tasks that are holding locks, and tasks that are waiting
on locks to be released.
The catblock.sql script
The catblock.sql script creates the following useful views:
dba_waiters
dba_blockers
dba_dml_locks
dba_ddl_locks
dba_locks
REM alllocks.sql - shows all locks in the database.
REM written by Thomas Hauck
set linesize 132
set pagesize 60
spool /tmp/alllocks
column owner format a10;
column name format a15;
column mode_held format a10;
column mode_requested format a10;
column type format a15;
column lock_id1 format a10;
column lock_id2 format a10;
prompt note that $oracle_home/rdbma/admin/catblock.sql
prompt must be run before this script functions . . .
prompt querying dba_ddl_locks . . .
select
session_id,
owner,
name,
type,
mode_held,
mode_requested
from
sys.dba_ddl_locks;
prompt querying dba_locks . . .
select
session_id,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
from
sys.dba_locks;
SQL > @alllocks
Note that $ORACLE_HOME/rdbms/admin/catblock.sql
must be run before this script functions . . .
Querying dba_waiters . . .
no rows selected
Querying dba_blockers . . .
no rows selected
Querying dba_dml_locks . . .
SESSION_ID OWNER NAME MODE_HELD MODE_REQUE
---------- ---------- -------------- ---------- ----------
19 RPT RPT_EXCEPTIONS Row-X (SX) None
Querying dba_ddl_locks . . .
SESSION MODE_ MODE_
_ID OWNER NAME TYPE HELD REQUE
------- ----- --------------- -------------------- -------- -------
13 RPT SHP_PRE_INS_UPD Table/Procedure Null None
_PROC
13 SYS STANDARD Body Null None
14 SYS STANDARD Body Null None
13 SYS DBMS_STANDARD Table/Procedure Null None
14 SYS DBMS_STANDARD Table/Procedure Null None
13 SYS DBMS_STANDARD Body Null None
14 SYS DBMS_STANDARD Body Null None
13 SYS STANDARD Table/Procedure Null None
14 SYS STANDARD Table/Procedure Null None
9 rows selected.
Querying dba_locks . . .
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---------- ---------------- ---------- ---------- ---------- ---------
2 Media Recovery Share None 32 0
2 Media Recovery Share None 31 0
2 Media Recovery Share None 30 0
2 Media Recovery Share None 29 0
2 Media Recovery Share None 28 0
2 Media Recovery Share None 27 0
2 Media Recovery Share None 26 0
2 Media Recovery Share None 25 0
2 Media Recovery Share None 24 0
2 Media Recovery Share None 23 0
2 Media Recovery Share None 22 0
2 Media Recovery Share None 21 0
2 Media Recovery Share None 20 0
2 Media Recovery Share None 19 0
2 Media Recovery Share None 18 0
2 Media Recovery Share None 17 0
2 Media Recovery Share None 16 0
2 Media Recovery Share None 15 0
2 Media Recovery Share None 14 0
2 Media Recovery Share None 13 0
2 Media Recovery Share None 12 0
2 Media Recovery Share None 11 0
2 Media Recovery Share None 10 0
2 Media Recovery Share None 9 0
2 Media Recovery Share None 8 0
2 Media Recovery Share None 7 0
2 Media Recovery Share None 6 0
2 Media Recovery Share None 5 0
2 Media Recovery Share None 4 0
2 Media Recovery Share None 3 0
2 Media Recovery Share None 2 0
2 Media Recovery Share None 1 0
3 Redo Thread Exclusive None 1 0
14 PS Null None 0 0
14 PS Null None 0 1
19 DML Row-X (SX) None 1457 0
36 rows selected.
The script above can be used whenever you suspect that locks are impeding performance. This script interrogates all of the views that were
created in catblock.sql. Click the View Code button to see the script and the output.
The utllockt.sql Script
The utllockt.sql script creates a view called lock_holders that can then be queried to see all sessions that are waiting on locks. Beware, however, this view creates a temporary table and can run slowly. The script and output are below:
column waiting_session format a8
select lpad(' ',3*(level-1)) ||
waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
Output
Now that we see the Oracle utility scripts for locks, let's explore how we can do our own locking within Oracle with the dbms_lock package.
