Paresh, for help with a query performance issue you should post the SQL and
actual query plan in use by Oracle. Most often when query performance
drastically changes there has been a change in the plan due to a change in
statistics, bind variable peek, adaptive query plan, etc...
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of paresh.gandhi@xxxxxxxxx <paresh.gandhi@xxxxxxxxx>
Sent: Friday, August 17, 2018 12:47:36 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL performance issue
Dear all,
Could someone shed some light on this please?
Thanks
Paresh
On Tue, 14 Aug 2018, 15:48 Paresh Gandhi,
<paresh.gandhi@xxxxxxxxx<mailto:paresh.gandhi@xxxxxxxxx>> wrote:
Hello,
I am stuck against a performance issue that was reported recently.
The issue I am currently looking at is about a job that takes 1 hour as opposed
to 20 minutes in the past. This is happening since we switched over from
primary to secondary site. Both sites are two-node rac databases. On primary
site this used to take 20 mins. We also ran this in pre-prod environment and it
takes roughly 20 mins.
I had taken a look at the AWR report from both pre-prod and PROD databases.
There is no single query that is taking long. However, there is a query that
runs for 13,000 times and is taking 0.23 seconds per execution as opposed to
0.05 against pre-prod. This explains the additional time taken in PROD (i.e.
new PROD). Please note that the plan hash value for the SQL_ID in both
environments is exactly the same.
Top wait events in PROD suggests high DB CPU and cluster related wait events.
So I suspect there is something down to getting blocks from the resource master
instance for the blocks in question. Please could someone help?
Database version is 11.2.0.4.
All the details are listed in the attached files.
I would greatly appreciate if you could share your views with me.
Many thanks
Paresh
DXC - This is a PRIVATE message - If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to bind
the Company to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail for
such purpose.
