Re: SQL performance issue
Date: Sat, 18 Aug 2018 09:31:30 +0000
Message-ID: <MMXP12301MB1598AE4BA695826D6CF9E04DA53C0_at_MMXP12301MB1598.GBRP123.PROD.OUTLOOK.COM>
"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."
Is that really 13,000 different queries or just the one query repeated 13,000 times ? Unless you're using literals instead of binds that's "one single query". If it showed up in the AWR report then there will be detailed stats for it for the interval - you can get them graphically, but I tend to use the script awrsqrpt.sql (in $ORACLE_HOME/rdbms/admin). If the query shows up in a good and a bad AWR on production then you can run of the report for that SQL_ID for a good period and a bad period and part of the report will tell you specifically about the most significant work done by the query.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of paresh.gandhi_at_gmail.com <paresh.gandhi_at_gmail.com> Sent: 17 August 2018 17:47
To: oracle-l_at_freelists.org
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_at_gmail.com<mailto:paresh.gandhi_at_gmail.com>> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Aug 18 2018 - 11:31:30 CEST