Ok, once again reproducing a problem takes a lot of (waiting-)time for the query to finish.
In the meantime I can also just write a super-short-blog post about a pretty common BW-on-Oracle performance hitch:
Inappropriate database optimizer statistics.
(yes, 'inapropriate' not 'outdated'!)
Usually, that means: if everything runs as it is supposed to, BW will take care of collecting the CBO statistics whenever large changes to the data are performed (e.g. data had been loaded or condensed, etc.).
Of course, the BW admin has to set this up in process chains or as attributes to infoproviders but usually, this works well.
If these BW-measures somehow "forget" to update the statistics when new statistics would be required, there is still the daily "Update Optimizer Statistics" (brconnect -f stats) run that should cover this.
The big exception
Unfortunately there is one setting available that prevents the update of new statistics:
the exception table DBSTATC.
In this table you can setup statistic gathering parameters that deviate from the default build-in behavior of brconnect. (This behavior is not too complex, but really does cover most SAP specifics. See the documentation for details here).
One of the most used exceptions here is to avoid gathering new statistics at all.
This can be useful, e.g. when a table is very volatile and you don't want to have your access plans changed all the time because the statistics sometimes report a small/emtpy and sometimes a large table.
(For such case SAP even provides pre-defined table statistics, check sap note 756335 on that)
But what does this have to do with BW?
Well, before brconnect there had been a different tool called SAPDBA and this tool wasn't able to cope with BW objects.
Therefore BW development decided to build its own statistics update coding and to prevent SAPDBA from messing around with the BW tables. And for that they created entries in DBSTATC with ACTIVE=I automatically.
A comfortable solution
This is of course all long past now, but these entries seem to have survived many system upgrades.
In order to get rid of them, there's a report available: SAP_DBSTATC_CLEANUP.
With it you can display and delete the DBSTATC entries for single infocubes or all of them.
Looking at the sap note that introduces this report (SAP note 129252) you'll find that this had been around for quite a while now 🙂
The recommendation is of course to remove all the DBSTATC entries for BW objects, so that the standard statistic gathering can be performed.
SAP note 1013912 - "FAQ: Oracle BW Performance" already contained this recommendation and from today on, it also contains the report name ...
And yes, the solution is to remove the entries and not to set ACTIVE=A or something like that. DBSTATC really is all about defining an exception. If the tables should be handled the standard way, then DBSTATC should contain any entry for them!
Ok, the customer report is through... back to the real work!
Best regards,
Lars
+++ UPDATE added 22.10.2011 +++
In addition to the SAP_DBSTATC_CLEANUP report mentioned above, it's also important to make sure that report SAP_ANALYZE_ALL_INFOCUBES isn't used for statistics gathering anymore.
This report calls a function RSDU_DBSTATC_EXIT for each analyzed infocube and thereby the DBSTATC entries are recreated again!
+++ UPDATE added 22.10.2011 +++
