Re: Extended database statistics

Amir,

Both Tim and Maris have given you excellent advice already. The only other thing I will mention is to review MOS doc # 396009.1 to make sure you have the database initialization parameters set accordingly for EBS.

The parameter you mentioned, OPTIMIZER_ADAPTIVE_FEATURES, is one of these parameters on the list that needs to be set to FALSE.

Alfredo

On Fri, Feb 24, 2017 at 9:36 AM, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

> We found thousands of extended statistics on standard tables. According to
> Oracle, they are collected if OPTIMIZER_ADAPTIVE_FEATURES is set to TRUE
> and this is what I want to validate.
>
> Also, if there a way to find out from an explain plan if extended
> statistics were used for the pan?
>
>
>
> *From:* Maris Elsins [mailto:elmaris_at_gmail.com]
> *Sent:* Friday, February 24, 2017 10:23 AM
> *To:* tim.evdbt_at_gmail.com
> *Cc:* Hameed, Amir <Amir.Hameed_at_xerox.com>; oracle-l_at_freelists.org
>
> *Subject:* Re: Extended database statistics
>
>
>
> If I remember right, then the column sets that FND_STATS collect the
> extended stats for are defined in FND_EXTNSTATS_COLS (for eBS).
>
> If you have more extended stats than defined in this table, then there's
> something else collecting them too.
>
>
>
>
>
>
> ---
>
> Maris Elsins
>
> _at_MarisElsins <https://twitter.com/MarisElsins>
>
> www.facebook.com/maris.elsins
>
>
>
>
>
>
>
> On Fri, Feb 24, 2017 at 5:16 PM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:
>
> Amir,
>
> In releases of EBS prior to R12.2, patching to FND_STATS might be
> necessary to make access to extended statistics available, according to
> this support white paper
> <https://support.oracle.com/epmos/main/downloadattachmentprocessor?attachid=1586374.1%3ADOC1586374_A&docType=WHITE%20PAPER&action=download>
> .
>
> If those patches have not been applied to FND_STATS, then you can
> eliminate EBS from your research.
>
> Hope this helps...
>
> -Tim
>
>
>
>
> On 2/24/17 08:05, Hameed, Amir wrote:
>
> Thanks Tim.
>
> The EBS version is 12.1.3.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org <oracle-l-bounce_at_freelists.org>] *On Behalf Of *Tim Gorman
> *Sent:* Friday, February 24, 2017 10:02 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Extended database statistics
>
>
>
> Amir,
>
> The version of EBS is important to share as well, but since the underlying
> database is 12c, I'm guessing it likely that EBS is R12.2.x?
>
> If so, please be aware that the FND_STATS package in EBS R12.2 supports
> the gathering of extended statistics
> <https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T174306.htm>,
> and you may want to check the settings in use to determine if the presence
> of the extended statistics is caused by EBS settings, rather than something
> in the RDBMS parameters?
>
> Hope this helps...
>
> -Tim
>
> On 2/24/17 07:55, Hameed, Amir wrote:
>
> 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