Re: Instrumenting Poor performance
Date: Tue, 18 Sep 2018 01:21:45 +0000
Message-ID: <SN1PR10MB03686A4857341F8A1AFDED13D81D0_at_SN1PR10MB0368.namprd10.prod.outlook.com>
We had tried to restore the database a few minutes prior to the issue occurring to a lower environment, we were unable to reproduce it. Today we are going to go through another refresh right before this query runs, hopefully we'll catch it in-time..
Thanks
From: Glauber, Leo <Leo.Glauber_at_sodexo.com> Sent: Monday, September 17, 2018 7:53 AM To: mwf_at_rsiz.com; nupendra_at_hotmail.com; 'Martin Berger' Cc: 'Oracle-L oracle-l'
Subject: RE: Instrumenting Poor performance
Do you have the ability to restore the production database to a non-prod environment for a timeframe when this occurs?
-Leo
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mark W. Farnham Sent: Saturday, September 15, 2018 7:37 AM To: nupendra_at_hotmail.com; 'Martin Berger' <martin.a.berger_at_gmail.com> Cc: 'Oracle-L oracle-l' <oracle-l_at_freelists.org> 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> [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..
- 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
This e-mail, attachments included, is confidential. It is intended solely for the addressees. If you are not an intended recipient, any use, copy or diffusion, even partial of this message is prohibited. Please delete it and notify the sender immediately. Since the integrity of this message cannot be guaranteed on the Internet, SODEXO cannot therefore be considered liable for its content.
Ce message, pieces jointes incluses, est confidentiel. Il est etabli a l'attention exclusive de ses destinataires. Si vous n'etes pas un destinataire, toute utilisation, copie ou diffusion, meme partielle de ce message est interdite. Merci de le detruire et d'en avertir immediatement l'expediteur. L'integrite de ce message ne pouvant etre garantie sur Internet, SODEXO ne peut etre tenu responsable de son contenu.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 18 2018 - 03:21:45 CEST