Skip to Content

As the fourth quarter of the year started everybody seems to check the goals that had been set for this year and what has already been reached. Looking back on the messages I worked on I found a very clear pattern, when the message was about BW on Oracle and performance.

Most messages were solved by the very same simple five steps:

  • ensuring the database software is up do date
  • ensuring the database parameters are set as recommended
  • ensuring that the CBO statistics were in fact current and correct
  • ensuring that all indexes are present and usable
  • ensuring that the f-fact table doesn’t have too many partitions

Yes, these steps are boring and not very popular.
To give the recommendation to implement these steps, not much special analysis is required.
In fact, it’s a rather quick thing to check on these and tell if they need to be applied. It reminds me of the the flossing discussions with the dentist… Anyhow, let’s review these points briefly one by one:

Up to date Oracle database software

BW systems regularly present the Oracle query optimizer with highly complex issues to solve.
And the Oracle cost based optimizer (CBO) is by far the most complex piece of coding in the whole DBMS software.
So, even without the long history of epic optimizer errors (check note #176754) it should be obvious, that it’s fairly impossible to not have bugs in this code.
For example one issue that struck often with the early 11.2. patches was that the optimizer started to use the infamous MERGE JOIN CARTESIAN (MJC) operation.
Due to bugs and/or outdated statistics the CBO believed to get one one or zero rows back from one row source and with that knowledge a MJC eventually seems to be a good choice.
Once you get more rows than that you easily end up with PSAPTEMP overflows (ORA-1652) and horrid runtimes.

And sorry, but Oracle 8i, 9i and 10g are all outdated and not supported anymore.
See note  #1110995 Extended maintenance for Oracle Version 10.2 on this.
Of course nobody can deny you to use Oracle 10g, it’s your system landscape after all.
But be prepared to have to install Oracle PLUS the latest SAP Bundle Patch before any ind-epth analysis (e.g.via  CBO trace) is performed.
We’re not chasing bugs in this old versions anymore.
Besides: very very likely the problems just won’t be there with the latest patch anyhow. It’s pretty mature nowadays.

Database parameters

Well this pretty much just goes with the paragraph before: without the correct parameter settings that fit the current version and patch of the database software, bugs and issues cannot be avoided, even if they are already known.

Current/fitting CBO statistics

Still, one of the links that I give to my customers most often is the one to my own old blog post

BW on Oracle: a performance hitch I see more and more often…

Even today it seems that there are yet many systems where the DBSTATC needs to be cleaned up.
Today I just added a small detail: when you used to use report SAP_ANALYZE_ALL_INFOCUBES to collect statistics, you really should stop that.
It basically brings back the DBSTATC entries…

Another aspect of this topic is that the standard heuristic to gather new statistics is based on a threshold of changed data in a table. Usually this threshold is at 50% of changed (that is added, updated, deleted) rows. For BW systems this can be suboptimal. For example time dimension tables might get new data and thus certain say 0CALMONTH values might suddenly be present in the histograms. By relying on the standard heuristic the CBO won’t notice that, as no new statistics have been gathered. One way to overcome this would be to force new statistics on single tables.

Another step to take would be to force the re-collection of statistics for tables that have very old (say older than a year) statistics.

Unfortunately there is – not yet – a automatism build into brtools or BW, but internal discussions on this have started!

Indexes present and usable

Indexes are critical for the optimizer. They speed up queries, they provide data access possibilities and sometimes even allow to leave out a table access alltogether.
But only if they are actually present on the database and usable.
For BW systems, where table partitioning is used per default, it’s important to have the indexes also partitioned.
Otherwise partition-wide actions like DROP PARTITION/TRUNCATE PARTITION will render the indexes on the table UNUSABLE or INVALID.
And they cannot be used for queries in this state.
The most common cases are described or at least referenced in note
#1513510 – 900 index in unsuable state with SAP Basis 7.00/7.01/7.02
Although I write the note initially to cover a specific bug in a SAP Basis SP it contains extensive explanations, a check SQL statement and links to other related sap notes.
Make sure to check it out and to get your indexes straight.

Too many f-table partitions

F-fact tables can be seen as the data inbox table of an infocube.
The request-wise partitioning allows quick and easy data removal in case the loaded data is not OK.
Usually this shouldn’t happen once your data mart has been properly setup and is stabilized.
Anyhow, keeping a good 30 partitions (e.g. a months worth of data) won’t do any harm.
But more than that make the work for the optimizer much more difficult.
Also, nearly nobody using business reporting checks on specific data load requests.
Instead queries are usually build upon a time reference.
The E-fact table can be partitioned by time and that way the query performance can be improved by factors.

So, make sure to check blog post
Partition count contest!
and note #590370 – Too many uncompressed request (f table partitions)
and start compressing your cubes today.

Is this it? Yep, kinda…

Are there other possible performance problems in the BW on Oracle world?
Sure they are. But these aren’t nearly as often as the ones that are solved for good by implementing the five steps discussed here.
As a little extra, now that you know about these five steps, you can plan and decide when you implement them – you don’t have to wait until you hit a serious performance issue and open a support message for that πŸ™‚ 

That’s it for now. Have a great weekend!

Cheers, Lars

To report this post you need to login first.


You must be Logged on to comment or reply to a post.

  1. Mark FΓΆrster
    Hello Lars,
    how good is HANA at analyzing free text data like SAP support calls? I don’t know if the actual solution to a problem is already tracked SAP-internally. Maybe if some linguistic genius would process the support call database, such an analysis could provide correlations between symptoms and their root-cause. If successful, that could raise Problem Management to a new level.
    1. Lars Breddemann Post author
      Hi (again) Mark!
      As you might guess, you’re not the first to come up with such an idea.
      ‘Automatic solution mining’ – how great would that be?

      Turns out, the real issues are not so much the text parsing and searching (we can do this with TREX for quite some time now), but other stuff instead.
      There are legal topics to be considered, data protection regulations and so forth.
      In addition at the end of such a symptom-root cause stochastic analysis there still have to be the expert that decides between correlation and coincident and who writes corrections and sap notes.

      Working in primary support now for the eighth year, I’ve seen this idea popping up every now and then – so, I’m sure it won’t be let down πŸ™‚

      To also answer your question about HANA doing stuff like this: right now, in the current version out to customers (SPS2) there is no such text search feature available with HANA.
      But I got a quite concrete gut feeling that something like that will be included too.

      best regards,

    2. Stefan Koehler
      Hello Mark,
      well in my opinion SAP should really invest more into its employees (number and their skills of course) instead of introducing such a system based on linguistic.

      Have you opened a service request nowadays? The response time is just ridiculous (even with prio mid / high requests) and in most cases the first responder is not able to support you. So in most of (my) cases the service requests end up by the same second or third level persons, who i am happy with, but mostly after weeks.

      Also after you have rated the quality of the service requests you got no response anymore – i can remember at some time – some a manager called you if you rated some negative points and asked you.

      Even so the escalation process is ridiculous – you call to speed up the service request processing but nothing happens. Sometimes i think the SAP guys make fun of us.

      There is a lot of space to improve the quality or better said to get the quality of round about 2 years ago and i don’t think that such systems will help with that issues.


      P.S.: Maybe some SAP manager read this and i will get some red phone (like Commissioner Gordon in Batman) to 2nd / 3rd level support πŸ˜›

      1. Mark FΓΆrster
        Hello Stefan,

        you are of course right that nothing can substitute good staff with good training in support organizations. My idea was of course not to try to substitute staff with processes. Problem Management complements Incident Management, but does not substitute it in any way.

        There are several initiatives in SAP HANA InnoJam online with a similar focus:
        Now when Lars says HANA isn’t ready yet for such projects then we’ll have to wait and see.

        SAP should implement a feature that experienced people could enter 2nd level support directly. We solemnly promise not to open bullshit support calls. Therefore we are entitled to skip 1st level support entirely. This would be a win-win situation where we save time ourselves and also SAP saves valuable time!



      2. HS Kok

        I personally have had my fair share of experiences when logging support calls with SAP.

        Most of the time they are picked up and passed off as “consulting issues”. Even when I present hardcore evidence that it is a program error, with the exact line that needs to be fixed (and how to fix it). I’m really not sure why/how they manage to do that by sidelining me, and naturally was frustrated whenever I had to log a support call (nearly one or two every 3 months).

        Nowadays when I log support calls, I make sure I have dead evidence pointing to program errors (so that it cannot be classified as “consulting issues” or “intended behavior”).

        And if I get any delays (guy’s still probably flippin’ his Technical Handbook for an answers), I just tell him (1st level) to bump me to the next level. And if the next guy takes > 1 day to get back to me, chances are that he (2nd level) isn’t sure either. And so I tell him to bump me to the next level. And I keep doing this until an OSS note is opened for my issue.

        At least, that’s how I finally managed to get SAP support working for me… πŸ™‚ Cheers!

        1. Lars Breddemann Post author

          Hello HS Kok,

          You sure got a point here.

          As it seems you’re one of the few percent of BW users with development level knowledge and insight that are able to figure out misbehaviour and analyze it down to the very part of the coding where the bug is located. So, first of all: congratulations for that.

          That of course also means that you’re in a much better situation than the vast majority of customers that seek support. And for those customers it’s very important to have somebody who walks them through the basics and who checks for simpler stuff as well.

          And sure enough, this is your advantage as well, since you surely don’t want to see the one developer who will actually fix your issue be drowning in messages that would require only a parameter check or the condension of some infocube requests. πŸ˜‰

          Another point here is: developers usually know their stuff really really well – but lack overview and insight into other areas. Once you hit issues that are “cross-topic” it very often becomes difficult. And that’s where a supporters with a broader topic range can really help a lot.



  2. Tom Cenens
    Hello Lars

    I seriously have to start archiving your blogs πŸ˜‰ they are always very to the point and useful.

    Are you going to SAP TechED Madrid by any chance?

    Kind regards


    1. Lars Breddemann Post author
      Hey Tom,

      thanks for the kudos!
      Glad you like what I write every once in a while πŸ™‚

      TechED wíll be without me this year – maybe in 2012 again. We’ll see about that.
      In any case, once I go to any public event, like TechEd, SAPPHIRE or Infodays I’ll definitively post this in advance, so that I can take the chance to meet all the SDN guys’n’girls in person.

      Have a nice weekend! Lars


Leave a Reply