Oracle 12c In Memory Option初探

时间:2024-08-12 22:34:26

前情提要:

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,内存使用没有翻倍。

Oracle 12c In Memory Option初探

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

Oracle 12c In Memory Option初探

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;

Oracle 12c In Memory Option初探

4.同时还有一些限制,不是所有的表都可以加载到内存列存储中

Sys用户、Systemn、SysAux表空间下的对象不能使用INMEMORY选项,

索引组织表、聚集表不能使用INMEMORY选项

不支持的数据类型:LONGS (deprecated since Oracle Database 8i);   Out of line LOBS

同时对象如果小于64K,考虑到内存空间消耗,将不会加载到内存列存储中。

5. In-Memory Compression 内存压缩

然后本文第二大部分:基于上述特性,进行了实际业务数据测试。

Oracle 12c In Memory Option初探

Oracle 12c In Memory Option初探

Oracle 12c In Memory Option初探

Oracle 12c In Memory Option初探

Oracle 12c In Memory Option初探

从上述测试的情况看,虽然是虚拟机环境,但是测试了多次。

从测试结果看,大批量Insert和Update的性能有所下降,大批量Delete性能有所提升,查询性能提升还是非常明显,如果内存足够,估计会再有提高。