Re: Instrumenting Poor performance

From: Upendra nerilla <nupendra_at_hotmail.com>
Date: Tue, 18 Sep 2018 00:33:42 +0000
Message-ID: <SN1PR10MB036834331B32C708A06F8C82D81D0_at_SN1PR10MB0368.namprd10.prod.outlook.com>

Thanks Mark for the suggestion..

It is possible that there may be other jobs impacted by concurrent manager queue.. but In this scenario, we are looking purely at a SQL execution bypassing the concurrent manager etc..

---

From: Mark W. Farnham <mwf_at_rsiz.com>
Sent: Saturday, September 15, 2018 7:37 AM To: nupendra_at_hotmail.com; 'Martin Berger' Cc: 'Oracle-L oracle-l'
Subject: RE: Instrumenting Poor performance

If this is a concurrent manager job pair it up scheduling another to start first such that the new job just queries the ccmgr tables to see if the original job is finished. If the original job hasn’t started, wait and check again (perhaps a minute in this case). If the original job finishes, have the new job quit. If the original job is running for more than the amount of time (current clock minus started > 4 minutes in this case if I understand you correctly) then turn on trace for the original job, perhaps also query a few counts on the component tables and the stats of the tables and then quit.

I’m presuming here that this is NOT part of a month end pile up on jobs starting in the first place, for example due to queue limits. Or someone bumping queue limits unrealistically high so lots of jobs are running slowly because the system is overloaded. I suppose the new job would count how many times it waited for the original job to start. Just make sure the new job starts before the original job.

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Upendra nerilla Sent: Thursday, September 13, 2018 10:55 PM To: Martin Berger
Cc: Oracle-L oracle-l
Subject: Re: Instrumenting Poor performance

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<mailto:martin.a.berger_at_gmail.com>> Sent: Thursday, September 13, 2018 4:37 PM To: nupendra_at_hotmail.com<mailto: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..

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

Received on Tue Sep 18 2018 - 02:33:42 CEST