Is the application team the group that wrote the code, or does it just
handle some processing on behalf of the end-users? If they wrote the PL/SQL
ask them for the text of any SQL statements executed by the procedure
they've specified and a rough estimate of how many times each statement
should run, then search the AWR reports (SQL ordered by ...) for
identifiable bits of the statement, or search v$sql for identifiable bits.
If the application team can't do this then ask them what options they have
for enabling any diagnostic output as the critical procedure starts
executing. You could also ask them about the code layer that gets called
that results in this procedure being executed, because you might be able to
find the starting call in the AWR and dynamic performance views and work
from there.
Regards
Jonathan Lewis
On Fri, 4 Aug 2023 at 11:22, Krishnaprasad Yadav <chrishna0007@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxx> 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: @OracleSK
Krishnaprasad Yadav <chrishna0007@xxxxxxxxx> hat am 01.08.202307:25 CEST geschrieben:
procedure
Dear Gurus,
currently working on slowness issue of procedure which is part of
like : proc_name.subproc_name , over here i need to figure out thequery related to subproc_name and not by using 10046 trace .
I tried ashrpti.sql and used pls_object_entry but I didn't get anyvalues in the report .
Is there any way to figure out queries related to suchsubproc_name also tried from dba_hist* views too by providing top_sql
/pls_object values but no luck .
procedure verified queries and by recommendation on it . but it was
Currently in my situation trace was taken (10046) and from
challenging to figure out queries without tracing.
It would be great help if you provide any light on it .
Regards,
Krishn
