Re: SQL performance issue

Hi Paresh,
If you want to find the root cause of the sql performance i suggest you provide :

++++++++++++++++

1. the AWR report from both executions ( good and bad)
2. SQLT XTRACT from both sql ids.

Please , for this sql id , provide the output from the SQLT utility, using the XTRACT method (it is important use this method for this issue) for: --- The XTRACT method will take the SQLID of the problem SQL as input and will *NOT* execute the SQL.
--- For information on obtaining and using SQLT, please refer to: SQLT Usage Instructions (Doc ID 1614107.1)

For example the following file is from a successful SQLT run using the XTRACT method:
sqlt_s45774_xtract_fp48hh5dkm529.zip

++++++++++++++++

If you can open a SR then that it would be easier for support to assist you ;)

Rgds,
Sachin Pawar
https://twitter.com/sach_pwr

On Fri, Aug 17, 2018 at 1:33 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> 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>
> <oracle-l-bounce_at_freelists.org> on behalf of paresh.gandhi_at_gmail.com
> <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> 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