Oracle's in-memory database strategy

This article introduces two major branches of Oracle memory database, TimesTen and database in-memory, which are well worth reading and are fully covered by the important features of both technologies, allowing readers to quickly get a comprehensive understanding of the key points of Oracle's in-Memory database technology, After reading this article, we suggest that we can read the original text in the reference again.

Overview

As memory capacity grows and prices fall, it is possible to put all of your user data into memory to avoid expensive I/O.

Oracle offers two complementary in-memory database technologies for application and database tiers:
1) TimesTen
Can be deployed at the database tier as a standalone database, or in the application tier as a cache of back-end Oracle databases.
Primarily for low-latency OLTP applications

2) Database In-memory
is an option for the database Enterprise Edition 12c, deployed at the database layer, to accelerate the analysis load. The size of the database is not limited by memory, the tables that need to be analyzed can be loaded into memory, others can be stored with disk, SSD, etc.

Part 1:oracle TimesTen in-memory databasetimesten Architecture

TimesTen is a relational, in-memory database that supports acid. Because the data is completely in memory, high performance and low latency can be achieved.

Applications can use JDBC, ODBC, OCI, SQL connection TimesTen.

The TimesTen supports the traditional C/S mode, as well as the unique higher performance direct mode (in Direct-link mode, the all database API invocations is treated simply as function call s into the TimesTen GKFX library allowing for in-process execution of database code)

Why is the memory database faster than the cache, as explained below:

Another over-arching design principle is the use of memory-based addressing rather than logical addressing. For instance, indexes in TimesTen contain pointers to the tuples in the base table. The metadata describing the layout of a table contains pointers to the pages comprising the table. Therefore both index scans and Tablescans can operate via pointer traversal. This design approach was repeated over and over again in the storage Manager with the result of that TimesTen is Significantl Y faster than a disk-oriented database even one that's completely cached–since there are no overhead from have to Tran Slate logical Rowids to physical memory addresses of buffers in a buffer cache.

Supported index types are hash, range, and bitmap

Hash Indexes for speeding up lookup queries, Bitmap Indexes for accelerating star joins with complex predicates, as well a S range Indexes for accelerating range scans.

The transactional durability in acid is implemented through checkpoint and Write-ahead logging, with two checkpoint files and multiple log files. Each time the checkpoint is completed, it switches to another file, so there is always a full memory image for the roll-forward of the backup and recovery. Logging, for performance, TimesTen provides delayed-durability mode in addition to Oracle-like durable commit, that is, after the log is written to log buffer, The data in buffer is flush to disk every 200ms in the background.
If data loss is not tolerated, you can use durable commit mode or the 2-safe copy mode mentioned later

The default isolation level is read-committed isolation. Support for row-level locks, like Oracle, supports MVCC or MVRC, so that reads and writes are not blocked.

TimesTen High Availability

The high availability of timesten is achieved through log-based replication, and the principle is log-shipping. Supports asynchronous, quasi-synchronous and synchronous modes (2-SAFE).

With 2-safe replication, a transaction are committed locally only after the commit have been successfully acknowledged by th E receiver.

2-safe replication is typically used in combination with non-durable commit.

This combination allows applications to achieve commits durability in both memories, without requiring any disk IO.

Replication can be based on a single table (classic Replication) and the entire database (Active standb Pair), and replication can be bidirectional (classic Replication) or one-way replication (classic Replication or Active standb Pair), the most common and recommended replication mode is to run a read-only application for the active standb pair,standby node, Standby nodes can be replicated to multiple subscriber nodes to improve read scalability.
Replication speed and throughput can be increased through parallel replication.

Application-tier caching

This is the most commonly used mode of TimesTen, which is a persistent transaction cache as a backend Oracle database, which greatly accelerates the application.
The high performance of the application-tier cache depends on:
In-memory optimizations-This is the most fundamental, that the full-memory architecture is much faster than the disk-based architecture
Application Proximity-timesten is deployed in the middle tier, closer to the application, and the unique direct Mode allows applications and timesten to communicate in-process, further improving efficiency

The TimesTen cache group corresponds to the tables that need to be cached in Oracle.
The loading of data in the Cache group supports pre-load (preloaded) and dynamically loaded (load on access) modes.

For dynamically loaded mode, the data to is referenced must be identified by a equality predicate on the primary key of the Roo T table

The dynamically loaded mode can also specify a cache aging policy that can be based on time or LRU

The most common cache types are read-only cache Groups and updatable cache Groups:
Read-only Cache Groups-

For data that's infrequently updated, but widely read, a read-only cache group can being created on TimesTen to offload the Backend Oracle database. Very Hot reference data, as online catalogs, airline gate arrival/departure information, etc. was a candidate for this type of caching. The Oracle side tables corresponding to read-only cache groups is updated on Oracle. The updates are periodically refreshed to timesten using an automatic refresh mechanism.

Updatable Cache Groups-

For frequently updated data, a updatable cache group with Write-through synchronization is appropriate. Account balance Information-a online ecommerce application, the location of subscribers in a cellular network, stream ING sensor data, etc. is all candidates for Write-through caching. TimesTen provides a number of alternative mechanisms for propagating writes to Oracle, but the most commonly used and high EST performing mechanism is referred to as asynchronous Writethrough where the changes be replicated to Oracle using a lo g-based transport mechanism. This mechanism was also capable of applying changes to Oracle in parallel, in keeping with the Parallel-everywhere design t Heme of the system.

Note: The term write-through in TimesTen is equivalent to Write-back in storage.
Replication from TimesTen to Oracle is the same principle as timesten to TimesTen.

Multiple TimesTen caches can be deployed for an Oracle database, and they are co-ordinated, called the Application-tier Database cache Grid.

Therefore, the type of the cache can be further divided into local and global cache Group.
Local Cache Groups-
The data in the cache is private and cannot be seen by other members

This type of cache group was useful when the data can be statically partitioned across grid members; For instance, different ranges of the user profile Ids is cached on the different grid members.

Global Cache Groups-

In many cases, an application cannot is statically partitioned and Global Cache Groups allow applications to transparently Share cached contents across a grid of independent TimesTen databases. With this type of cache group, cache instances is migrated across the grid on reference. Only consistent (committed) changes is propagated across the grid.

The data in the cache can be shared by other members. from which TimesTen node the app is accessed, the cache instance is passed to that node. Therefore, while it is not possible to partition statically, it is necessary to ensure that the data is localized to avoid excessive data transfer between nodes.

Thus, the contents of the global cache group is accessible from any location, via data shipping.

With no consideration for replication, each cache instance has only one copy in the global cache, which increases the TimesTen node to scale horizontally, providing more capacity and processing power.
If you need to consider high availability, you can establish an active Standby Pair for each node.

The federated query for all TimesTen members in the global cache can be implemented through the global query. For example, COUNT (*), MAX, etc.

A global query is a query executed the parallel across multiple grid members.

Part ii:oracle Database in-memoryoracle database In-memory overview

Oracle database In-memory (Dbim) provides row and column two formats for the data, where the row format is the already existing buffer Cache. Unlike traditional in-memory databases, Dbim does not limit the capacity of the data to be fully accommodated in memory.
Dbim Innovation lies in:
* Dual Format
The row format is suitable for OLTP applications that access a few rows of multiple columns, and column formats are suitable for analysis
* Unlimited Capacity
You can place important tables and partitions for analysis on the IM column store while the rest of the data is placed in buffer CACHE,SSD and disk
* Transparent Application
Due to the seamless embedding of columnar columns into the data access layer, all database features such as RAC, multi-tenancy, ADG can be used together, the database automatically determines whether to access the Columnstore or buffer cache

New In-memory column Style

The new column format is completely memory-based, which puts the entire table space, tables, partial columns, partitions, and sub-partitions of the table into memory.
Data can be modified, and the database automatically maintains data consistency in columnar and row formats.
Data is automatically published to memory through the background process and there is no application disruption.
The loading of the data can be prioritized, preloaded at database startup, or loaded in real time when accessed.

The loaded data is automatically compressed, you can choose a different compression level, and if the OLTP range is frequent, you can choose a lower-cost compression level.

Memory scan

With SIMD (single instruction multiple data), multiple columns of data can be scanned in parallel by an individual instruction.

Storage Index further enables I/O optimization,

An in-memory Storage Index keeps track of minimum and maximum values for each column CU.

Similar to partitioning, you can skip unnecessary Cu (Column Unit) at query time, thus reducing I/O

In-memory join

A typical star join between the fact table and the dimension table can convert the join to a column scan via Bloom filter, which is ideal for columnstore, which improves scanning speed.

In-memory aggregations and groupings

A New Optimizer transformation, called Vector Group by, was used to compute multi-dimensional aggregates in real-time. The Vector Group by transformation are a two-part process similar to the well known star transformation.

Memory Columnstore and Full transaction consistency

The default isolation level provided by Oracle Database is known as consistent Read. With consistent Read, every transaction in the database was associated with a monotonically increasing timestamp referred T O as a System change number (SCN). A multi-versioning protocol is employed by the buffer cache to ensure that a given transaction or query only sees changes Made by transactions with older Scns.

After adding the Columnstore format, you can still maintain data consistency with the buffer cache.

The IM Column store similarly maintains the same consistent read semantics as the buffer cache. Each IMCU are marked with the SCN of the time of its creation. An associated metadata area, known as a Snapshot metadata Unit (SMU) tracks changes to the rows within the IMCU made Beyon D that SCN.

The SMU maintains the validity of the data in the IMCU, and if the data is stale due to modification, the query automatically updates the data with the Log Union query, and the data in the IMCU is automatically updated in the background.

IMCU updates can be updated according to set thresholds or regular small batches.

Column Storage Extensions

Scaling is achieved by distributing data between RAC nodes. Parallel queries can be implemented simultaneously

The distributed policy can be based on the partition, and if there is no partition, it can be based on ROWID. Or it is automatically selected by the system.

After the data is distributed, if you consider high availability, you can choose to replicate, and the replicated data resides on other nodes in the RAC or all other nodes.

Future development of columnar format

• Consider a combination of automatic data optimization (ADO) in 12c for automated life-cycle management of data
• Implement Columnstore on ADG physical standby to maximize ROI with disaster-sparing
• Extend the column format to flash or other persistent memory technologies

Reference

• Oracle ' s in-memory Database strategy for OLTP and Analytics

Oracle's in-memory database strategy