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> 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-l