Home -> Mailing Lists -> Oracle-L -> Re: Tracing query inside procedure without 10046

Re: Tracing query inside procedure without 10046

From: William Robertson <william_at_williamrobertson.net>
Date: Thu, 3 Aug 2023 19:41:42 +0100
Message-Id: <A1C373FD-47B0-45FC-814A-228197B1B9EE_at_williamrobertson.net> 




You might also use PL/Scope to capture all statements from a code unit (requires Oracle 12.2), e.g.

alter package x compile body plscope_settings = 'STATEMENTS_ALL';

Then query user_statements. 

William 

Sent from my iPhone

On 1 Aug 2023, at 18:54, 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




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


Received on Thu Aug 03 2023 - 20:41:42 CEST












Original text of this message