can't log from v$lock

Hi,

In a 10.2 DB I try to log a history of blockings from v$lock. To identify blocking sessions I use this statement, which runs nicely fast:

select v$session.*, sysdate
from v$Session
where SID in ( select sid
from v$lock
where BLOCK > 0
or request > 0
)
;

no rows selected
Elapsed: 00:00:00.07

but when I try to insert this into my user-table (100% copy of v$session + 1 date-column), it runs terribly slow:

insert into ext_dk.session_log
select v$session.*, sysdate
from v$Session
where SID in ( select sid
from v$lock
where BLOCK > 0
or request > 0
)
;

0 rows created.
Elapsed: 00:00:50.51

I have another log-statement like this, working wiht pure v$lock as source (like sub-statement form above), which also runs in no time while inserting.

Only the combintion of session-date with subselelct frmo v$lock is running very slow, so I can't do that on a production-DB.

any idea why this happen and wherefrom this is caused ?

thanks for any tip LaoDe

trace the statement, see where your time is being taken

Hi dave,

I'll do that as soon as I'm free from the real blocking-analyze-case.

Meanwhile I use this workaround, which might be even a better statement anyway and it runs in no time:

insert into ext_dk.session_log
select s.*, sysdate
from v$Session s
,v$lock l
where s.SID =l.sid
and (l.BLOCK > 0
or l.request > 0
);

LaoDe

Your temporary workaround is nice.