Systems Engineering and RDBMS

Lock Detection Scripts in Oracle (Non-RAC environment)

Posted by decipherinfosys on June 22, 2007

Here are some lock detection scripts for Oracle in a non-RAC environment.  Next time, we will post the ones that apply to the RAC environment and will post the series of steps on how to help detect those issues using the gv$ views.  In the following SQL scripts, it is assumed that you already have the select privileges on the objects in the from clause.  If you do not, then you need to grant those select privileges to the Oracle User using which you will be running these scripts.  If you want to look for a specific Oracle user, replace the “username is not null” clause with the equality operator and given that particular user name.  Also, the RULE hint is used because we ran into a performance issue in 9iR2 where accessing the system views was very slow using the ALL_ROWS or FIRST_ROWS_N optimizer modes.  The RULE mode is deprecated in 10g, so you can either leave it there if you want a generic script for all environments (when used that way, the hint is ignored) or just yank it out for your 10g installations.

Login into database using SQLPlus to run the following 2 sqls to find out the locking information in the
database.
SQL 1: Shows the SID and SERIAL# of all the locks currently held in the db along with the type of the lock
and related information.
SQL 2: Shows the SID and SERIAL# of all the locks along with the currently SQL being run.

set lines 120 pages 1000 feed on verify off echo off
column sid          heading ‘Sid’         format 99999
column serial_num   heading ‘Serial#’     format 999999
column db_username  heading ‘DB|Username’ format a15 wrap
column process      heading ‘Process’     format a10 truncate
column host_pid     heading ‘Host|PID’    format a8  wrap
column machine      heading ‘Host’        format a12 truncate
column program      heading ‘Program’     format a25 truncate
column object_name  heading ‘Object|Name’ format a20 wrap
column lock_type    heading ‘Lock|Type’   format a5 truncate
column mode_held    heading ‘Mode|Held’   format a10 truncate
column mode_req     heading ‘Mode|Requested’ format a15 truncate
column ctime        heading ‘Time Hrs.|Since|LMode|Held’ format a8 truncate
column is_blocking  heading ‘Blocking?’   format a12 wrap
break on sid on serial_num on process on db_username on spid on machine
select /*+rule */
ses.sid as sid,
ses.serial# as serial_num,
ses.process  as process,
ses.username as db_username,
pro.spid     as host_pid,
ses.machine  as machine,
substr(ses.program,1,30) as program,
substr(obj.object_name,1,20) as object_name,
loc.lock_type as lock_type,
loc.mode_held as mode_held,
loc.mode_requested as mode_req,
to_char(trunc(sysdate) + loc.last_convert/(24*3600), ‘HH24:MI:SS’) as ctime,
loc.blocking_others as is_blocking
from v$session ses,
v$process pro,
dba_lock loc,
dba_objects obj
where ses.sid      = loc.session_id
and ses.paddr    = pro.addr
and loc.lock_id1 = obj.object_id
and ses.username is not null
order by ses.sid, ses.serial#, ses.process, ses.username
/
clear columns
clear breaks

prompt
prompt
col sid        heading ‘Session|Id’  format 99999
col serial_num heading ‘Serial#’     format 999999
column db_username  heading ‘DB|Username’ format a15 wrap
column lock_id1 format a8 noprint
column ctime   heading ‘Time Hrs.|Since|LMode|Held’ format a8 truncate
col sql        heading ‘SQL|Text’    format a64 wrap
break on sid on serial_num on process on db_username
select /*+rule */
a.sid as sid,
a.serial#  as serial_num,
a.process  as process,
a.username as db_username,
c.lock_id1 as lock_id1,
to_char(trunc(sysdate) + c.last_convert/(24*3600), ‘HH24:MI:SS’) as ctime,
b.sql_text sql
from   v$session a,
v$sqltext b,
dba_lock c
where  a.sid            = c.session_id
and    a.username is not null
and   ((a.sql_address    = b.address
and  a.sql_hash_value = b.hash_value)
or    (a.prev_sql_addr  = b.address
and  a.prev_hash_value = b.hash_value))
order by a.sid, a.serial#, a.process, a.username, c.lock_id1, b.piece
/
clear breaks
clear columns

If your Oracle user has the “ALTER SYSTEM” privileges, you can then use the SID and SERIAL# information from above and use that to kill the sessions (if at all needed) that are holding blocking locks for a longer duration of time.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: