Re: SQL performance in prod

Tim, you forgot one:

7. The fact whether it rains Monday morning or not

The original anecdote referred to the fact that if it rained, a certain employee that normally arrives first on a sunny day, would get to the office later - which caused a different employee to first trigger execution plan creation, with different bind variables, leading to a different plan.

So the query would run fast all day on a sunny day, but slow all day when it rained.

Venky - try looking at the values of the bind variables of a good run vs a bad run.

On Thu, Jul 19, 2018 at 5:58 AM, 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
>
>
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

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