BW on Oracle: a performance hitch I see more and more often…
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!
+++ 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 +++
Short but very interesting blog content. We have multiple customers struggling with BW performance.
I bought the new SAPPress book on BW Administration and Monitoring which is also quite good, I can recommend it for administrators who want more insight on BW.
Indeed many BW performance issues arise from common design and administration failures.
Therefore it's a very good idea to check the documentation or even a book on this topic.
Thanks for your comment and hope to have you as a reader again 😉
thanks for the blog! Being familiar with SAP basis and interessted in BW your blogs are very informative. I have checked some systems, this specific problem seems to be not very common, but I found two systems with the symptoms.
I really like proactive troubleshooting.
thanks for your nice comment.
Hopefully this specific issue is not too common, but given that I work in SAP support and only see systems where something doesn't work as expected I get a bit alarmed when I fall over the same issue again and again.
Thus, writing a short blog like this one seems like a good idea to me to share this observation.
And to be honest: it's kinda boring to analyse performance issues and find that the problem could be solved by some very basic system maintenance.
I'd rather not see such messages anymore but interesting, complicated stuff instead 😉
SAP can not perform on top of Oracle. We all waiting for new mircale. 🙂
All the best,
This is a common issue to have poor performance when data size is quiet huge at most of the customer sites.
It’s really hinders many of us to prove it having better performance in compare to Oracle BI (OBIEE), Hyperion etc.
SAP has found that miracle to resolve it, with next version of SAP BW 7.3, additional option for underlying SAP database option would be Teradata.
Teradata capability of parallel processing and other features would surely increase SAP BI performance in the next phase.
is right about the upcoming Teradata integration to BW 7.30 (via MaxDB-Teradata-Bridge), there are other functions for BW speedup available as well.
The BWA/BIA is one of them. (already available for a long time and pretty sucessfull)
HANA will be another one. (coming up this year)