Skip to Content

Welcome to the problem

As usual understanding the meaning and the scope of the question is key for the answer to this question, recently filed by a customer.

So, what is the customer complaining about? What does he mean with “partition pruning“?

It’s a feature of the query optimizer to figure out, which partitions of a table or an index it can be safely left aside, when running the query.
It reduces work and thereby makes queries faster.

Easy example for this

Think of a table containing sales information together with the date of the recorded sales transactions.
Now this table is partitioned by the month (say, column “0CALMONTH”) and you want to see the sales data of the the first quarter of 2011.
You would probably come up with a SQL statement like this:

SELECT 0CALMONTH as MONTH, SUM(0AMOUNT) as SUM_AMOUNT
FROM SALES
WHERE 0CALMONTH is between 201101 and 201103
GROUP BY 0CALMONTH;

With the information given, the optimizer knows that all relevant data can be found in the partitions that contain the data for 201101, 201102 and 201103 – all other partitions can be ignored.

Nice, isn’t it?

So, how can we know that such stuff is happening in our database?
The explain plan contains this information:

SQL> explain plan for
  2  SELECT "0CALMONTH" as MONTH, SUM("0AMOUNT") as SUM_AMOUNT
  3  FROM SALES
  4  WHERE   "0CALMONTH"  between 201101 and 201103
  5  GROUP BY "0CALMONTH";
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3524420984
--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    11 |     4  (25)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     1 |    11 |     4  (25)| 00:00:01 |     8 |    10 |
|   2 |   HASH GROUP BY          |       |     1 |    11 |     4  (25)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL     | SALES |  3056 | 33616 |     3   (0)| 00:00:01 |     8 |    10 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("0CALMONTH"<=201103)
15 rows selected.

 

The Pstart/Pstop columns tell us precisely, which partitions are used for the single steps – and we see, that only 3 of them (8,9,10) are actually used.

Back to the customer

Ok, so my customer was telling me, that exactly THIS was NOT happening on his system.
Since we use partitioning in BW on Oracle for a long time very successfully, I asked for a reproduction example, which the customer swiftly provided.
Based on what the customer told me I should run a BW query in RSRT and activate the checkbox to display the execution plans for the SQL statements (interestingly this one is called “Display Runschedule” in BW 7.0 onwards…).

A first mistake (or “to know your checkboxes and save everybody’s time”)

When I first ran the query, this was the SQL against the E-fact table which I assumed to be the problematic one (I removed some uninteresting stuff to make reading a bit easier):

====> QUERY 1 

SELECT "DT"."SID_0FISCVARNT"      AS "S____034",
       "DT"."SID_0CALDAY"         AS "S____003",
       "D1"."SID_0PLANT"          AS "S____066",
       "X1"."S__0MATL_GROUP"      AS "S____170",
       "DU"."SID_0LOC_CURRCY"     AS "S____239",
       SUM ("F"."ISSVS_VAL")      AS "Z____565",
       SUM ("F"."RECVS_VAL")      AS "Z____571",
       COUNT(*)                   AS "Z____149",
   [ ... ]
FROM   "/BIC/EZICSTKBAL" "F"                     // <<-- our partitioned E-fact table
   [ ... ]
WHERE  (( ((     "DT"."SID_0CALDAY" = 20100101
             AND "DT"."SID_0CALMONTH" = 201001
             AND "F"."SID_0CALMONTH" = 201001 )) // <<-- a selection criteria on the partitioning column
          AND (( "DP"."SID_0CHNGID" = 0 ))
          AND (( "DT"."SID_0FISCVARNT" = 14 ))
          AND (( "X1"."S__0MATL_GROUP" = 28 ))
          AND (( "D1"."SID_0PLANT" = 14 ))      
          AND (( "DP"."SID_0RECORDTP" = 0 ))     // <<-- we select a certain kind of data here
          AND (( "DP"."SID_0REQUID" <= 72505 )) ))
       AND "X1"."OBJVERS" = 'A'

GROUP  BY "DT"."SID_0FISCVARNT",
          "DT"."SID_0CALDAY",
          "D1"."SID_0PLANT",
          "X1"."S__0MATL_GROUP",
          "DU"."SID_0LOC_CURRCY"

Execution Plan

---------------------------------------------------------------------------------------------------------------------------------
| Id  |Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                    |     1 |   166 |    15   (7)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                             |                    |     1 |   166 |    15   (7)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID              | /BIC/DZICSTKBALP   |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                            |                    |     1 |   166 |    14   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                           |                    |     1 |   157 |    13   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                          |                    |     1 |   151 |    12   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                         |                    |     1 |   141 |    11   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                        |                    |     1 |   132 |    10   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS                       |                    |     1 |   125 |     9   (0)| 00:00:01 |       |       |
|   9 |          TABLE ACCESS BY INDEX ROWID       | /BIC/DZICSTKBALT   |     1 |    18 |     0   (0)| 00:00:01 |       |       |
|* 10 |           INDEX RANGE SCAN                 | /BIC/DZICSTKBALT01 |     1 |       |     0   (0)| 00:00:01 |       |       |
|  11 |          PARTITION RANGE SINGLE            |                    |    54 |  6156 |     9   (0)| 00:00:01 |    54 |    54 |
|* 12 |           TABLE ACCESS BY LOCAL INDEX ROWID| /BIC/EZICSTKBAL    |    54 |  6156 |     9   (0)| 00:00:01 |    54 |    54 |
|  13 |            BITMAP CONVERSION TO ROWIDS     |                    |       |       |            |          |       |       |
|* 14 |             BITMAP INDEX SINGLE VALUE      | /BIC/EZICSTKBAL~02 |       |       |            |          |    54 |    54 |
|* 15 |         TABLE ACCESS BY INDEX ROWID        | /BIC/DZICSTKBAL1   |     1 |     7 |     1   (0)| 00:00:01 |       |       |
|* 16 |          INDEX UNIQUE SCAN                 | /BIC/DZICSTKBAL1~0 |     1 |       |     0   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS BY INDEX ROWID         | /BIC/DZICSTKBAL2   |     1 |   141 |     1   (0)| 00:00:01 |       |       |
|* 18 |         INDEX UNIQUE SCAN                  | /BIC/DZICSTKBAL2~0 |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 19 |       TABLE ACCESS BY INDEX ROWID          | /BI0/XMATERIAL     |     1 |    10 |     1   (0)| 00:00:01 |       |       |
|* 20 |        INDEX UNIQUE SCAN                   | /BI0/XMATERIAL~0   |     1 |       |     0   (0)| 00:00:01 |       |       |
|  21 |      TABLE ACCESS BY INDEX ROWID           | /BIC/DZICSTKBALU   |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|* 22 |       INDEX UNIQUE SCAN                    | /BIC/DZICSTKBALU~0 |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 23 |     INDEX RANGE SCAN                       | /BIC/DZICSTKBALP02 |     1 |       |     0   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------

As I saw (and as you see now) partition pruning was fully operational on this query (check the bold line and the PSTART/PSTOP columns!) – so what was the customer talking about?

Well, the tricky part with this BW query started via RSRT is, that the OLAP layer does run multiple SQL statements in parallel to reduce the total runtim of queries.
That was the reason why the customer initially told me to check the “No parallel processing” box in the Execute+Debug dialog of RSRT.
A hint that I simply overlooked at first.

Et voilá, the problem!

Once I re-ran the query with parallel execution disabled, I was able to see the execution plan the customer was complaining about:

===> QUERY 2

SELECT "DT"."SID_0FISCVARNT"      AS "S____034",
       "DP"."SID_0RECORDTP"       AS "S____011",
       "DT"."SID_0CALDAY"         AS "S____003",
       "D1"."SID_0PLANT"          AS "S____066",
       "X2"."S__0MATL_GROUP"      AS "S____170",
       "DU"."SID_0LOC_CURRCY"     AS "S____239",
       "DU"."SID_Z_UOM_EA"        AS "S____157",
       "DU"."SID_Z_UOM_FT"        AS "S____158",
       "DU"."SID_Z_UOM_LB"        AS "S____159",
       'ZICSTKBAL'                AS "K____002",
       188                        AS "S____002",
       SUM ("F"."ISSVS_VAL")      AS "Z____565",
   [ ... ]

FROM   "/BIC/EZICSTKBAL" "F"
   [ ... ]

WHERE  (( (( "DT"."SID_0FISCVARNT" = 14 ))          // <<-- no selection criteria on the partitioning column anymore
          AND (( "X2"."S__0MATL_GROUP" = 28 ))
          AND (( "D1"."SID_0PLANT" = 14 ))
          AND (( "DP"."SID_0REQUID" <= 72505 )) ))
       AND (((( "DP"."SID_0RECORDTP" = 1            // <<-- we also select some other type of data here...
                 OR "DP"."SID_0REQUID" > 0 ))))
       AND "X2"."OBJVERS" = 'A'
GROUP  BY "DT"."SID_0FISCVARNT",
          "DP"."SID_0RECORDTP",
          "DT"."SID_0CALDAY",
          "D1"."SID_0PLANT",
          "X2"."S__0MATL_GROUP",
          "DU"."SID_0LOC_CURRCY",
          "DU"."SID_Z_UOM_EA",
          "DU"."SID_Z_UOM_FT",
          "DU"."SID_Z_UOM_LB"


Execution Plan
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                             |   440 | 84040 |   128K  (1)| 00:25:39 |       |       |

   [ ... ]

|* 21 |        HASH JOIN                          |                             |   440 | 64680 |   127K  (1)| 00:25:28 |       |       |
|* 22 |         TABLE ACCESS FULL                 | /BIC/DZICSTKBALT            |  3309 | 59562 |     7   (0)| 00:00:01 |       |       |
|  23 |         PARTITION RANGE ALL               |                             | 48550 |  6116K|   127K  (1)| 00:25:28 |     1 |   90  |
|  24 |          TABLE ACCESS BY LOCAL INDEX ROWID| /BIC/EZICSTKBAL             | 48550 |  6116K|   127K  (1)| 00:25:28 |     1 |   90  |
|  25 |           BITMAP CONVERSION TO ROWIDS     |                             |       |       |            |          |       |       |
|  26 |            BITMAP AND                     |                             |       |       |            |          |       |       |
|  27 |             BITMAP MERGE                  |                             |       |       |            |          |       |       |
|  28 |              BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |
|  29 |               BUFFER SORT                 |                             |       |       |            |          |       |       |
|* 30 |                VIEW                       | index$_join$_025            |     3 |    21 |     3  (34)| 00:00:01 |       |       |
|* 31 |                 HASH JOIN                 |                             |       |       |            |          |       |       |
|* 32 |                  INDEX RANGE SCAN         | /BIC/DZICSTKBAL101          |     3 |    21 |     2  (50)| 00:00:01 |       |       |
|  33 |                  INDEX FAST FULL SCAN     | /BIC/DZICSTKBAL1~0          |     3 |    21 |     1   (0)| 00:00:01 |       |       |
|* 34 |               BITMAP INDEX RANGE SCAN     | /BIC/EZICSTKBAL~04          |       |       |            |          |     1 |   90  |
|  35 |             BITMAP MERGE                  |                             |       |       |            |          |       |       |
|  36 |              BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |
|  37 |               BUFFER SORT                 |                             |       |       |            |          |       |       |
|* 38 |                TABLE ACCESS BY INDEX ROWID| /BIC/DZICSTKBALP            |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|* 39 |                 INDEX RANGE SCAN          | /BIC/DZICSTKBALP03          |     2 |       |     1   (0)| 00:00:01 |       |       |
|* 40 |               BITMAP INDEX RANGE SCAN     | /BIC/EZICSTKBAL~01          |       |       |            |          |     1 |   90  |
|  41 |             BITMAP MERGE                  |                             |       |       |            |          |       |       |
|  42 |              BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |
|  43 |               BUFFER SORT                 |                             |       |       |            |          |       |       |
|  44 |                TABLE ACCESS FULL          | SYS_TEMP_0FD9DDDB8_15EC3B48 |     1 |    13 |     2   (0)| 00:00:01 |       |       |
|* 45 |               BITMAP INDEX RANGE SCAN     | /BIC/EZICSTKBAL~05          |       |       |            |          |     1 |   90  |
|  46 |     TABLE ACCESS FULL                     | SYS_TEMP_0FD9DDDB8_15EC3B48 | 10839 | 86712 |     7   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------

Here we can see what the customer was complaining about: Oracle decides to read through ALL partitions, although we’ve seen that the BW query clearly contained the information about the time dimension.

So why is this happening?

Oracle does nothing wrong here!
If you look closely, then you see that the second statement didn’t contain a selection criteria on the partitioning column.

Do we have a bug in the BW OLAP code here?

Actually not!
Would these be BW queries on standard infocubes we would have a problem here.
Especially since we have TWO queries against the SAME table!!!

Fact is: this is not a query on a standard infocube.
This is a query against a so called inventory or non-cummulative cube.

Inventory Cubes?

Inventory Infocubes are a special data design implemented in SAP BW that allow to analyze stock information. Since you cannot just add up the amount of stock over time, such information required special handling by the OLAP processor.

So, what is done in this case is to store reference stock information (“markers”) and in addition to these the relative stock movements. That way OLAP can take the marker numbers and calculate backwards the amount of stock that was present for the given reference point in time.

This is of course far too short as an explanation, so make sure to check out sap note #1548125 for a far better one.

The consequence of this data design is that the OLAP engine needs to read the data in two steps and it does this indeed.
That’s why wee see four SQL queries in this case (instead of the usual two SELECT statements against F- and E-facttable)

1. Read of movement data from F- and E-facttable

and

2. Read of “marker” data from F- and E-facttable

Due to the nature of the “marker” data, OLAP needs to catch up ALL of them, regardless of the time restriction.
Therefore, no WHERE condition for the time dimension is created in the second kind of statements.

Since the E-facttable in this case was partitioned by the time dimension, partition pruning would require to have such a restricting WHERE condition.
But as this is missing, no partition pruning can be done  – which is correct in this case.

Let’s have a closer look to the statements and the execution plans.
Review query 1 above. This is one of the queries issued by OLAP for gathering all the movement data.

Please note the restrictions on the time dimensions, especially the explicit condition on “F”.”SID_0CALMONTH” which allows for the partition pruning to take place.
Also see, that OLAP here is reading the movement data, denoted by the
condition  “DP”.”SID_0RECORDTP”  = 0.

As we see, in the execution plan above, ORACLE uses the restriction
on the partition key and only reads a single partition.

Now, let’s check the query 2 again.

With this query OLAP reads the “marker” data (or “reference points”) and we can see this by the predicate “DP”.”SID_0RECORDTP” = 1.

Important here is to see, that there aren’t any restrictions on the
time dimension left (except the fiscal variant, for rather formal
reasons). So, there is no way for Oracle to avoid any partition of the
E-facttable.
 

Room for improvement!?

Ok, so far we’ve already learned a lot about the way OLAP gets data from the database when dealing with NCUM cubes.
But, as you may have already guessed, there’s still more to it.

The marker information always represent the most current state of data. No movement is done after the marker information. So basically, if a query should return the stock amount from a day in the past, OLAP takes the marker and calculates backwards.
Another way to put this is to say: there cannot be any information more recent or with a ‘higher‘ value in the time dimension. And in fact, marker information are always stored with the maximum/infinitive date 31.12.9999.

Now, since the E-fact table can only be partitioned by the time dimension, it’s assured that the marker data will always end up being stored in the MAXVALUE partition.

So, in theory it would be possible to create a statement that delivers the very same data to OLAP and still allow for the partition pruning to take place. For that it would ‘just’ be necessary to add the somewhat artificical selection predicate “F”.”SID_0CALMONTH” = 99991231 to the query.

I write “in theory” because this feature is not present in the OLAP-DBIF layer right now. Of course we’re discussing this internally, but whether or not this is going to be realized is completely open (for many good and valid reasons).

Once again, a rather lengthly blog post about some rather not so simple topics, but I still hope that you’ve enjoyed reading it and that you’ve learned a bit or byte.

To report this post you need to login first.

14 Comments

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

  1. Bala Prabahar
    Hi Lars,

    Your blogs are always simple and easy to follow. Thanks for writing informative blogs.
    A couple of questions:
    1) My experience with non-*** cubes is limited. I have about 9 months experience dealing with non-*** cubes(I’ve dealt with compressing non-*** cubes of several million records).
    I’m a bit surprised that it took almost 10years to identify this feature. Does it mean non-*** cubes are not widely used or customers are not running into performance issues with non-*** cubes? Whenever there is a performance problem on partitioned tables, as you know, partition pruning is the first thing one would look for.
    2) Would you mind sharing good/valid reasons for not adding that MAXVALUE partition to WHERE criteria of the query?

    Thank you,
    Bala

    (0) 
    1. Lars Breddemann Post author
      Hmmm… what do you mean with “it took 9 years to identify this feature”?

      One point for sure is that there aren’t many performance issues with NCUM infocubes that happen because of the non used partition pruning while reading marker data of the E-facttable.

      Concerning the reasons for not implementing this (and these are just my private thoughts on this):
      – more complex code for a quite special case
      – questionable effect on general performance
      – unclear handling of other partitioning schemes (e.g. there would be the need to derive selection criteria for 0FISCVAR, 0CALYEAR etc.

      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.

      regards,
      Lars

      (0) 
      1. Bala Prabahar
        I meant BW has been using partitioning for 9+ years. Yet no one discovered that pruning was not working for non-*** cubes all these years. That is surprising. (I know partitioning is not automatically implemented for E tables. May be partitioning for non-*** cubes is not being used as widely as for ***-cubes).

        Thanks for your response.

        (0) 
  2. Mikhail Budilov
    Thank you Lars for you post.

    What features we still waiting for BW on oracle ?
    1. Partitions on 0CALDAY – Why not, 21 century )
    2. more oracle hint’s /* parallel */ on BW-processes.

    I think this features will be very useful and increase performance.

    (0) 
    1. Lars Breddemann Post author
      Hello Mikhail.

      it’s always easy to request certain features to be implemented and added.
      The point here is: Oracle and SAP work *CLOSELY* (!) together to get the best *STABLE* (!!!) performance for BW systems.
      For example parallel query is used where it is sensible and safe to use it (e.g. during request condension).
      For running user queries it would easily lead to completely unusable systems as more and more system resources would be allocated in an uncontrolled way.

      With BW 7.30 however, there are whole new opportunities for semantical partitioning – so maybe there’s something it there for you as well.

      (0) 
  3. Stefan Koehler
    Hello Lars,
    from time to time i think, that the SAP OLAP layer is developed by some aliens from the other end of the univers ;-))

    So hard to troubleshoot database related topics when the SQL statements are generated by the SAP OLAP SQL engine (just think about temporary objects that only exists once while running this particular query). So many kind of cubes with different behaviour (just think about that real time info cube issue that we still have) .. but thankfully we got a good support employee called “Lars” who cares about such topics nowadays … Keep on going that great work!

    By the way – Great blog article!

    Regards
    Stefan

    (0) 
    1. Lars Breddemann Post author
      Hi Stefan,

      thanks for the nice comments – this really makes a good start for the week!

      Yes, transactional (or realtime) InfoCubes are also a interesting topic and I’m pretty sure that I’m going to cover this as well.

      So, keep that RSS subscription on my posts 😉

      Cheers, Lars

      (0) 
    2. Lars Breddemann Post author
      Hi Stefan,

      thanks for the nice comments – this really makes a good start for the week!

      Yes, transactional (or realtime) InfoCubes are also a interesting topic and I’m pretty sure that I’m going to cover this as well.

      So, keep that RSS subscription on my posts 😉

      Cheers, Lars

      (0) 
    1. Lars Breddemann Post author
      I’m pretty sure that the MSSQL Server optimizer does also have such a feature.
      But, really, I’ve no clue on MS SQL server execution plan analysis that could be considered to by beyond noticing the obvous.

      So, you might be better off by grabbing your DBA and stare together at the execution plan of your queries than asking me 😉

      regards,
      Lars

      (0) 
  4. ryan mussa
    My query has the fiscal periods defined in the column selections — Current Fiscal Period, YTD Fiscal Year and Previous Fiscal Year.  The selections include Fiscal Period.  My cube is partitioned by Fiscal Period and Compressed.  The query is on a Multiprovider.  The Database is Oracle 10.2.0.4.0.

    When I look at the SQL Explain for “E” table of my cube, the Partition Range Iterator as PStart = Key and PStop = Key and TABLE ACCESS FULL.

    Do you know why the PStart and PStop would not include the Start and End values of the partitions for the selected Fiscal Periods?  Is it because the selection of fiscal periods is in the columns and not in the Filter area of the query?

    Thanks.
    Ryan

    (0) 
    1. Lars Breddemann Post author
      Hi Ryan,

      sorry, but this question is too complex to be properly discussed in the comments section of this blog.

      Please do either open a forum thread (and send me an email about it) or open a support message to have the execution plan explained.

      regards,
      Lars

      (0) 
      1. Bala Prabahar
        As Lars explained, this is too complex.

        I just wanted to add my cents to this discussion:

        Your query doesn’t know the Start and End partitions at compile time.I guess it would become clear at runtime. My guess why the plan  doesn’t specify the actual values for start and end partitions:
          1) Date formats used in query is either different from the one used in “CREATE TABLE” statement or year in query doesn’t have 4 digits. or
          2) Bind variables are used in query.

        Regardless of why KEY instead of actual values shows up, I’m 99% sure your query is using Partition pruning. It might read a few partitions(due to uncertainty) more than needed.

        Bala

        (0) 
        1. Lars Breddemann Post author
          Hello Bala,

          Bind variables aren’t used with SAP BW queries.
          Never. We just don’t do that.

          Concerning the date formats it’s pretty much the same: we know how we put the data to the database and we create the statements for that correctly.

          It’s very likely that the query was transformed by the optimizer, so that the partition pruning is now fed with the result of a subquery.
          However, we simply cannot know this, because we lack information about the execution plan, the data, the database parameters etc.

          That was the reason why I referred to the forums or to a support message.

          regards,
          Lars

          (0) 

Leave a Reply