Oracle 12c In Memory Option初探

2023-01-10 17:30:53

前情提要：

Oracle OpenWorld 2013中Larry Ellison爆料的Oracle新特性:Oracle In Memory Database Option

1. 这个新特性将随着12.1.0.2发布；

2. In memory database option不会取代TimesTen（以下简称TT），因为这是两种层面的产品，TT还是会架在DB层之前，和应用紧密相连，为应用提供缓存，in memory database option在DB层，可以做到高可用如RAC，DG等一些TT无法实现的架构。另外同样道理，in memory database option也不会替代Exalytics；

3.In memory database option引入了列存储

4.Oracle In-Memory Columnar Compression，提供2倍到10倍的压缩率；

5.显著增快SQL的全表扫描处理速度，全表扫描将增快10~100倍，基于CPU的最大数据处理速度，对于简单扫描可以每秒扫描10亿行数据；对于简单的连接过滤谓词最终选出少量数据行的达到每秒1亿条每秒；

6.显著增快事务处理，DML操作-单行DML和批量DML都将运行地更快；单行的处理收益主要来源于降低10倍的索引维护；

7.100%的应用程序透明。类似于OLTP压缩，主要的优势在于对于应用而言完全透明。所有的其他ORACLE特性均将可以与in-memory option一起工作，包括partitioning, indexes, text indexes，而没有明确的数据类型或者存储类型限制。

最近，Oracle12.1.0.2发布了，下载地址：

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

目前只有Linux和Solaris版本。

安装Linux虚拟机和Oracle12.0.1.2，参考以下链接（在此非常感谢潇湘隐者）：

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

环境Ready后，研究下Oracle 官方的技术说明：

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

大致整理了下：

1.Row Format vs. Column Format

Oracle Database In-Memory (Database In-Memory) provides the best of both worlds by allowing data
to be 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 not double memory requirements. The in-memory
column format should be sized to accommodate the objects that must be stored in memory, but the
buffer cache has been optimized for decades to run effectively with a much smaller size than the size
of the database. In practice it is expected that the dual-format architecture will impose less than a 20%
overhead in terms of total memory requirements. This is a small price to pay for optimal performance
at all times for all workloads.

同时支持Row Format vs. Column Format，内存使用没有翻倍。

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 be stored in memory in both a row and a column format.
The In-Memory area is 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 is visible in V$SGA. As
a static pool, any changes to the INMEMORY_SIZE parameter will not 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 have a minimum size of 100MB.
The In-Memory area is sub-divided into two pools: a 1MB pool used to store the actual column
formatted data populated into memory, and a 64K pool used to store metadata about the objects that
are populated into the IM column store. The amount of available memory in each pool is visible in the
V$INMEMORY_AREA view. The relative size of the two pools is determined by internal heuristics, the
majority of the In-Memory area memory is allocated to the 1MB pool.

SGA区增加了一个新组件：In-Memory Area，静态池，大小通过参数InMemory_Size（默认0）控制，修改后必须重启数据库生效。

最小值100M，其内部包含两个池：

1MB pool ：存储内存中实际的列格式数据

64K pool：内存列存储涉及对象的元数据MetaData

3. Populating The In-Memory Column Store 添加到内存列存储

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

增加INMEMORY选项，支持表空间、表、分区、物化视图

1.表空间级别设置INMEMORY，表空间下所有新的表、物化视图将被加载到内存列存储中。

If it is enabled at the tablespace
Oracle Database In-Memory option 5 level, then all new tables and materialized views in the tablespace will be enabled for the IM column
store by default.
ALTER TABLESPACE ts_data INMEMORY;

2. 表级别设置INMEMORY，支持排除指定的列、支持表的分区

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.支持内存加载优先级配置

ALTER TABLE customers INMEMORY PRIORITY CRITICAL;