Re: Instrumenting Poor performance
Date: Fri, 14 Sep 2018 02:54:59 +0000
Message-ID: <SN1PR10MB03681FD61D7A7407CBF87F9CD8190_at_SN1PR10MB0368.namprd10.prod.outlook.com>
Thanks Martin. That helps..
I have used orasrp and TVD$XTAT, both are great.
I can generate the trace only when the performance issue pops up in a couple of weeks..
Wondering if there is anything else that I could look into while waiting?
From: Martin Berger <martin.a.berger_at_gmail.com> Sent: Thursday, September 13, 2018 4:37 PM To: nupendra_at_hotmail.com
Cc: Oracle-L oracle-l
Subject: 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<mailto: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<mailto:martin.a.berger_at_gmail.com>> Sent: Thursday, September 13, 2018 3:52 PM To: nupendra_at_hotmail.com<mailto: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<mailto: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..
- Gather daily row count of all the tables involved in the query..
- 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-lReceived on Fri Sep 14 2018 - 04:54:59 CEST