Re: Instrumenting Poor performance

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Fri, 14 Sep 2018 06:52:00 +0000
Message-ID: <AM0PR04MB4436117B74E7B9F6DA345AA3A1190_at_AM0PR04MB4436.eurprd04.prod.outlook.com>

Sql plan hash does not include the predicates so that’s one niche consideration to look out for. AWR doesn’t hold predicates so you will need to wait until you can pull plans from memory via dbms_xplan.display_cursor to see that. It’s possible but unusual.

Real time sql monitoring can you tell you where the time was spent but if you no longer have that available for bad execution then you can get a very rough approximation from ASH raw data.

Full sql trace is best to see what you were doing but ASH is usually good enough.

A combination of wait events (e.g runtime decisions about switching from buffered to direct io) and the detail of what you are waiting on exactly (e.g reading lots of undo) might be sufficient to give pointers.

Otherwise you might need the combination of ash/tracing (what) and snapping session statistics (why) to give a better picture.

Regards
Dominic

Sent from my iPhone

On 13 Sep 2018, at 21:47, Upendra nerilla <nupendra_at_hotmail.com<mailto:nupendra_at_hotmail.com>> wrote:

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 Fri Sep 14 2018 - 08:52:00 CEST