Re: Tracing query inside procedure without 10046

Hi Andy,

Thanks for reverting back .

For past execution can we also be able to do it in the same way , i mean to do group by of program_line# and higher one would be prominent one to figure out the bottleneck query . If this understanding is correct, not sure about which view can help me for porgram_line# from dba_hist* views , can you please suggest that too.

Regards,
Krishna

On Tue, 1 Aug 2023 at 11:03, Andy Sayer <andysayer_at_gmail.com> wrote:

> The 10046 has everything needed but if you don’t want to go through it:
> v$sql.program_id gives you the object_id of the pl/Sql program which first
> parsed the query. V$sql.program_line# will give you the line number, which
> could be useful in large named programs.
>
> Hope that helps,
> Andy
>
> On Mon, Jul 31, 2023 at 10:26 PM, 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