Re: SQL performance in prod

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

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