Re: Tracing query inside procedure without 10046

It is easy enough to unwrap the code with the Salvisberg unwrapper in SQL developer even if only for inspection purposes. Whether you change the code is another matter.

The hierarchical profiler will let you relate elapsed execution time to pl/SQL line number to Sqlid.

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 Fri, 4 Aug 2023, 11:23 Krishnaprasad Yadav, <chrishna0007_at_gmail.com> wrote:

> Hi Jonathan,
>
> Code is wrapped and procedure name is provided by application team, since
> these code runs every day ,Intention is to find the SQLID
> related to that procedure
>
> Regards ,Krishna
>
> On Fri, 4 Aug, 2023, 02:12 Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:
>
>>
>> From what you're saying it seems you know that there is a procedure
>> (embedded in another procedure) that executes SQL and you want access to
>> the SQL text to test. Is there something stopping you from reading the
>> source of the procedure?
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Tue, 1 Aug 2023 at 18:52, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
>> wrote:
>>
>>> 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