Re: Tracing query inside procedure without 10046

I think we need a better description of what you're trying to achieve and where you're starting from.

It seems you have a procedure declared within a procedure, and you believe you have identified an SQL statement that is executed within the inner procedure, but there are several inner procedures and you don't know which one is executing the statement.

Is the outer procedure part of a package, or a standalone stored procedure? What is the surrounding code that called the procedure? How did you work out that the outer procedure was the one you needed to drill into?
How did you discover you had a performance problem that needed to be addressed?
Why did you choose to addressi it by looking for the containing procedure rather than working out how to make the SQL more efficient?

It's also worth knowing whether this is something that is VERY expensive to run and runs only a few times, or something that is fairly cheap to run but runs a very large number of times (possibly from many different sessions at roughly the same time).

Regards
Jonathan Lewis

On Tue, 1 Aug 2023 at 06:26, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Dear Gurus,
>
> currently working on slowness issue of procedure which is part of
> procedure
> like : proc_name.subproc_name , over here i need to figure out the query
> related to subproc_name and not by using 10046 trace .
> I tried ashrpti.sql and used pls_object_entry but I didn't get any values
> in the report .
> Is there any way to figure out queries related to such subproc_name also
> tried from dba_hist* views too by providing top_sql /pls_object values but
> no luck .
>
> Currently in my situation trace was taken (10046) and from procedure
> verified queries and by recommendation on it . but it was challenging to
> figure out queries without tracing.
>
> It would be great help if you provide any light on it .
>
> Regards,
> Krishna
>
>

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