Re: Instrumenting Poor performance

to compile sql_trace files, you can use tools like tkprof
orasrp <http://www.oracledba.ru/orasrp/> TVD$XTAT <https://antognini.ch/2008/10/introduce-tvdxtat/> Method-R Profiler <https://method-r.com/software/workbench/> there are others also.
This should give you a good starting point where and why time is spent sometimes.

hth,
berx

Am Do., 13. Sep. 2018 um 22:04 Uhr schrieb Upendra nerilla < nupendra_at_hotmail.com>:

> is it always the same SQL_ID?
> You an enable sql_trace for this particular SQL:
> alter system set events 'sql_trace[sql: <SQL_ID>] level=12';
>
> It is always the same sql_id. We have a 10046 and 10053 trace for the good
> execution.. Waiting for the next poor execution to capture the traces
> again..
>
>
> I assume EBS can enable tracing for specific activities, but I'm not close
> to EBS, but maybe there are better solutions than simple sql_trace.
>
> I am not an EBS guy, so I am at a loss here. :(
>
>
> Do you have proper facility to create a profile based on these sql_trace
> files?
>
> There is already a sql_profile created for the sql_id. Is that what you
> mean?
>
>
> ------------------------------
> *From:* Martin Berger <martin.a.berger_at_gmail.com>
> *Sent:* Thursday, September 13, 2018 3:52 PM
> *To:* nupendra_at_hotmail.com
> *Cc:* Oracle-L oracle-l
> *Subject:* Re: Instrumenting Poor performance
>
> Hi Upendra,
>
> is it always the same SQL_ID?
> You an enable sql_trace for this particular SQL:
> alter system set events 'sql_trace[sql: <SQL_ID>] level=12';
>
>
> I assume EBS can enable tracing for specific activities, but I'm not close
> to EBS, but maybe there are better solutions than simple sql_trace.
>
> With the traces of different "good" and "bad" and see where the time is
> spent.
>
> Do you have proper facility to create a profile based on these sql_trace
> files?
>
> br,
> berx
>
>
>
> Am Do., 13. Sep. 2018 um 21:11 Uhr schrieb Upendra nerilla <
> nupendra_at_hotmail.com>:
>
> Hello Team -
> We have an EBS application which is running EBS and several other modules
> (OTC). From time to time I see a particular query giving poor response
> time. Normal response is <4 mins and during the poor execution, it goes
> over an hour.
>
> We have a SQL baseline created for that SQL_ID and forcing the optimal
> plan. We are able to see that optimizer is using the same hash. There
> are no host resource (cpu/memory/io) constraints.. This job runs only a
> couple of times a day, very time sensitive for the business. We are unable
> to reproduce this at lower environments even when we performed refreshes..
>
> We see a pattern that this issue shows up during the first week of the
> month and disappears after a couple of days (not the same exact day).
> Here is what I am thinking of gathering - high level info..
>
> 1. Gather daily row count of all the tables involved in the query..
> 2. Review the jobs and see if there is anything unique..
>
> What else could I gather to troubleshoot this further?
> Appreciate any insights..
>
> Thank you
> -Upendra
>
>

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