Re: Tracing query inside procedure without 10046

Hi,

if we really got a log like  "starting subprocedure X / ending subprocedure X" it might contain timestamps. Together with the session information this would allow to query ASH.

How about it?

Thanks

Lothar

Am 06.08.2023 um 10:08 schrieb Krishnaprasad Yadav:
> 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-1217
> https://dbwhisperer.wordpress.com
>

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