Archive for November, 2013|Monthly archive page

Hana and her sisters

In Database options, DBA, Oracle database on November 30, 2013 at 17:50

Often Oracle DBAs are curious about the differences between SAP HANA and Oracle. As of the end of 2013, the current release of Oracle is and the current release of SAP HANA is SAP HANA SP6 Rev.69.

The Oracle database has five software editions: Enterprise, Standard, Standard One, Express and Personal. There are falso five known SAP HANA software editions: Platform, Enterprise, Extended Enterprise, HANA for SAP BW, and HANA Edge.


Most articles on the Net are rather at high level and very few DBA-level comparisons exist. claims that the dependency on the DBA has been reduced to a large extent although I see there are HANA DBA job openings. Let us go a bit deeper.

One of the new features in SP6 is the new ACCESSIBLE_VIEWS view which allows you to see which views a user can access. The corresponding Oracle view is called USER_VIEWS. It was for sure present before Oracle 12c.

A new parameter reserved_connections is available in the indexserver.ini configuration file (in Oracle this file is called init.ora), which you can edit on the Configuration tab of the Administration editor. You can use this parameter to reserve a certain number of connections for administrative access only. When the maximum number of connections minus the number reserved connections is reached, only an administrator with the system privilege SESSION ADMIN can log on to the system, for example, to resolve blocking situations by canceling sessions.

Oracle does not have such a parameter but you can use Real-time ADDM: you can connect to the database and investigate what is going on when you cannot connect to the database because it is hanging on it is extremely slow. This is for real, connection to the database is in diagnostic mode, there is direct connections to the SGA.

From backup point of view, there are some similarities: in HANA the BACKUP OPERATOR is a new system privilege that allows you to implement a finer-grained separation of duties. On Oracle the similar privilege is SYSBACKUP. In SP6, the Backup editor of the SAP HANA studio now displays the following additional information: last successful data backup with information on start/end time, duration, size, and throughput. This has been visible in Oracle for many releases.

You can follow several discussion SAPA HANA here.


SAP HANA automatically creates additional files if existing files in a data volume located in an ext3 file system reach the 2 TB limit. The autoextend option is much more flexible in Oracle, there are several options for the DBA.

There is now support for hybrid LOBs (BLOB, CLOB, NCLOB) in SAP HANA. Each LOB now has its own virtual file inside SAP HANA. In Oracle, LOB is already an old concept. Modern LOBs are call SecureFiles.

Think of the power of all Oracle database features using the in-memory option! It is really a switch: DBAs have to set the new inmemory_size parameter and run alter table .. inmemory; for all tables for which you would use the option!

According to IDC, 80% of SAP’s installations runs on Oracle and Oracle has continued to enhance its leadership position by certifying Exadata for both SAP ERP and BW. However, core database features like multiversion read consistency with row level locking (came in 1988) and cost based optimizer (present since 1992) are non-existent in HANA. I have seen claims that “SAP HANA in-memory processing greatly reduces the need for a cost based optimizer and basically eliminating the need for data access query optimization via optimizer hints” but I let you decide if this is true or not.

The reality is that HANA misses dozens of DW features like Bitmap Indexes, Join Indexes, Materialized Views, Parallel DML, Parallel Query, etc. Not to mention the security one like TDE, indexing and compression of encrypted data, network encryption and we can go on and on…

I will let you read The Top 10 SAP HANA Myths Spread by Other Vendors and decide for yourself what is myth and what is not.

Let us go to monitoring. To improve usability for SAP HANA DBAs, the user-defined filters and layout configuration applied on the several tabs in the Administration editor are automatically saved when the SAP HANA studio is closed and re-applied the next time it is opened, independently of system. This feature is available on the following sub-tabs of the Performance tab:

○ Sessions
○ SQL Plan Cache
○ Expensive Statements Trace
○ Job Progress

Interesting! Moreover, the SAP HANA studio now provides support for system replication. You can even monitor the status to confirm that both systems are in sync. It is also possible to display zipped diagnosis files, that is files ending with *.gz, on the Diagnosis Files tab. The file is unzipped to a temporary file in the local SAP HANA studio workspace. Additional configuration options are available for the performance trace, SQL trace, and expensive statements trace. All this information and more can be found in SAP HANA Platform Release Notes.

In a recent conference, a heard a claim that “If the Oracle database is the Garden of Eden, then DB2 is dry branch of a tree”. I wonder if someone would claim similar for SAP HANA Studio/DBA Cockpit and Oracle Enterprise Manager?