can't log from v$lock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: can't log from v$lock

  1. #1
    Join Date
    Nov 2009
    Posts
    2

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace the statement, see where your time is being taken

  3. #3
    Join Date
    Nov 2009
    Posts
    2

    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

  4. #4
    Join Date
    Mar 2001
    Location
    India
    Posts
    57
    Your temporary workaround is nice.
    Thanks and Regards,
    Gitesh Trivedi
    Dbametrix Solutions
    Database DBA support

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width