Currently, some organizations provide in-memory databases for a wide range of activities. This paper examines In-Memory technology in Oracle and IBM and SQL.
The concept of In-Memory technology in the Oracle database
In-Memory technology in the Oracle database is considered as a new option for the Oracle 12c database. In fact, this technology is considered as an Add-On option for the Oracle 12c database, and allows the company to provide its main interface software as an in-memory database. Oracle Database In-Memory can run on all platforms running on Oracle 12c. Oracle needs a separate product such as Oracle Time Ten for In-Memory OLTP that does not fully integrate into the main database.
Exploring IBM DB2 BLU Acceleration
The IBM DB2 BLU Acceleration, presented by the IBM Research and Development Center, is being introduced as a new feature for In-Memory, integrated with IBM DB2 10.5. This technology uses similar memory and storage structures such as storage groups, tabbed spaces, buffer pools, and more, including SQL language usage interfaces and executable like the old DB2. IBM DB2 BLU Acceleration is an In-Memory Columnstore implementation that is suitable for data warehouses and analytical applications.
Comparison of features in In-Memory technology in various databases
In the following, we will look at this section and the next sections to provide a comparison between the features of this technology in different types of databases.
Currently, Oracle’s database does not provide in-memory support for OLTP workloads, but supports this as an optional add-on with the Oracle 12c database for analyzing workloads. Oracle also features In-Memory independently for a separate product called Times Ten, which integrates with Oracle’s database, but at the same time, there are two different products that are installed and managed individually.
Examining the In-Memory OLTP technology in Oracle TimeTen
TimeTen started as an In-Memory database system project in 1995 at HP and then became an independent company. TimeTen was acquired by Oracle in 2005, and thereafter, steps were taken to integrate it with the Oracle Stack, including Pl / SQL / OCI Stack and MAA (Maximum Access Architecture). This technology can be implemented in the following ways:
- Server User Interface and Client :
Supported server interfaces are supported for the server and Client, and scenarios such as logging and access to common in-memory databases are created for a large number of Application-Tier platforms.
- Applications are interconnected:
Applications can be directly linked to the TimeTen address space, thus eliminating overhead or IPC overflows and simplifying the process of processing the Query for its optimal performance.
- Cache for Oracle database ( TimeTen Cache ):
In the case of an Oracle database dedicated to a workload, TimeTen can be implemented as an additional layer of the database cache between the application and the Oracle database. Cache tables in this middle layer may be read-only or can be updated. Applications use a standard SQL user interface to access TimeTen Cache tables, and the synchronization process between these Cache tables and the Oracle database runs automatically.
Compare In-Memory OLTP in Oracle and SQL Server
Oracle TimeTen is a relatively old product, while In-Memory OLTP in SQL Server has some technological advances and has in many cases become an extraordinary toolkit. The table below provides a comparison between Oracle TimesTen, SQL Server 2014 In-Memory OLTP and SQL Server 2016 In-Memory OLTP features, in which the Surface Area adds more than the SQL language to the Native Continuity feature and aggregation.
|Property||TimesTen||SQL Server 2014||SQL Server 2016|
|SQL language||Supports most PL / SQL like DW||InterOp support from most OLTPs||Increase Surface Area in T-SQL|
|Compilation Native||No||Yes, Surface Area in T-SQL that targets OLTP workloads.||Increase Surface Area in T-SQL|
|Based on Lock||Yes, row, table, database locks, selection at connection level||No, using optimistic concurrency control||No new changes|
|Integration||Poor integration with Oracle database||Integration with SQL||No new changes|
|Durability||At the database level (temporary and permanent database)||At the table level, all tables are permanently capability by default, but each separate table can be considered uncontinuous on its own.||2 terabytes of continuous memory that optimizes database tables.|
|Security||—-||Audit and license to control access to admin tables and operations||Transparent data encryption
Table-In-Memory OLTP: Compare TimesTen with SQL Server
- SQL language :
TimesTen and TimesTen Cache include PL / SQL support for data warehousing. The main advantage of this feature is that any of the PL / SQL applications running on the Oracle server are simply transferred slightly to TimesTen. SQL Server in InterOp supports the same way from most OLTPs.
- Compilation Native :
This process is not supported by Oracle TimesTen for In-Memory OLTP. In SQL Server, Native can compile operations on the In-Memory OLTP tables to achieve maximum business processing performance. In later versions of SQL Server, Surface Area can be expanded to enhance compilation as Native.
- Lock -based :
Oracle provides a lock or lock mechanism in the rows, tables, and database levels that can be configured at connection time. This method often leads to synchronous bottlenecks. SQL Server does not use any lock because it provides optimistic synchronization, and thus provides a non-friction Scale-UP capability.
Given the fact that Oracle TimesTen is considered as a separate product, it needs a mechanism to integrate with the Oracle database. While Microsoft’s In-Memory OLTP is not a separate product, it’s part of SQL Server. This way, from the point of view of backup, recovery and management, it is more efficient. Additionally, users can choose to only transfer important functional tables to optimal memory tables due to the integration of SQL Server In-Memory OLTP with the database. SQL Server includes some useful and useful reports that help you identify the tables that should be migrated to the memory optimization process.
The sustainability capability in Oracle is at the database level. While this feature is in SQL Server at the table level, it provides flexibility in setting tables in an application. For example, users can create a table without continuity with the optimum memory for data.