Turn everything adaptive off, and then drop them one last time?
This is unlikely to be the never found yet silver bullet, but it *might* be
a good idea for EBS where they have a very particularly specific stats
collection routine as mentioned by Tim much earlier, and only applying the
specific extended stats that FND_STATS patched dictates or that you
specifically research as beneficial to your specific data texture in EBS.
Because of the power and flexibility of the flexfield structures and the
variability of charts of accounts and master item lists (as two examples),
the general case optimization of EBS is probably intractable. A good
starting point is what they have provided and then augmented by knowing your
data "texture" and troubleshooting anything that seems to consume
significantly more resources than it should consistently.
Particularly in EBS this is distinctly NOT usually the same as tuning the
most expensive thing you have. Usually in EBS that is a mainline job part
and parcel of the basic major functioning as general ledger, manufacturing,
and so forth. Your mileage may vary, but often you'll find that while
remaining expensive there is no fat on the bones of these processes.
Of course the biggest single thing remains that workflow and the
accumulation of CCMGR job logs has never been purged. THAT is pure fat
easily rendered.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hameed, Amir
Sent: Friday, February 24, 2017 4:21 PM
To: Jonathan Lewis; alfredo.abate@xxxxxxxxx; mwf@xxxxxxxx
Cc: 'Maris Elsins'; tim.evdbt@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Extended database statistics
The statement in question belonged to the auto-invoice job. It statement was
initially using INDEX_N6 and was taking over 30 minutes to complete. The row
set operation from TKP showed that it was scanning over 155m rows from
NDEX_N6. I forced it to use INDEX_N7 via a hint and it finished within a
second. Because it was a standard EBS code, we went to Oracle and asked that
we need this statement optimized. After doing further analysis, it was
determined that extended statistics existed on the underlying table; there
were 10 of them. After we dropped those extended statistics, the statement
automatically started using INDEX_N7. So, the extended statistics were
messing up the execution plan.
There are currently a total of over 4500 extended statistics on the standard
tables. I was hoping if there was a way to either disable these extended
statistics instead of dropping them but I am not sure if they can be
disabled.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] ;
Sent: Friday, February 24, 2017 4:01 PM
To: alfredo.abate@xxxxxxxxx; Hameed, Amir <Amir.Hameed@xxxxxxxxx>;
mwf@xxxxxxxx
Cc: 'Maris Elsins' <elmaris@xxxxxxxxx>; tim.evdbt@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: Re: Extended database statistics
If you have the 10132 trace - or pull the plan from memory with the
other_xml - there's information about the number (and nothing else) of sql
plan directives viewed and used; but as soon as a directive is replaced by a
column group information the directive is no longer counted.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Mark W. Farnham <mwf@xxxxxxxx>
Sent: 24 February 2017 20:55:09
To: alfredo.abate@xxxxxxxxx; Amir.Hameed@xxxxxxxxx
Cc: 'Maris Elsins'; tim.evdbt@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Alfredo Abate
Sent: Friday, February 24, 2017 12:54 PM
To: Amir.Hameed@xxxxxxxxx
Cc: Maris Elsins; tim.evdbt@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>> 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@xxxxxxxxx<mailto:elmaris@xxxxxxxxx>]
Sent: Friday, February 24, 2017 10:23 AM
To: tim.evdbt@xxxxxxxxx<mailto:tim.evdbt@xxxxxxxxx>
Cc: Hameed, Amir <Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
@MarisElsins<https://twitter.com/MarisElsins>
www.facebook.com/maris.elsins<https://www.facebook.com/maris.elsins>
On Fri, Feb 24, 2017 at 5:16 PM, Tim Gorman
<tim.evdbt@xxxxxxxxx<mailto:tim.evdbt@xxxxxxxxx>> 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?atta
chid=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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
Sent: Friday, February 24, 2017 10:02 AM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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/T202991T17430
6.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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
