Re: Tracing query inside procedure without 10046

Hi Martin ,

we dont use the application log , but in my current state , application team came with sqlid involved in sub procedure ,which we worked on and settle the issue ,Thanks for your input , will try to explore this .

Also , I would like to thank everyone in this group as I explored a lot of things based on your input .

Regards,
Krishna

On Sun, 6 Aug 2023 at 19:06, Martin Berger <martin.a.berger_at_gmail.com> wrote:

> Hi Krishna,
>
> do you use an application log (as Jonathan suggested)?
> In this case, you can enable SQL TRACE (in worst case for the whole DB)
> and afterwards search for the inserts into this application log to give you
> the boundaries of your trace interests.
> With those details, you can crop <https://youtu.be/YrY7o8VpIOc>the part
> of interest out of your total trace data - and work from there?
>
> Even if you do not use an application log, you can enable DBMS_TRACE
> <https://dbaora.com/tracing-plsql-using-dbms_trace-oracle-database-11g-release-2-11-2/> in
> addition with SQL TRACE - you will again find the interesting boundaries in
> inserts into PLSQL_TRACE_%
> tables.
>
> Does this make any sense in this thread's context?
> Martin
>
>
> Am So., 6. Aug. 2023 um 10:10 Uhr schrieb Krishnaprasad Yadav <
> chrishna0007_at_gmail.com>:
>
>> Hi Jonathan,
>>
>> Your understanding is correct , we tried from
>> dba_hist_active_Sess_history but no luck , we could see the top sql
>> statement of procedure itself i,e exec proc.subproc .
>> since we didn't able to figure out sql_id involved in it , we tried to
>> explore the things
>>
>> Regards,
>> Krishna
>>
>> On Sat, 5 Aug 2023 at 22:54, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> My understanding so far is that the "application team" has identified a
>>> procedure that has been declared inside another procedure as the (higher
>>> level) source of poor performance in an overnight job. There is no
>>> indication of how they might have done this, but I think it would have to
>>> be through some application code that logs "starting subprocedure X /
>>> ending subprocedure X".
>>>
>>> The OP's problem then seems to be that although that can see ALL the
>>> activity relating to the MAIN procedure they have no way of identifying
>>> which bits of that activity happened in the course of the sub-procedure
>>> (any identification of top-level PL/SQL will refer only to the MAIN
>>> procedure).
>>>
>>> If my understanding is correct then the OP will only be able to get any
>>> useful information from the existing dba_hist_active_sess_history if the
>>> application team identifies and supplies the SQL_ID of any SQL embedded
>>> within the subprocedure, or tells the OP how they identified the
>>> subprocedure as the source of the problem and supplied the start and end
>>> times of any calls tot he subprocedure.
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>> On Sat, 5 Aug 2023 at 17:54, Mladen Gogala <gogala.mladen_at_gmail.com>
>>> wrote:
>>>
>>>> On 8/5/23 05:33, Lothar Flatz wrote:
>>>>
>>>> Hi,
>>>>
>>>> no. You can set a trap that would automatically trace a certain sql_id
>>>> when it executes.
>>>> E.g.
>>>> https://smarttechways.com/2018/12/10/trace-the-sql-query-with-sql-id-in-oracle/
>>>> Traces in Oracle are very complex, there are options to trace almost
>>>> everything you can imagine.
>>>> Stefan Köhler is an expert on it.
>>>>
>>>> It is also possible to turn on SQL Trace programmatically, through the
>>>> DBMS_MONITOR package. It would work even better if you use
>>>> DBMS_APPLICATION_INFO to set up client id and module id. You can then turn
>>>> on tracing for client_id or module_id. However, I am not sure what does the
>>>> OP want to do? Is the goal do determine procedure which starts the SQL or
>>>> is the purpose to determine which SQL is causing the problem? For the
>>>> latter, the application trace using DBMS_MONITOR is the Best Way (TM).
>>>>
>>>> --
>>>> Mladen Gogala
>>>> Database Consultant
>>>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>>>
>>>>
>
> --
> Martin Berger Oracle ♠
> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
> ^∆x http://berxblog.blogspot.com
>

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