Re: SQL performance in prod

Hi All,

Well experts has already given the valuable inputs, below are my 2 cents

It seems to me case of following :

• There are many histograms on the underlying table columns - Also tables have quite large number of indexes like ind(a), ind(a, b), ind(b) where are a b are column names . It itself is wrong strategy but yes it happens over the time - Since there are many histograms and it is finding corresponding indexes too, it is picking some other index each time thus flipping of plan and so poor performance

Histogram Creation is controlled by method_opt parameter while stats gathering
It is generally taken as default and in most cases it is " FOR ALL COLUMNS SIZE AUTO" which is default database setting For Oracle own product "Oracle E-business suite" it is taken as "FOR ALL COLUMNS SIZE 1" means no histogram by default, which is best strategy for any application

*Solution*

You can try gathering the stats on all the tables by following exec dbms_stats.gather_table_stats('TABNAME', method_opt=>' FOR ALL COLUMNS SIZE1 ' ); I hope it will give you plan stability and better run timings

Warm Regards,
Harmandeep Singh

On Thu, Jul 19, 2018 at 4:01 AM V Raman <vraman4list_at_gmail.com> 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