Re: Tracing query inside procedure without 10046

David,

I think there was a time when the line numbers reported by hprof didn't agree with the line numbers reported by user_source because of pl/sql optimization moving lines around (constant folding, moving assigments out of loops etc.) Have you come across that problem at all in recent versions.

Regards
Jonathan Lewis

On Tue, 1 Aug 2023 at 11:19, David Kurtz <info_at_go-faster.co.uk> wrote:

> The hierarchical profiler will link PL/SQL line number to Sqlid, and
> profile time.
> You can join it to ASH data to profile DB time, including profile by line
> in the execution plan
>
> See also
>
> https://blog.go-faster.co.uk/2023/04/investigating-unfamiliar-plsql-with.html
> https://www.go-faster.co.uk/p/tuning-with-plsql-performance-profiler.html
>
>
>
>
> David Kurtz
> Go-Faster Consultancy
> www.go-faster.co.uk
> Tel: +44 7771 760660
> (sent from a mobile device, typos and brevity to be expected)
>
> On Tue, 1 Aug 2023, 08:48 Krishnaprasad Yadav, <chrishna0007_at_gmail.com>
> wrote:
>
>> Hi Stefan,
>>
>> I am looking for live as well as post-problem analysis . I have never
>> used DBMS_HPROF , but I am exploring this. Thanks for letting me know about
>> this !! , but using this, does it generate any additional load in a
>> highly concurrent system .
>>
>> For post-problem any further way to take up .
>>
>> Regards,
>> Krishna
>>
>>
>> On Tue, 1 Aug 2023 at 13:02, Stefan Koehler <contact_at_soocs.de> wrote:
>>
>>> Hello Krishna,
>>> are you talking about live analysis or about post-problem analysis?
>>>
>>> If you mean live, you can also use the PL/SQL hierarchical profiler
>>> (DBMS_HPROF). It will show you exactly how much time is spent in your
>>> PL/SQL code or with SQL execution.
>>>
>>> Best Regards
>>> Stefan Koehler
>>>
>>> Independent Oracle performance consultant and researcher
>>> Website: www.soocs.de
>>> Twitter: _at_OracleSK
>>>
>>> > Krishnaprasad Yadav <chrishna0007_at_gmail.com> hat am 01.08.2023 07:25
>>> CEST geschrieben:
>>> >
>>> > 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