Re: Tracing query inside procedure without 10046

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