Re: Instrumenting Poor performance
Date: Mon, 17 Sep 2018 21:32:44 +0000
Message-ID: <SN1PR10MB0368BD4E754C44904E4D534FD81E0_at_SN1PR10MB0368.namprd10.prod.outlook.com>
Jonathan,
Thank you so much for pointing out the potentially conflicting update.. I do see a large number of "db_sequential_read" for a single table which might be the root cause.. I will look into identifying which other process may be the culprit updating that table..
I am going through the saved AWR baseline (during the poor execution) and see if I could find anything..
-Upendra
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Sent: Friday, September 14, 2018 3:28 AM To: Martin Berger; nupendra_at_hotmail.com Cc: Oracle-L oracle-l
Subject: Re: Instrumenting Poor performance
Are you licensed to for the diagnostic and performance packs. If not are you running statspack against the database ?
In either case the (AWR/Statspack) history will probably have captured a copy of the statement when it was running badly, so you should have a few execution stats for the bad run. If it's in the AWR and you have enterprise manager you can access this through the GUI, if not the you can run awrsqrpt.sql (or sprepsql.sql) for a text report of the SQL for the snapshot ids.
The execution stats in the report may give you some clues, e.g. based on the headings in the stats section:
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets:
Disk Reads:
Rows processed:
CPU Time(s/ms):
Elapsed Time(s/ms):
Sorts:
Parse Calls:
Invalidations:
Version count:
Sharable Mem(K):
Executions:
Is the number of disk reads much larger, or it is about the same with some extra CPU time and a much larger elapsed time The latter might simply mean a good run is well cached, a bad run is badly cached.
Is the number of buffer gets about the same, or massively increased with a huge increase in buffer gets with an increase in disk reads - if the plan is DEFINITELY unchanged this would be a hint that a conflicting update was running at the same time and you spent a lot of time generating read-consistent images.
Get the report for a few sessions with good run times so that you have a handle on the possible workload and variation on a good run.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Upendra nerilla <nupendra_at_hotmail.com> Sent: 14 September 2018 03:54:59
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> 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 Mon Sep 17 2018 - 23:32:44 CEST