RE: Tracing query inside procedure without 10046

And what's the reasoning behind at least not trying trace/Method-R?

Clay Jackson
Database Solutions Sales Engineer
[cid:image001.jpg_at_01D9C6CB.0FFA1A50]<https://www.quest.com/solutions/database-performance-monitoring/> clay.jackson_at_quest.com<mailto:clay.jackson_at_quest.com> office 949-754-1203 mobile 425-802-9603

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mladen Gogala Sent: Friday, August 4, 2023 11:31 AM
To: oracle-l_at_freelists.org
Subject: Re: Tracing query inside procedure without 10046

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

On 8/1/23 01:25, Krishnaprasad Yadav wrote: 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 .

Well, there are two known application tuning methods: Method-R (my personal preference) and Method-C, based on the buffer cache hit ratio (BCHR). Method-R is based on the 10046 trace. Since you don't want that, the only thing you can fall back to is BCHR witchcraft. You should also collect 4-leaved clover, mandrake, black cats and similar, should you decide to use BCHR. DBMS_HPROF can tell you how much time is your package spending in which procedure, but it will inevitably leave out event based stuff. AWR reports can help with some of that, but, essentially, there is no substitution for SQL trace.

--

Mladen Gogala

Database Consultant

Tel: (347) 321-1217

https://dbwhisperer.wordpress.com<https://dbwhisperer.wordpress.com/>






--
http://www.freelists.org/webpage/oracle-l






(image/jpeg attachment: image001.jpg)