Re: Tracing query inside procedure without 10046

Hi Krishna,

do you use an application log (as Jonathan suggested)? In this case, you can enable SQL TRACE (in worst case for the whole DB) and afterwards search for the inserts into this application log to give you the boundaries of your trace interests.
With those details, you can crop <https://youtu.be/YrY7o8VpIOc>the part of interest out of your total trace data - and work from there?

Even if you do not use an application log, you can enable DBMS_TRACE <https://dbaora.com/tracing-plsql-using-dbms_trace-oracle-database-11g-release-2-11-2/> in
addition with SQL TRACE - you will again find the interesting boundaries in inserts into PLSQL_TRACE_%
 tables.

Does this make any sense in this thread's context?  Martin

Am So., 6. Aug. 2023 um 10:10 Uhr schrieb Krishnaprasad Yadav < chrishna0007_at_gmail.com>:

> Hi Jonathan,
>
> Your understanding is correct , we tried from dba_hist_active_Sess_history
> but no luck , we could see the top sql statement of procedure itself
> i,e exec proc.subproc .
> since we didn't able to figure out sql_id involved in it , we tried to
> explore the things
>
> Regards,
> Krishna
>
> On Sat, 5 Aug 2023 at 22:54, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> My understanding so far is that the "application team" has identified a
>> procedure that has been declared inside another procedure as the (higher
>> level) source of poor performance in an overnight job. There is no
>> indication of how they might have done this, but I think it would have to
>> be through some application code that logs "starting subprocedure X /
>> ending subprocedure X".
>>
>> The OP's problem then seems to be that although that can see ALL the
>> activity relating to the MAIN procedure they have no way of identifying
>> which bits of that activity happened in the course of the sub-procedure
>> (any identification of top-level PL/SQL will refer only to the MAIN
>> procedure).
>>
>> If my understanding is correct then the OP will only be able to get any
>> useful information from the existing dba_hist_active_sess_history if the
>> application team identifies and supplies the SQL_ID of any SQL embedded
>> within the subprocedure, or tells the OP how they identified the
>> subprocedure as the source of the problem and supplied the start and end
>> times of any calls tot he subprocedure.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Sat, 5 Aug 2023 at 17:54, Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>>> On 8/5/23 05:33, Lothar Flatz wrote:
>>>
>>> Hi,
>>>
>>> no. You can set a trap that would automatically trace a certain sql_id
>>> when it executes.
>>> E.g.
>>> https://smarttechways.com/2018/12/10/trace-the-sql-query-with-sql-id-in-oracle/
>>> Traces in Oracle are very complex, there are options to trace almost
>>> everything you can imagine.
>>> Stefan Köhler is an expert on it.
>>>
>>> It is also possible to turn on SQL Trace programmatically, through the
>>> DBMS_MONITOR package. It would work even better if you use
>>> DBMS_APPLICATION_INFO to set up client id and module id. You can then turn
>>> on tracing for client_id or module_id. However, I am not sure what does the
>>> OP want to do? Is the goal do determine procedure which starts the SQL or
>>> is the purpose to determine which SQL is causing the problem? For the
>>> latter, the application trace using DBMS_MONITOR is the Best Way (TM).
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>>
>>>

-- 
Martin Berger                Oracle ♠
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

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