Re: Tracing query inside procedure without 10046

Hi Jonathan ,

Currently I was trying to figure out queries involved in procedure.subprocedure .

So , actually one of the JOB(kind of Day end job ) of customer was running slow , so my intention was to get sql's from the procedure for evaluating their performance (just to rule out kind of plan change) , now in this case procedure which was taking time was part of other procedure , so i tried to get details from ashrpti.sql but output was of same procedure name (i.e sqlid/statement was showing the same name i.e proc.subproc) which i was passing input for ashrpti , since it runs in night time , it went unnoticed by application team and post completion of job , team realize execution time is increased and hence , 10046 was out from the option .
Hence was trying to figure out any option by which we can get sql's details related to subprocedure

Regards,
Krishna

On Tue, 1 Aug 2023 at 22:49, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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