Re: Tracing query inside procedure without 10046

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
>>
>>

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