Re: SQL performance in prod

Stefan,

I had one too many:  hardware changes could not affect execution plans.  I added that to pad the list without much thought just before pressing SEND, and of course regretted it two seconds later.

There are certainly more items to add, but I started with the easily-verifiable stuff.

Clearly a 10053 trace is the ultimate, but as the OP noted, it requires prescience to be set before it is needed, and prescience isn't always available.

10053 trace output is also not easy to read.  Reading two such traces, comprehending both, and then comparing and contrasting usually requires intelligence and attention to detail approaching the level of Wolfgang Breitling.

Thanks!

-Tim

On 7/18/18 21:03, Stefan Knecht wrote:
> 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
> <mailto: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 <http://zztat.net/> | _at_zztat_oracle |
> fb.me/zztat <http://fb.me/zztat> | zztat.net/blog/
> <http://zztat.net/blog/>

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