Re: Performance Question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 26 Apr 2006 15:31:42 +0100
Message-ID: <hYmdna7iIM_RGdLZRVnyvA@bt.com>

"Maxim Demenko" <mdemenko_at_arcor.de> wrote in message news:444f8030$0$4514$9b4e6d93_at_newsread2.arcor-online.net...
> jsfromynr schrieb:
>> Hello All,
>> In order to find which segment is waiting I ran this statement with
>> system user.
>> Select EVENT,P1TEXT,P2TEXT,P1,P2,P3 From v$session_wait where
>> sid=<sessionid>
>> and used the value of P2 as block_id to see which table/index is being
>> waited.
>> Select segment_name from dba_extents where block_id=<P2>
>>
>> But I got no rows . Is something I am doing wrong?
>>
>> With Warm regards
>> Jatinder Singh
>>
>
> block_id points to the start of extent, you are waiting on block (p2)
> which may be any between block_id and block_id + blocks - 1 ( from
> dba_extents ).
> Also this query can be pretty expensive...
>
> Best regards
>
> Maxim

Good reply - particularly the "expensive" bit.

Addressed in 9i by v$session.

If a session is waiting for a block, the object_id will be recorded in v$session as row_wait_obj#.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Received on Wed Apr 26 2006 - 09:31:42 CDT