Dontcheff

Archive for November, 2014|Monthly archive page

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.

Forbes_Strong_Databases

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.

RDBMS_market

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…

HANA_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) 
as "Allocation Limit GB" from PUBLIC.M_HOST_RESOURCE_UTILIZATION

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 (12.1.0.2) is more complex and sophisticated:

create_table_in_memory

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.

CatchMeIfYouCan

Advertisements