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@xxxxxxxxx> 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
