Re: SQL performance issue

Paresh,

As another suggestion, please think more about the appropriateness of using an AWR report for tuning a specific process?

An AWR report consists of information gathered for a database instance or all the instances in a RAC cluster, which is an enormous amount of information pertaining to many processes.  You are trying to tune a specific process, so you have already arrived at the endpoint of AWR analysis, which is identifying a SQL statement or process within an instance or cluster, and you are ready to begin the next step by examining the process in detail using SQL tracing.

So please consider learning more about SQL tracing and TKPROF (and similar utilities), about which numerous books, blog post articles, and Oracle documentation has been written.  AWR reports will not provide much assistance to you for this task.

The other consideration is that this email list is not Oracle Support.  If you or your company has Oracle licenses, then you have the resources of Oracle Support to assist you.  They have an instructive process for resolving such cases.  There is evidently urgency implied in your emails to this list, and if there is indeed urgency for resolving this situation, then working this case with Oracle Support is the most responsible thing for you to do.  This list will be useful for helping you understand what Oracle Support asks you to do, but we are not your support organization.

Hope this helps...

-Tim

On 8/17/18 09:59, Powell, Mark wrote:
>
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org>
> on behalf of paresh.gandhi_at_gmail.com <paresh.gandhi_at_gmail.com>
> *Sent:* Friday, August 17, 2018 12:47:36 PM
> *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
>
>
>
>
> 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.

--
http://www.freelists.org/webpage/oracle-l