Re: SQL performance in prod

From: V Raman <vraman4list_at_gmail.com>
Date: Wed, 18 Jul 2018 22:21:01 -0500
Message-ID: <CAGAYmOAMuHv8g+YF23=vJqo=V5VQiv0xTKioJqB5nvjnGrRUzQ_at_mail.gmail.com>



Thanks a lot Tim and everyone else.

Tim: 1-5 no change. Although i am not 100% sure about #4.

Since this is v12 there is the default stats job every night; adaptive features turned off fully. I will have to dig to see if the stats change made an effect.

Venky.

On Wed, Jul 18, 2018 at 5:58 PM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> Venky,
>
> "Assuming there is not much change in the DB"
>
> Let's narrow down the things that can change an execution plan...
>
>
> 1. hardware change (i.e. #-cpus, # GB of RAM, storage, etc)
> 2. application software change (i.e. change to the SQL text)
> 3. Oracle software change (i.e. patch, upgrade, etc)
> 4. initialization parameter change
> 5. gathering system statistics
> 6. gathering table, index, column statistics
>
>
> When you state the assumption about "no much change in the DB", I am
> assuming that you're discussing items #1-4.
>
> How about item #5? Can you query the SYS.AUX_STATS$ table and display the
> column PVAL1 where PNAME has the value "DSTART" or "DSTOP"?
>
> How about item #6? Can you display the contents of DBA_TAB_STATS_HISTORY
> for the tables involved in the query? Please refer to the useful blog
> posts by Uwe Hesse HERE
> <https://uhesse.com/2012/04/23/diff_table_stats_in_history-example/> and
> by Marcel-Jan Krijgsman HERE
> <https://mjsoracleblog.wordpress.com/2013/02/19/more-statistics-history/>
> for more information, if necessary?
>
> Hope this helps?
>
> Thanks!
>
> -Tim
>
>
>
>
> On 7/18/18 15:30, V Raman wrote:
>
> List
>
> We have a SQL that is performing intermittently bad in our prod env. The
> good ones take 2 to 5 mins, the bad ones run for hours we kill them. They
> run fine in the non prod env. I ran an awsqrpt and based on that I see that
> there are a few executions with the bad ones taking hours. Looking at the
> differences in the execution plan, the good ones have lots of nested loops
> in them, with the bad ones having lots of hash joins.
>
> I am trying to figure out the cause(s). Assuming there is not much change
> in the DB, the first thing that comes to mind is statistics. Can the
> listers help with ideas? Thanks.
>
> If anyone is interested is seeing the report, i can provide a link to them
> by email.
>
> Venky
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 19 2018 - 05:21:01 CEST

Original text of this message