Quick summary of locks and skip v$lock.
Lock summary without accessing v$lock/*
Script to report summary of lock activity by waiters on the database.
OBJ -> Table: waitevent: p1: p2: locktype: ROWID.
CMD -> Type of command being waited on.
SES_CNT -> Number of sessions waiting.
HASH -> Hash value being waited on.
MODULE -> Module they are coming from.
Written by Sai */
col obj for a70
col cmd for a19
col module for a15
set lines 160
set trimspool on
select obj, cmd, count(*) as ses_cnt, min(hash_value) hash, module
from
(select decode(sign(ROW_WAIT_OBJ#), 1,
(select object_name from dba_objects where object_id=ROW_WAIT_OBJ# and data_object_id is not null)
,-1, 'UNKNOWN', 'UNKNOWN') || ':' ||
w.event || ':' || w.p2 || ':' || w.p3 || ':' ||
chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' ||
decode(sign(ROW_WAIT_OBJ#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects
where object_id = ROW_WAIT_OBJ# and data_object_id is not null),
ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#), 'No rowid') || '''' as obj,
decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd,
decode(sign(sql_hash_value), 1, sql_hash_value, prev_hash_value) hash_value,
module
from
v$session s, v$session_wait w
where
status = 'ACTIVE' and
lockwait is not null and
s.sid = w.sid)
group by obj, cmd, module
order by 3, 1
/
posted by Saibabu Devabhaktuni @ 7:54 PM
7 Comments:
your script receives an error
ORA-00918: column ambiguously defined
I think it's in the obj column. Please check it
Hi,
Thanks for your comment. Sorry, the original version was written for 8i & 9i, below one would work for 10g.
/*
Script to report summary of lock activity by waiters on the database.
OBJ -> Table: waitevent: p1: p2: locktype: ROWID.
SES_CNT -> Number of sessions waiting.
HASH -> Hash value being waited on.
INFO -> Command_type : module
Written by Sai */
col obj for a75
col ses_cnt for 9999
col info for a35
set lines 160
set trimspool on
select
obj,
count(*) as ses_cnt,
min(sql_hash_value) hash,
cmd ||' : '|| module "INFO"
from
(select decode(sign(row_wait_obj#), 1,
(select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null)
,-1, 'UNKNOWN', 'UNKNOWN') || ':' || event || ':' || p2 || ':' || p3 || ':' ||
chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' ||
decode(sign(row_wait_obj#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects
where object_id = row_wait_obj# and data_object_id is not null),
row_wait_file#, row_wait_block#, row_wait_row#), 'No rowid') || '''' as obj,
decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd,
sql_hash_value,
module
from
v$session
where
status = 'ACTIVE' and
lockwait is not null
)
group by obj, cmd||' : '||module
order by 2
/
Thanks,
Sai.
It was very interesting for me to read the blog. Thanx for it. I like such topics and anything that is connected to them. BTW, why don't you change design :).
Nice post you got here. I'd like to read something more concerning this matter. Thank you for sharing that info.
Hello friend amazing and very interesting blog about Quick summary of locks and skip v$lock.
I really like this write! I enjoy it so much! thanks for give me a good reading moment!
Anyway, I am adding this RSS to my email and could look out for much more of your respective interesting content. Make sure you update this again soon.
Post a Comment
<< Home