Re: How to find the exact SQL locking others?

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_at_gmail.com>

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_at_gmail.com>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_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

>>

>> --

>> Kamus <kamusis_at_gmail.com>

>>

>> Visit my blog for more : http://www.dbform.com

>> Join ACOUG: http://www.acoug.org

>>

>>

>> --

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

>>

>>

>>



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