Re: Tracing query inside procedure without 10046

If you really can't get the 10046 trace - which is the right way to do what you want - it's possible Tanel Poder's Snapper will help, particularly if you can run the process again.

Regards,

John Thomas

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

> 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