Archive for the ‘SAP HANA’ Category

Oracle In-Memory for SAP Databases

In DBA, Oracle database, SAP HANA on March 19, 2016 at 14:02

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 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 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 (
– 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 or newer. Strongly Recommended Windows DB Bundle Patch
– 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:


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.


Strong Oracle Database 12c Wiping Away the Competition

In DBA, Oracle database, SAP HANA on November 23, 2014 at 12:24

49% of Oracle’s total software revenues in FY14 came from database software. As Forbes mentioned in their article Strong Database And Application Software Sales Could Lift Oracle Higher, the company commands a market share of nearly 48% in the well established and mature Relational Database Management System market.


According to Forbes “Oracle has been aggressive in marketing its newest database offering, Oracle 12c, against strong competition from alternative RDBMS providers, as well as HANA from its rival SAP. In-memory platforms are increasingly being deployed to facilitate faster transactions for applications where the cost of the extra memory can be justified, including the rapidly expanding online commerce market across the globe.”

Although SAP HANA belongs to the 14% other segment, it should not be at all ignored or underestimated. But curb your enthusiasm.


In HANA there are 2 types of tables. Row based and Column based. It is defined upon table creation and can be changed vice-versa afterwards with the ALTER TABLE SALES ALTER TYPE ROW; command. Because computer memory is structured linearly, there are two options for the sequences of cell values stored in contiguous memory locations:

Row Storage – It stores table records in a sequence of rows.
Column Storage – It stores table records in a sequence of columns i.e. the entries of a column is stored in contiguous memory locations.

An Oracle DBA might be slightly amused or bewildered when going though the instruction on how to Create Table in SAP HANA. Copy the SQL statement below in SQL editor, Press F8, Right click on the table…


SAP HANA tracks memory from the perspective of the host. The most important concepts are as follows:

– Physical memory: The amount of (system) physical memory available on the host.
– SAP HANA Allocated memory: The memory pool reserved by SAP HANA from the operating system.
– SAP HANA Used memory: The amount of memory from this pool that is actually used by the SAP HANA database.

On most SAP HANA hosts, the DRAM ranges from 256 gigabytes to 2 terabytes. To find the global allocation limit of the database, run below SQL query:

select HOST, round(ALLOCATION_LIMIT/1024/1024/1024,2) 

In order to understand how to find out different memory consumption in SAP HANA, check SAP HANA Memory Usage Explained.

Oracle Database 12c In-Memory ( is more complex and sophisticated:


The Oracle DBA can:

– Define if the table is row format only or both: then the data is kept synchronized on disk and in RAM
– Exclude certain partitions from the table: a multi-million row table with old unused data might be a memory consumption killer
– Exclude certain columns from the table and only store in memory the analytical columns: look at the NO INMEMORY syntax above
– Define what type of memory compression is to be used for the columns
– Specify if the compression to be more suited for DML or for SELECT
– Define the priority of how fast to load the memory area: imagine thousands of SAP tables being loaded into memory after instance startup
– Define how to distribute and replicate the data in RAC: do you really want same data replicated in all RAC instances?
– Create an In-Memory tablespace so afterwards all tables created in that tablespace will be IM tables
– Specify how many copies of each In-Memory Compression Unit (IMCU) of the tables in the IM column store will be spread across all the Oracle RAC instances

All of the above is far from reality in SAP HANA. Did you know that in SAP HANA, a non-partitioned table cannot store more than 2 billion rows? Check these SAP HANA Restrictions.

Every 6 months there is a major release of HANA, called a Service Pack. Service Pack 9, or SPS09, has several new innovations. There are SAP HANA user defined functions (UDFs) for Hadoop which enable SAP HANA to access the map reduce jobs within Hadoop directly.

For the Exadata and SuperCluster DBAs: the duplicate-clause is only applicable if you are using Oracle Real Application Clusters on an Engineered System. Otherwise, the duplicate-clause is ignored and there is only one copy of each IMCU in memory.