-
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
-
workaround to slow subselect on v$lock
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|