Re: How to find the exact SQL locking others?
- From: Leyi Kamus Zhang <kamusis@xxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Mon, 2 Jul 2012 21:49:58 +0800
Hi Martin
Thanks for your reply.
The most useful reason I can imagine for this function is for application
tuning. You know, the SQL issued in session 1 and session 2, maybe not the
same even they are modifying the same set of rows. So if we can get the
exact SQL which locked others, we can check easily the exact position coded
in application, and of course we can tuning it more easily.
One of my friends did a test, he searched the redo change vector logged in
the trace from the "oradebug dump processstate level 10", he can find the
object modified by the SQL, the before-value and after-value of the row
columns, so yes, I think I can find the exact SQL text by Logminer (from
SQL_REDO), but it's really complicated when doing it in a product system.
OK, Maybe you are correct, there is no NORMAL way to know the SQL.
--
Kamus <kamusis@xxxxxxxxx>
Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org
On Mon, Jul 2, 2012 at 8:53 PM, Martin Berger <martin.a.berger@xxxxxxxxx>wrote:
> Kamus,
>
> I tried to answer this question some time ago and came to the conclusion:
> you can't!
>
> Of course it's hard to prove the absence of a possibility. but I just
> tried to imagine if (and where) Oracle would need this information? It just
> don't need it!
> The whole uncommitted thing is handled by a transaction. For any
> consistent get in buffer cache, the transaction is sufficient; also for
> rollback (or commit).
> At the moment a cursor is closed, no one cares about it anymore.
>
> Even in log miner there is the transaction and the changes, but afaik not
> the exact statement?
>
> If you find any reason the RDBMS needs this information, let's dig into
> it.
> Otherwise this feature must be part of any kind of enhancement request
> (either filed in the past or future) otherwise it does not exist in the
> code.
>
> sorry for this kind of answer,
> Martin
>
> On Mon, Jul 2, 2012 at 9:45 AM, Leyi Kamus Zhang <kamusis@xxxxxxxxx>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
>>
>> --
>> Kamus <kamusis@xxxxxxxxx>
>>
>> Visit my blog for more : http://www.dbform.com
>> Join ACOUG: http://www.acoug.org
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
