Re: Extended database statistics

Hi,

For any version of eBS, optimizer_adaptive_features=FALSE and _optimizer_autostats_job=FALSE are 2 mandatory parameters. Setting them to FALSE will also ensure the extended statistics based on directives will not be collected (as that functionality is disabled).

Theoretically, the best could be to delete all statistics and directives from this DB and then collect fresh statistics by using the "Gather % Stats" concurrent programs.
The bad news is that some of the queries may actually benefit from the additional statistics you already have, and the performance may degrade after they are wiped.

regards,

---
Maris Elsins
_at_MarisElsins <https://twitter.com/MarisElsins>
www.facebook.com/maris.elsins



On Fri, Feb 24, 2017 at 5:09 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk


> wrote:




>

> Since you're on 12.1.0.2 it might be the last stage of:

> http://jonathanlewis.wordpress.com/2016/08/02/adaptive-mayhem/

>

> Regards


> Jonathan Lewis

>

> ________________________________________

> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on

> behalf of Hameed, Amir <Amir.Hameed_at_xerox.com>

> Sent: 24 February 2017 14:55:07

> To: oracle-l_at_freelists.org

> Subject: Extended database statistics

>

> Hi,

> The Oracle database version is 12c (12.1.0.2). The application is Oracle

> E-Business Suite.

> While working on a performance issue, we discovered that there were a lot

> of extended statistics present in the database for standard EBS tables. We

> do not create extended statistics on any table as part of our statistics

> gathering process. The following parameters were set to TRUE:

> optimizer_adaptive_features

> optimizer_adaptive_reporting_only

>

> I am trying to understand the following:

>

> 1.       Does Oracle collect extended statistics automatically if the

> optimizer_adaptive_features parameter is set to TRUE?

>

> 2.       Is there a way to find out from the explain plan if extended

> statistics were used in the plan?

>

>

> Thank you,

> Amir

> --

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

>

>

>


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