Re: Tracing query inside procedure without 10046

Hi Krishna,

How historic are you talking? The information should hang around in v$sql for quite a while. Otherwise, you can call the procedure again and query v$sql to get a list of all the sql_id’s you care about and use them as filters against v$active_session_history.

That said, I’d be a little concerned that you’ve made some assumptions that have lead to the wrong subprocedure being the target of your investigation. Generating a 10046 trace of the entire process that you care about and using tkprof to summarize it is probably way you should be doing it. You can then look up the relevant named pl/sql program based on the tkprof investigation. Ie use the trace to find where the problem is, rather than try to find a problem in a certain place.

Thanks,
Andy

On Mon, Jul 31, 2023 at 11:05 PM, Krishnaprasad Yadav < chrishna0007_at_gmail.com> wrote:

> 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