Re: List of SQL involved in a transaction

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Wed, 04 Feb 2009 06:44:14 -0700
Message-ID: <49899BAE.7040309_at_optimaldba.com>



If the sql issued in these sessions/transactions is part of a pl/sql stored procedure, you *might* be able to use v$sql.program_id to locate the stored procedure. The program_id is the object_id for the pl/sql procedure that *first* parsed the statement. This may not be the case in your situation, but it is worth taking a look.

If you are using statspack and have a snapshot from the time where there were problems, you should look for the sql_id in stats$sql_summary and use the value of program_id.

This method is far from exact (if the application is not using pl/sql stored procs and/or the sql was not first parsed by the current procedure the info can't be used), but it may direct you to the proper code.

Regards,
Daniel Fink

-- 
Daniel Fink

OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/




Vishal Gupta wrote:

> Thanks for all the responses.
>
>
> Cheers,
> Vishal Gupta
>
> On 3 Feb 2009, at 17:42, "Cary Millsap" <cary.millsap_at_method-r.com
> <mailto:cary.millsap_at_method-r.com>> wrote:
>
>> Yes, there may be lots of transactions in a session, but they're
>> delimited by XCTEND lines. If you're using autonomous transactions,
>> it might be a little tricky, but if you're not, then the job should
>> be very straightforward.
>>
>> Cary Millsap
>>
>>
>> On Tue, Feb 3, 2009 at 11:05 AM, Vishal Gupta <vishal_at_vishalgupta.com
>> <mailto:vishal_at_vishalgupta.com>> wrote:
>>
>> yes trace is one of the way. But then there could be lot of
>> transactions in a session. I was more interested in finding
>> information from v$ or x$ views.
>>
>> Say, you have session blocking another session. And you can find
>> out what objects are locked by blocking session and identify the
>> session as well. But one wanted to find out all the SQL
>> statements already executed by this blocking session in current
>> transaction, how do you generate this list. V$TRANSACTION gives
>> information about current SQL_ID and pre_sql_id. But not all of them.
>>
>> Regards,
>> Vishal Gupta
>> http://www.vishalgupta.com
>>
>> ________________________________
>>
>> From: oracle-l-bounce_at_freelists.org
>> <mailto:oracle-l-bounce_at_freelists.org> on behalf of Cary Millsap
>> Sent: Tue 03/02/2009 16:07
>> To: Vishal Gupta
>> Cc: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
>> Subject: Re: List of SQL involved in a transaction
>>
>>
>> dbms_monitor.session_trace_enable
>>
>> Cary
>>
>>
>>
>> On Tue, Feb 3, 2009 at 9:59 AM, Vishal Gupta
>> <vishal_at_vishalgupta.com <mailto:vishal_at_vishalgupta.com>> wrote:
>>
>>
>> Hi,
>>
>> Does anyone know how to find list of all the SQLs involved
>> in a transaction?
>>
>> Regards,
>> Vishal Gupta
>>
>> http://www.vishalgupta.com
>>
>>
>>
-- http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 04 2009 - 07:44:14 CST

Original text of this message