Friday, March 10, 2006

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
/

7 Comments:

At December 10, 2007 at 2:19:00 AM PST, Blogger Unknown said...

your script receives an error
ORA-00918: column ambiguously defined

I think it's in the obj column. Please check it

 
At December 10, 2007 at 10:02:00 AM PST, Blogger Saibabu Devabhaktuni said...

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.

 
At January 27, 2010 at 1:54:00 PM PST, Anonymous Anonymous said...

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 :).

 
At February 12, 2010 at 12:14:00 AM PST, Anonymous Anonymous said...

Nice post you got here. I'd like to read something more concerning this matter. Thank you for sharing that info.

 
At April 22, 2010 at 1:21:00 PM PDT, Anonymous cheap viagra said...

Hello friend amazing and very interesting blog about Quick summary of locks and skip v$lock.

 
At April 27, 2010 at 9:25:00 AM PDT, Anonymous citrate sildenafil said...

I really like this write! I enjoy it so much! thanks for give me a good reading moment!

 
At April 15, 2014 at 10:42:00 AM PDT, Anonymous Buy EZ Flash IV said...

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