Skip to Content

Recently I read a blog titled “Question to SAP Support “Why is partition pruning not working in my BW on Oracle”. Lars as usual has explained the details in easy to understand simple language. Those who’re familiar with SAP and database technologies would know that SAP doesn’t take full advantage of underlying disk-based RDBMS. This is not something new. However what surprised me was that the bug introduced several years ago while implementing a simple partitioning scheme was not uncovered until recently. Why did it take 9+ years to uncover a simple bug? I wondered. Most probable reason is that SAP customers don’t use DB features-even supported by SAP-extensively.  

Partitioning Versus non-partitioning:

Let me explain the advantages of partitioning using a simple example. Let us say we’ve a customer table with 1,000,000 records. When not partitioned, all 1million records would reside in one table. See the diagram below. Top portion shows unpartitioned table whereas bottom portion shows partitioned table with 10 partitions. For the sake of simplicity, let us assume each partition contains the same number of records, 100,000. 

The table below shows the range of customer numbers in each partition.

 

The table below shows the range of customer numbers stored in non-partitioned table.

Let us say, 10 users(U1, U2, U3 etc) try to analyze data stored in customers table. Each one-let us assume- is responsible to analyze 100,000 records in different ranges as shown in table below. If table is not partitioned, each one would read 1,000,000 as indicated in 3rd column. When that table is partitioned, each one would read only 100,000 records due to partition pruning feature.

Bottom Line

The example I used is very simple and I didn’t consider other aspects like indexes, different types of partitioning etc. Only purpose of the example was to show:

  1. How partition pruning works and
  2. How it helps to reduce the I/O accesses to Storage Area Network(SAN)

Parameters and Patches

In order for databases to perform optimally, keeping the database current with patches and parameter recommendations is critical. In SAP/Oracle world, SAP’s recommendations on both parameters/patch level work very well. When this recommendation is not followed-as Lars explains(see below)-, it would lead to severe performance problems in not just BW systems but in any SAP system. 

Lars states:

“All in all you’ve to keep in mind that this really is just a little fancy feature flower on the big wall of system performance.

The *VAST* (!!!) majority of severe performance problems in BW systems are caused by not having implemented the current Oracle patches and the parameter recommendations.And without these points in place, no what so clever feature will make a fast system out of a slow one.”

Is partitioning really a little fancy feature? I disagree. I’ve seen appropriate partitioning strategy solving serious performance issues several times in both BW and non-BW systems. This is not a little fancy feature but an essential feature to manage VLDBs. 

In addition, SAP really shouldn’t use customers not following SAP’s recommendations on parameters/patches as an excuse for not fixing a serious bug such as the one explained in Lars’s blog.  

Please note partitioning not only reduces i/o but also improves the usage of Oracle memory areas by reading less. This may in some cases improve Buffer Cache hit ratio.

To report this post you need to login first.

3 Comments

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

  1. Lars Breddemann
    Hi Bala,

    thanks a lot for taking the time and effort to write a whole blog post to make your point. This is highly appreciated.

    But why do you stick to explaining how partitioning (on a rather conceptual level) works?
    Why don’t you deliver arguments, facts and/or proofs that my points are wrong and that it’s really a ‘serious bug’ what I’ve explained in my blog?

    I tend to say: Because it isn’t as bad as you put it.
    My blog was about a very small piece of code that does not play a major role in system performance for most customer systems.
    I personally have worked on hundreds of performance problem messages and just recently came across this issue.

    And even if it would be implemented to support partition pruning for the reading of reference data in inventory infocubes, it’s not yet clear how big the effect would be at all to the total response time.

    As I already wrote: asking for new/additional features and functions is a very popular.
    Of course, the next feature will be the silver bullet, the magic ingredient to overall system performance improvement.

    But you know what?  That doesn’t happen.
    Partitioning, Bitmap-Indexes, Table/Index-Compression, Multi-Node-Clusters – all these are technologies that can be used to improve system performance.
    But using them properly to the benefit of the users requires to fully understand them and to master the functions of the database system.

    SAP is surely not playing finger pointing with customers when it comes to database performance.
    It’s just not making much sense to insist of a single feature implementation that helps in a rather seldom case and have systems running on ancient software versions with the wrong parameter setup.

    Or, to put it a bit more challenging: “Show me a real life example where the implementation of the partition pruning for the reference data of an NC Infocube improves the total query runtime reasonable (say at least reduction by 1/3) and I show you ten cases where simply changing the parameters and implementing the patches and/or regathering wrong statistics improved query performances by more then 200%” 😉

    Best regards and keep the blogs comming!
    Lars

    (0) 
    1. Bala Prabahar Post author
      Hi Lars,

      Why did I write about how partitioning works?
      I’m planning to write blogs explaining how to partition a table outside SAP; in addition I’ll be covering all types of partitions. Before writing them, I wanted to give a brief introduction to what partitioning is.

      Why NC – Partition Pruning issue is a serious bug?
      PSA/F-Fact tables are partitioned primarily because drop instead of delete is faster.
      Similarly E fact tables are partitioned primarily due to Partition Pruning for reporting. If primary benefit of some feature doesn’t work, I would call it a serious bug. In an ideal world, that feature for NC cubes should either be removed or  bug should be fixed. If no customer who uses NC cubes is complaining because there is no business value, then I agree why bother fixing that issue. This is a valid reason for not fixing it as far as I’m concerned.
      Partitioning is not going to solve all problems. As you explained, partitioning is one(primary) of many features we can use to improve the performance of any system. Proper use of them is key to success. In addition to what you mentioned, design, aggregates, BW Caching, broadcasting etc also would help solve performance issues depending on nature/frequency of reports.
      Without analyzing the nature of queries or system status, it is not possible to improve query runtime by either partitioning or changing the parameters and or implementing the patches.
      I guess we’re in agreement on everything except: Partitioning is NOT a little fancy feature. It is an essential feature to manage VLDBs.

      I love this discussion. Thanks.

      (0) 
      1. Lars Breddemann
        Hi Bala,

        please remember that the partition pruning for the E-facttable of NC-cubes works for the reading of the movement data. And this is a much larger volume of data to be worked on.

        So, no, this is not a bug at all and if you remember my blog and the discussion about it, then you also remember that using the partition pruning requires some derived selection criteria that might not be straight forward to derive correctly.

        And “correctly” is the key word here!

        To me, a bug would be something where the system behaves other than intended and defined by the programming. The situation I described really does work as intended and as coded. It’s in no way a bug.

        I of course agree on that partitioning is a nice feature, but I’m sure that I didn’t call it “little or fancy”, although it’s not required to have it – even for BW systems.

        Looking forwards to read your blogs,

        Lars

        (0) 

Leave a Reply