A preliminary study of Oracle 12c in Memory option

Previously summary:

Oracle's new features in Oracle OpenWorld 2013 for Larry Ellison explosive:Oracle in Memory Option

1. This new feature will be released with 12.1.0.2 ;

2. in memory option will not replace TimesTen (TT), as this is a two-tier product, TT is still in the DB layer before, and the application is closely connected to the application to provide cache, in memory database option in the DB layer, can be made highly available such as RAC,DG, and so on some TT can not implement the architecture. In the same way, in memory option will not replace exalytics;

3.In memory option introduces column storage

4.Oracle In-memory Columnar Compression, provides twice to 10 times times the compression ratio;

5. Significantly faster SQL full table scan processing speed, full table scan will increase 10~100 times, based on the maximum CPU data processing speed, for a simple scan can scan 1 billion rows of data per second, for a simple connection filtering predicate to select a small number of data rows per second to 100 million per second;

6. Significantly faster transaction processing, DML operations-single-line DML and bulk DML will run faster, and single-line processing proceeds mainly from reduced index maintenance by 10 times times;

7.1% of applications are transparent. Similar to OLTP compression, the main advantage is that it is completely transparent to the application. All other Oracle features will work with IN-MEMORY option, including partitioning, indexes, text indexes, without explicit data types or storage type restrictions.

Recently, Oracle12.1.0.2 released the following:

Http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html?ssSourceSiteId=ocomen

There are currently only Linux and Solaris versions.

To install Linux virtual machines and Oracle12.0.1.2, refer to the following links (thank you very much, Xiaoxiang):

Http://www.cnblogs.com/kerrycode/archive/2013/09/13/3319958.html

After the environment is ready, study Oracle's official technical note:

Http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

Roughly sorted out below:

1.Row format vs. Column format

Oracle database In-memory (Database in-memory) provides the best of both worlds by allowing data
To is simultaneously populated in both an in-memory row format (the buffer cache) and a new inmemory
Column format.

Note that the Dual-format architecture does is not a double memory requirements. The In-memory
Column format should is sized to accommodate the objects that must being stored in memory, but the
Buffer cache have been optimized for decades to run effectively with a much smaller size than the size
of the database. In practice it's expected that the Dual-format architecture would impose less than a 20%
Overhead in terms of total memory requirements. This was a small price-to-pay for optimal performance
At all times for all workloads.

Row format vs. Column format is supported, and memory usage is not doubled.

2.The in-memory Column Store

Database In-memory uses an in-memory column store (IM column store), which is a new component
Of the Oracle Database System Global Area (SGA), called the In-memory area. Data in the IM column
Store does not reside in the traditional row format used by the Oracle Database; Instead it uses a new
Column format. The IM column store does not replace the buffer cache, but acts as a supplement, so
That data can now is stored in memory in both a row and a column format.
The In-memory area was a static pool within the SGA, whose size is controlled by the initialization
Parameter Inmemory_size (default 0). The current size of the In-memory area was visible in V$SGA. As
A static pool, any changes to the Inmemory_size parameter won't take effect until the database
instance is restarted. It is also not impacted or controlled by Automatic Memory Management (AMM).
The In-memory area must has a minimum size of 100MB.
The In-memory area was sub-divided into and pools:a 1MB pool used to store the actual column
Formatted data populated into memory, and a 64K pool used to store metadata on the objects that
is populated into the IM column store. The amount of available memory in each pool are visible in the
V$inmemory_area view. The relative size of the pools is determined by internal heuristics, the
Majority of the In-memory area Memory are allocated to the 1MB pool.

A new component has been added to the SGA region: In-memory area, static pool, size is controlled by parameter inmemory_size (default 0) and must be restarted after the database is modified.

A minimum value of 100M with two pools inside it:

1MB Pool: Store the actual column-format data in memory

64K Pool: The memory column stores the metadata that involves the object metadata

3. Populating the In-memory column store add to memory Columnstore

Database In-memory adds a new INMEMORY attribute for tables and materialized views. Only objects with the
INMEMORY attribute is populated into the IM column store. The INMEMORY attribute can be
Specified on a tablespace, table, (sub) partition, or materialized view.

Added inmemory options to support table spaces, tables, partitions, materialized views

1. Tablespace level setting inmemory, all new tables, materialized views under tablespace will be loaded into the memory columnstore.

If It is enabled at the Tablespace
Oracle Database in-memory option 5 level, then all new tables and materialized views in the tablespace would be enabled for The IM column
Store by default.
ALTER tablespace ts_data INMEMORY;

2. Table level setting inmemory, support for excluding specified columns, supporting table partitions

ALTER TABLE Sales INMEMORY;

ALTER TABLE Sales INMEMORY NO INMEMORY (prod_id);

For a partitioned table, all of the table's partitions inherit the in-memory attribute but it ' s
Possible to populate just a subset of the partitions or sub-partitions.

ALTER TABLE Sales MODIFY PARTITION sales_q1_1998 NO INMEMORY;

3. Support Memory Load Priority configuration

ALTER TABLE customers INMEMORY priority CRITICAL;

4. There are also limitations, not all tables can be loaded into memory Columnstore

SYS user, SYSTEMN, Sysaux object under Table space cannot use the inmemory option,

Index organization tables, clustered tables cannot use the InMemory option

Unsupported data type: LONGS (deprecated since Oracle Database 8i); Out of line lobs

At the same time, if the object is less than 64K, the memory space consumption is not loaded into the memory columnstore.

5. In-memory Compression Memory Compression

Then the second part of this paper: based on the above characteristics, the actual business data testing.

From the above test situation, although it is a virtual machine environment, but tested several times.

From the test results, the performance of the high-volume insert and update has been reduced, the large-volume delete performance has improved, the query performance is still very obvious, if the memory is sufficient, the estimate will be improved.