Re: Tracing query inside procedure without 10046

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