Skip to Content

HANA loads the right partition for range-partitioned table only for queries in SQL plan cache

Hi!

I've noticed a strange behavior of HANA (SPS 08 and 09) with tables loading in memory. I have a big column table partitioned by HASH on the first level and by RANGE (calendar period) on the second level. For clean test I unload the table comletely from memory. After that I run an SQL query with the restriction of calendar period in the way that only single or very few partitions must be analyzed. If that query is missing in SQL plan cache HANA loads all partitions in memory, but only the columns involved in the query. After that I unload the table from memory again and repeat the query. At this moment a query is already in SQL plan cache and only relevant partitions are loaded. This behavior is normal for HANA? The most recent revisions work the same way? If it's normal, please, provide me to official documentation which desrcibe this.

Thanks, Andrey.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Mar 30, 2016 at 06:40 AM

    Sounds like you provided the partition information in your where clause in the wrong data type. You might want to check with the partitioning trace which partition conditions were understood.

    Check the SAP note FAQ partitioning, search this forum (this has been discussed before) or read my book.

    The SQL plan cache does not have an influence on partition loading.

    Add comment
    10|10000 characters needed characters exceeded

    • Ok, there's some mixup here.

      The optimiser sampling results, which lead to the initial column load in your example with unload priority 5, are cached and kept.

      When you run your second test with unload priority 6 these sampling results got reused and didn't trigger a column reload.

      Once again: unload priority is evaluated only when SAP HANA determines column unloading. It's not used anywhere else, especially not in the query evaluation.

  • Jun 29, 2016 at 09:41 AM

    Hi!

    Just for info. A behaviour when HANA loads all the partitions in memory at the time of SQL statement preparation (instead of loading only the relevant partitions according to SQL query restriction) is expected:

    When all partitions are unloaded and you prepare an SQL statement, HANA SQL optimizer first collects statistics from most columns of ALL partitions to be able to porivde optimal execution plans for the later queries.

    So that's how it is. Actually, it means that partition pruning is not working during SQL statement preparation. But this is true only for tables with unload priority values 0 - 5 (btw, the default value for most tables is 5). But not everything is so bad, you can change this behavior:

    If you want to change the behavior that for prepared statements most

    of the columns for all partions are loaded then you need to change the

    unload priority for the table from default value 5 to 7.Then only

    columns from some larger partitions are loaded, with the burden that

    SQL execution plans for the statement may not be optimal dependent

    on filter values which are later used to execute the SQL, after the

    preparation.

    Actually, we noticed that even priority value 6 provides loading of almost only the necessary partitions and columns (you can find in my message of Apr 20, 2016 10:05 PM that HANA still loading very few unnecessary columns and partitions). We did not notice any performance degradation with changing priority from 5 to 6, but the memory is well saved and the queries run faster. Unfortunately, the dependence of loading behavior on the priority values is not documented:

    The different unload priority values are not officially documented

    and explained in detail, but the value range is from 0-9, with 5 being

    default.

    So just need to have this in mind.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi!

      I got a chance to test it on the SPS11 system (SP111). Well, it works how it really should work - only relevant partitions are touched in all cases. It seems that something was fixed in SPS11 concerning partitions loading during statements preparartion, but I cannot find what exactly.

      For example, table PRUNING_TESTUP1.

      SPS9:

      SPS11:

      UPD: guys, pls, could someone check any of the test cases from this thread (txt files with SQL script) on both SPS11(or 12) and on SPS<=10. Do you observe the same unwanted partitions loading on SPS<=10? Thanks in advance.

      SPS11.PNG (35.9 kB)
      SPS9.PNG (48.1 kB)
  • Andrey,

    Congratulations for the tests.

    In our SPS09, we face the same scenario: unload priority affects loading behavior exactly as you detailed.

    Also, loading only works right concerning columns in the first level of the partitioning. If a select is executed with a where clause on a column in the second leval of the partitioning, all partitions are loaded into memory.

    Add comment
    10|10000 characters needed characters exceeded