Oracle 12c In Memory Option初探

標籤：des   style   blog   http   color   使用   os   io   

前情提要：

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;

4.同時還有一些限制，不是所有的表都可以載入到記憶體列儲存中

    Sys使用者、Systemn、SysAux資料表空間下的對象不能使用INMEMORY選項，

    索引組織表、聚集表不能使用INMEMORY選項

    不支援的資料類型：LONGS (deprecated since Oracle Database 8i)；   Out of line LOBS

    同時對象如果小於64K，考慮到記憶體空間消耗，將不會載入到記憶體列儲存中。

5. In-Memory Compression 記憶體壓縮

 

然後本文第二大部分：基於上述特性，進行了實際業務資料測試。

從上述測試的情況看，雖然是虛擬機器環境，但是測試了多次。

從測試結果看，大批量Insert和Update的效能有所下降，大批量Delete效能有所提升，查詢效能提升還是非常明顯，如果記憶體足夠，估計會再有提高。