Re: How to find the exact SQL locking others?

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 3 Jul 2012 14:31:58 -0400
Message-ID: <CAE-dsOK63F=81L6UL5uLNShZcQ+hoivjmLovk0aCSOft1U7cgQ_at_mail.gmail.com>

summary: figure out which table your blocked sessions sql is blocking on. See below for easy ways to parse that
go to v$open_cursor for the blocking session (from v$blocking_session) and find queries that have that table name. Parse out for selects that don't use select for update). should be a relatively small list. detailed version:

use v$block_Session to get the blocking session. parse the waiting sql of the session that is being blocked to see what table it is being blocked on. you can use both v$sql_plan and v$sqlarea

updates,deletes are easy
select object_name
from v$sql_plan
where sql_id = <sql_id>
where id = 1
and operation in ('UPDATE','DELETE')

inserts require you to go to v$sqlarea. best to use regular expressions. can be a bit tricky if your inserts can add hints. then there are all the other possibilities such as DDL. but you may not need to parse all that.

so now you know which table is being blocked.

v$open_cursor.sql_id for all open queries in the blocking session get a list of sql that has your table_name in it. you can more specifically filter out SELECT statements, etc...

you may see more than 1, but the list should be alot smaller.

On Mon, Jul 2, 2012 at 3:45 AM, Leyi Kamus Zhang <kamusis_at_gmail.com> wrote:

> Hi Lists
> Maybe the question is not so easy as it looked from subject.
>
> Session 1:
> SQL> update t set n=2 where n=1;
> <<==== no commit here
> SQL> select sysdate from dual;
> SQL> select table_name from tabs;
> <<==== run any SQL that you want, to age out the SQL_ID and PREV_SQL_ID in
> v$session
>
> Session 2:
> SQL> update t set n=3 where n=1;
> <<===== will hang in wait for "TX-row lock contention"
>
> My question is: How to find the exact SQL "update t set n=2 where n=1" was
> issued in session 1?
>
> I tried:
> 1. from v$active_session_history, the SQL is not captured by sampling
> 2. from v$open_cursor, no luck
> 3. oradebug dump processstate, no SQL text
> 4. oradebug hanganalyze, no SQL text
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 03 2012 - 13:31:58 CDT