Instrumenting Poor performance
Date: Thu, 13 Sep 2018 19:09:47 +0000
Message-ID: <SN1PR10MB03689BA25FEAAAD5AA73BBDFD81A0_at_SN1PR10MB0368.namprd10.prod.outlook.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-l
Received on Thu Sep 13 2018 - 21:09:47 CEST