RE: Extended database statistics

Regarding whether or not you can see from the plan whether extended stats helped you arrive at that plan, I’m not aware of anything short of a Wolfgang trace that shows you what the CBO process was in arriving at the plan.  

It would probably be difficult to remove the extended stat columns and re-run the exact query again to see if you obtained a different plan without them.  

And if you’ve got adaptive features activated, good luck telling WHY the plan changed anyway. I think it checks the race results at Las Vegas. (Not really. But right now there are more factors going on than I can keep track of and I’m not even sure which factors are taking place that we cannot see. If there is an event like the Wolfgang trace that can cause “adaptive” to spit out something when it makes a change and tells why that might be helpful. I’m not sure whether adaptive changes are reported in a Millsap trace. I have never seen them there, but not seeing evidence is not the same as not happening.)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alfredo Abate Sent: Friday, February 24, 2017 12:54 PM To: Amir.Hameed_at_xerox.com
Cc: Maris Elsins; tim.evdbt_at_gmail.com; oracle-l_at_freelists.org Subject: 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] 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 Received on Fri Feb 24 2017 - 21:55:09 CET