Re: v$lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 27 Jun 2001 10:10:52 +0100
Message-ID: <993633506.12696.0.nnrp-13.9e984b29@news.demon.co.uk>

It's not normal -
have a look at v$session_wait for the calling session when the query hangs.

the 15 or so locks for SID = 2 are the Media Recovery locks - one per file.

I would consider enhancing your strategy by only considering blocking transactions (V$LOCK.BLOCK=1), and then only if their CTIME was more than a few seconds and the thing they were block had also recorded a few seconds.

I would also be a little cautious about killing blocking sessions if this would result in a large rollback - see v$transaction.used_urec

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Michael B. Allen wrote in message ...


>If I do:

>

>select v$session.sid, v$session.username

> from v$session, v$lock

> where v$session.sid = v$lock.sid;

>

>the query returns about 15 entries with sid == 2 but then just hangs. Is

>this normal?

>

>I want to write two cgi scripts; one that produces a list of sessions

>holding locked records with username, machine, program, etc with a link

>to the second cgi script which will kill the session with alter system

>kill ...

>

>What's the best way to do this?

>

>Thanks,

>Mike

Received on Wed Jun 27 2001 - 04:10:52 CDT