Skip to Content

Please refer to Part I of the blog: How to verify that your query is actually using the physical partition?

In the Part I blog, our focus was to identify the clues and confirm that the BW query was indeed reading from the right partition.

In this second part of the blog, I would like to share some of my experiences with identifying the performance bottlenecks of the BW Query.

In general, below are a few parameters that we take into consideration for query performance optimization for high database time.

  • Details on distribution of data across D and F/E tables – helps decision making on ‘Line dimension’
  • Details on summarization ratio – helps decision making on ‘Aggregates’

In addition to above, Details on SQL Execution plan to identify the ‘Cost’ly reads should also be considered.

Always, it is a must to dig deeper and pin point the exact root cause for slow running queries. The Statistics tool gives us a good guidance on the problematic areas to start with, but, we often jump to conclusions and proceed with the performance tuning corrective actions without knowing the real root cause.

Therefore, it is required to take a step back, have a re-look at the query design and all the tables involved in the SQL joins.

To continue from where I left my previous blog, I completed the cube partitioning and was also able to confirm that partition pruning was happening and the query was reading from the right partition as desired. But, I was not able to find appreciable improvement in the response times. I missed to note something obvious – here it is.

If you focus on the database reads in this execution plan, it indicates reads on the dimension tables, Fact tables and master data tables.

This one is the execution plan based on Cost based optimizer – so check out the records hit and the cost calculated.

image

All reads until the Fact table (circled in the screenshot) seems to be okay -but when you look at the database (/BIC/XINFOOBJECT) which is hit soon after the Fact table, it is really scary. Please refer to the red bold records. You see 18 million records hit, 200 times more than your Fact table itself.

More than partitioning the Fact table, my real concern should have been this master data read on /BIC/XINFOOBJECT.

My query was using a Navigational  attribute of the XINFOOBJECT and so was hitting the X table. I compared the size of the X table vs. the size of the dimension table that carried the same field and to my rescue, the latter was far better.

So, I replaced the Navigational attribute in the query with the characteristics from the cube. Fortunately, this replacement was okay, as per business logic, for my reporting requirement and so there was not any impact replacing navigational attribute by characteristics.

Now, I came back to my execution plan and could see that the count of records hit and the total cost got drastically reduced.

Finally, the query response was a lot faster.

Had this replacement not been feasible (for whatever reasons), there wouldn’t have been a way out. Unfortunately, there is not much performance tuning that you can do with master data table reads – you can check for degenerated index, if any, on master data and try to repair the index and update the statistics. Also, you can purge unwanted/historical master data from the master data table – if that is not in use and approved by your business.

With this big a table in the schema, there is not much of response gain that I can dream of – doing the cube partition. Basically, the effect gets nullified. The real value of range partitioning started to unfold only after this navigational attribute was removed from the query.

To report this post you need to login first.

7 Comments

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

  1. François Gyöngyösi
    Hi Sreenivas,

    Optimising BW queries could indeed be a tricky business. We had the same kind of issue a while ago trying to optimise a query that uses a navigational attributes of a master data with millions of records.

    With BW 3.5 under Oracle, BW gives a hint to Oracle to use a STAR TRANSFORMATION. The query would take around 500 seconds to execute.

    Via transaction RSRT, we executed the query with the database optimiser disabled (in this case, a STAR TRANSFORMATION is not used by Oracle). The query execution time went down to a few seconds!

    In the end, we decided to add the navigational attributes in an aggregate. Obviously, this is not always practical because it could take a long time to update the aggregate each time the master data is itself updated. In our case, this was acceptable.

    François.

    (0) 
  2. Lars Breddemann
    … one of the blog posts that begin so promising and ends up making things simpler as they are.

    After reading the first blog post too let me make some comments to your optimizer observations and the ones of François.

    First of all:
    Partitions are _not_ primarily there to improve query performance.
    The main purpose for partitions is to be able to get rid of your data much quicker and cleaner as it would be possible to do without partitioning.
    Index rebuilds, statistics gathering – all that data shovelling stuff is easier when your huge fact tables are nicely chunked down to partitions.

    The optimizer-Query-Improvement “partition pruning” is just a by-product, that will only improve your partition for very specific circumstances (partition by column is used in the where condition, range scan on the table/partition is chosen).

    Since the table rows in your example are accessed via an index lookup anyhow it’s actually the INDEX partitioning that improves the query here.
    The table partitioning does not do a thing for you in this example.

    Concerning the STAR TRANSFORMATION == BAD comment:
    Sorry, but the described effect that the query runs quicker when ST is disabled is quite likely the consequence of a bug or a misconfiguration of the database.

    Working in Oracle database support for some years now, I can nearly blindfolded tell that BI systems with query performance problems usually don’t use the current CBO merge fixes and aren’t setup with the recommended parameters.

    Way over 75% of all performance problems are solved after fixing this alone!

    Looking at the query example given above:
    What might be the reason that the CBO uses a HASH JOIN to combine the X-table with the Join-result of /BIC/ECUBENAME and the TEMP-table?
    Maybe a missing (aka not yet defined) index?

    Saying that the redesign of the query was the only possible solution to this problem is a bit far off (even without knowing yet the query text).

    Then you write about “degenerated Indexes”… just another one of those myths that unfortunately are also presented largely in SAP BI courses like “BW360 – performance tuning and administration”.

    What would be a “degenerated” index for you?
    How should such a thing be created in your BI system (where you usually drop indexes before data loading and rebuild them afterwards)?

    Of the many cases where customers just rebuild indexes to improve performance, for only a few a positive effect was measurable.
    And yet, for most of those cases, this wasn’t due to the new index structure, but simply due to the fact that with the index rebuild also the index statistics are re-collected!

    All in all I’m disappointed to just see another blog post presenting some rules of thumb and recommendations that seem to have worked for you presented without the appropriate database grounded reasoning and analysis.

    regards,
    Lars

    (0) 
    1. Sreenivas Varadharajan Post author
      Thanks Lars for your comments. There is always a new thing to learn. Let me clarify a couple of items here. By ‘Degenerates Index’, I was referring to the Indexes of infoobject master data S and X tables. These were found during the RSRV checks that we perform in BI.

      The intension of sharing this experience was to highlight that designing a query – in terms of analyzing when would a navigational attribute really required, when is it good to use the characteristics instead of an attibute and what impact would any change on query have on performance – is the most critical part for improving the response time. Mostly the BEX query tool is seen as a drag and drop tool and so the understanding of underlying database reads should be understood is what I wanted to convey.

      (0) 
    2. Matthew Paul
      Lars — would you be able to elaborate or give a link for more information related to “current CBO merge fixes and recommended parameters” — I would like to learn about these and check my system.  Thanks –Matt
      (0) 
      1. Lars Breddemann
        Hi Matthew,

        the easiest way to check your system would be to use the automatic check tools from notes

        #1175996 – Automated Oracle 10g patches check
        #1171650 – Automated Oracle DB parameter check

        With these you easily can generate lists of missing patches and parameters that need to be changed.

        Regards,
        Lars

        (0) 

Leave a Reply