The Japanese proverb “HANA YORI DANGO” means literally “Dumplings rather than flowers” meaning “to prefer substance over style, as in to prefer to be given functional, useful items (such as dumplings) instead of merely decorative items (such as flowers)”.
HANA is nowadays a very stylish and trendy concept among the database professionals but I would follow the Japanese saying and rather go with substance.
The two different points of view can be easily found on the internet:
SAP: What Oracle won’t tell you about SAP HANA
Oracle: Oracle Database In-Memory vs SAP HANA benchmark results
The following “Facts vs Claims” will most likely perplex and bewilder everyone. Just have a look and decide for yourself. Let us not go into details but consider this statement:
FACT: Oracle makes big data a bigger problem with 4 copies of the data (3 in-memory and 1 on disk).
I have been trying for quite some time to figure out the 3 copies of in-memory data that Oracle creates with no success. Perhaps this is a riddle?
An year ago, on March 31st 2015, SAP was certified to run on Oracle Database 12.1.0.2. Rather odd one would say, as in the past SAP were awaiting the 2nd release of the Oracle database in order to certify it for SAP. At least a sign that 12.1.0.2 is mature enough to be used for SAP production systems.
As of June 30th 2015, the Oracle Database In-Memory Option is supported and certified for SAP environments for all SAP products based on SAP NetWeaver 7.x. on Unix/Linux, Windows and Oracle Engineered Systems platforms running Oracle Database 12c – in single instance and Oracle Oracle Real Application Clusters deployments.
In-Memory is such a great feature – but (for both Oracle and SAP) often the challenge you’ll face are these 2 tricky questions:
1. Which of your tables, partitions, and even columns should you mark for In-Memory column store availability?
2. What should be the value of the SGA, PGA and IMCS size? That is, how much memory do I need (global_allocation_limit in SAP and inmemory_size for Oracle)?
Here you have a very strong advantage of Oracle over SAP. The answers are now easier to find with the new In-Memory Advisor which is available via download from MOS Note:1965343.1. Use of In-Memory Advisor for databases where the IM option has not yet been deployed does NOT require an Oracle Tuning Pack license.
For SAP, Quick Sizer is used for a new implementation of Business Suite powered by SAP HANA. Here is a How to Properly Size an SAP In-Memory Database. There are two different approaches for performing the sizing: user-based sizing, which determines sizing requirements based on the number of users in the system, and throughput-based sizing, which bases the sizing on the items being processed. The sizing rules for SAP Business Suite on SAP HANA that are outlined in SAP Note 1793345.
Memory Management in the Column Store: SAP HANA aims to keep all relevant data in memory. Standard row tables are loaded into memory when the database is started and remain there as long as it is running. They are not unloaded. Column tables, on the other hand, are loaded on demand, column by column when they are first accessed. With Oracle, only tables (or columns/partitions of the table) that need to be in memory are set in memory thus avoiding waist of unnecessary memory and having the ability to do more with less.
The Oracle approach is much more sophisticated but there are 10 major issues DBAs should pay attention to when using the In-Memory option with SAP databases:
1. To use Oracle Database In-Memory with SAP NetWeaver the following technical and business prerequisites must be met:
– Oracle Database 12c Release 1 Patch Set 1 (12.1.0.2)
– UNIX/Linux: Oracle Database SAP Bundle Patch June 2015 (SAP1202P_1506) or newer. Strongly recommended Oracle Database SAP Bundle Patch August 2015 (SAP1202P_1508)
– Windows: Windows DB Bundle Patch 12.1.0.2.6 or newer. Strongly Recommended Windows DB Bundle Patch 12.1.0.2.8
– SAP NetWeaver 7.x Version with minimum SAP Kernel 7.21_EXT
2. Indexes. It is not allowed to make any changes to the standard index design of the SAP installations. However, customer specific index design can be changed. That is, all indexes which belong to the Y or Z namespaces can be changed.
3. Database Buffer Cache. It is not allowed to reduce the size of the database buffer cache and assign the memory to the In-Memory column store.
4. SAP Dictionary Support. Full SAP Dictionary (DDIC) Support of in-memory attributes at the table level starts with the support package SAP_BASIS 7.40 SP12.
5. Individual Columns. It is not supported to load individual columns of an SAP table or partition into the IM column store. It is also not supported to exclude individual columns from an SAP table or partition from the IM column store. An SAP table is a database table used by an SAP application.
6. The database where you want to run In-Memory Advisor must have XDB component installed as the In-Memory Advisor relies on functions provided by XDB. In 12c XDB is installed by default.
7. The In-Memory Advisor is contained in the SAP Bundle Patch as patch 21231656.
8. For SAP applications it is strongly recommended to use a reasonable time window of collected AWR data. At least 2-3 days of AWR data should be used for the In-Memory Advisor. It absolutely makes no sense to use data from a 1-2 hour time window.
9. Use these In-Memory Advisor Parameter Name and Value:
– WRITE_DISADVANTAGE_FACTOR = 0.7
– LOB_BENEFIT_REDUCTION = 1.2
– MIN_INMEMORY_OBJECT_SIZE = 1024000
– READ_BENEFIT_FACTOR = 2
10. For SAP systems therefore the following init.ora parameters should be used:
inmemory_max_populate_servers = 4
inmemory_clause_default = “PRIORITY HIGH”
inmemory_size should be set to the value (+ ~20% for metadata and journals) used in the generate recommendation step of the In-Memory Advisor or set to value calculated by the SAP_IM_ADV Package for all tables/partitions to be loaded into the In-Memory column store.
Using RAT shows the benefit of the In-Memory option. Compare the DB time of the 2 replays after going to Exadata with the In-Memory option. DB time is the best and possibly only metric to compare captures with replays.
SAP Notes: 2178980 – Using Oracle Database In-Memory with SAP NetWeaver
MOS Notes: 1292089.1 – Master Note for Oracle XML Database (XDB) Install / Deinstall & 1965343.1 – Oracle Database In-Memory Advisor
Using SAP NetWeaver with Oracle Database In-Memory
Bottom line: if my SAP application runs on top of an Oracle database, I would rather put it on Exadata with the In-Memory option enabled that move it to SAP HANA.
Julian Dontcheff's Database Blog 