Oracle 12c中性能优化&功能增强新特性之重大突破——内存列存储
内存列存储(IM column store) 是Oracle12.1.0.2版本的主要特点。该特点允许列，表，分区和物化视图在内存中以列格式存储，而不是通常的行格式。数据存在内存中的好处显而易见，而列格式存储非常适合商务智能（BI）产品中的分析查询。
列存储是Oracle企业版的独立许可选项。
1. 简介
内存列存储在SGA中是一个新部分，通过初始化参数INMEMORY_SIZE指定大小。可以选择确定的列，整个表，物化视图或表分区组合存储在该部分。或者，你也可以在表空间层启用内存列存储，从而该表空间中的所有表和物化视图自动启用内存列存储。已对下列命令进行了修改以包括另外的in-memory子句。
CREATE TABLE
ALTER TABLE
CREATE TABLESPACE
ALTER TABLESPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
常规用例将在后面展示。
文档中说到如下场景适合内存列存储。
使用"=", "<", ">" and "IN"谓词的大数据量扫描。
只返回很多列的表的少数列的查询。
连接小表和大表的查询。
汇聚数据的查询。
文档也提到该特性不适合以下场景。
有复杂谓词的查询。
返回大量列的查询。
返回大量行的查询。
多个大表连接的查询。
从上可知，需记住的最重要的事是要为决定哪些对象将从中受益负责。如果决定正确，你将看到性能的巨大提升。如果决定错误，你将浪费掉本可被缓冲区使用的大量内存。
2. 开启内存列存储
记住，内存列存储是SGA的一部分，因此，SGA必须能包含你指定的NMEMORY_SIZE参数。在多宿主库环境中，如果任何PDB需要存取内存列存储，INMEMORY_SIZE参数必须在CDB中设置。
1) 如果你正使用AMM (MEMORY_TARGET)，将必须扩展它以适应INMEMORY_SIZE参数值。
2) 如果你正使用ASMM (SGA_TARGET)，将必须扩展它以适应INMEMORY_SIZE参数值。
假定COMPATIBLE参数设置成12.1.0或更高，且SGA总有足够的空间容纳内存列存储，下列过程将开启内存列存储。这里设置INMEMORY_SIZE参数为2G。
ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE;
ALTER SYSTEM SET INMEMORY_SIZE=2GSCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 2929552 bytes
Variable Size 419433584 bytes
Database Buffers 637534208 bytes
Redo Buffers 13844480 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
SQL>
注意，启动期间会显示"In-Memory Area"行。
当前IM相关设置将被如下显示。除了大小，所有其他相关参数均为默认值。
SQL> SHOW PARAMETER INMEMORY
NAME TYPE VALUE
----------------------------------------------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 1
inmemory_query string ENABLE
inmemory_size big integer 2G
inmemory_trickle_repopulate_servers_integer 1
percent
optimizer_inmemory_aware boolean TRUE
SQL>
除非在PDB级别显式设置INMEMORY_SIZE参数，否则，该参数设置将被所有PDB继承。在PDB级别改变INMEMORY_SIZE参数并不要求重启实例或PDB。
CONN sys@pdb1 AS SYSDBA
-- Disable IM column store in the PDB
ALTER SYSTEM SET INMEMORY_SIZE=0;
-- OR
ALTER SYSTEM RESET INMEMORY_SIZE;
-- Assign a PDB-specific size.
ALTER SYSTEM SET INMEMORY_SIZE=1G;
3. 关闭内存列
根据你的目的，有多种关闭内存列存储的方法。
设置INMEMORY_FORCE参数为"OFF"意味着对象不再被保存在内存列存储中，将其切换回"DEFAULT"将恢复其默认的行为。
-- System level
ALTER SYSTEM SET INMEMORY_FORCE=OFF;
ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;
设置INMEMORY_QUERY参数为"DISABLE"意味着优化器优化查询时将不再考虑内存列存储。将其切换回"ENABLE"将恢复其默认功能。
-- System level
ALTER SYSTEM SET INMEMORY_QUERY=DISABLE;
ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;
-- Session level
ALTER SESSION SET INMEMORY_QUERY=DISABLE;
ALTER SESSION SET INMEMORY_QUERY=ENABLE;
为了完全禁用内存列存储且释放内存，只需重置INMEMORY_SIZE参数。
ALTER SYSTEM RESET INMEMORY_SIZESCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
就像前面说的，可以更改PDB级别的设置且不需要实例或PDB重启。
4. 管理表
CREATE TABLE和ALTER TABLE命令已被改进，因此，你可以决定表是否要存储在内存列存储中。以NO INMEMORY子句创建的表和不确定子句是一样的。下面的例子将展示用三种语法创建的三个表。
视图[DBA|ALL|USER]_TABLES已经被改进来包含内存列存储相关的信息。
CONN test/test@pdb1
CREATE TABLE im_tab (
id NUMBER
) INMEMORY;
CREATE TABLE noim_tab (
id NUMBER
) NO INMEMORY;
CREATE TABLE default_tab (
id NUMBER
);
COLUMN table_name FORMAT A20
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
ORDER BY table_name;
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
DEFAULT_TAB DISABLED
IM_TAB ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
NOIM_TAB DISABLED
3 rows selected.
SQL>
ALTER TABLE命令可以改变对象内存列存储方面的状态。下例容易的改变内存列存储的状态。
ALTER TABLE IM_TAB NO INMEMORY;
ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FORCAPACITY LOW;
ALTER TABLE DEFAULT_TAB INMEMORY PRIORITYHIGH;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
ORDER BY table_name;
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- -------------------------------- -------------
DEFAULT_TAB ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
IM_TAB DISABLED
NOIM_TAB ENABLED NONE AUTO FOR CAPACITY LOW NO DUPLICATE
3 rows selected.
SQL>
5. 管理列
下例展示将部分列放进内存列存储的语法。
CREATE TABLE im_col_tab (
id NUMBER,
col1NUMBER,
col2NUMBER,
col3NUMBER,
col4NUMBER
) INMEMORY
INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1,col2)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
NO INMEMORY (id, col4);
通过查询视图V$IM_COLUMN_LEVEL来获取列设置的相关信息。
CONN sys@pdb1 AS SYSDBA
SELECT table_name,
segment_column_id,
column_name,
inmemory_compression
FROM v$im_column_level
WHERE owner = 'TEST'
and table_name = 'IM_COL_TAB'
ORDER BY segment_column_id;
TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION
-------------------- ------------------------------------------------ --------------------------
IM_COL_TAB 1 ID NO INMEMORY
IM_COL_TAB 2 COL1 FOR QUERY HIGH
IM_COL_TAB 3 COL2 FOR QUERY HIGH
IM_COL_TAB 4 COL3 FOR CAPACITY HIGH
IM_COL_TAB 5 COL4 NO INMEMORY
5 rows selected.
SQL>
可以用ALTER TABLE命令改变内存列存储的设置。
CONN test/test@pdb1
ALTER TABLE im_col_tab
NO INMEMORY (col1, col2)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
NO INMEMORY (id, col4);
CONN sys@pdb1 AS SYSDBA
SELECT table_name,
segment_column_id,
column_name,
inmemory_compression
FROM v$im_column_level
WHERE owner = 'TEST'
and table_name = 'IM_COL_TAB'
ORDER BY segment_column_id;
TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION
-------------------- ------------------------------------------------ --------------------------
IM_COL_TAB 1 ID NO INMEMORY
IM_COL_TAB 2 COL1 NO INMEMORY
IM_COL_TAB 3 COL2 NO INMEMORY
IM_COL_TAB 4 COL3 FOR CAPACITY HIGH
IM_COL_TAB 5 COL4 NO INMEMORY
5 rows selected.
SQL>
6. 管理物化视图
CREATE MATERIALIZED VIEW和ALTER MATERIALIZED VIEW命令和 CREATE TABLE and ALTER TABLE命令类似。
CONN test/test@pdb1
CREATE TABLE t1 AS
SELECT * FROM all_objects;
CREATE MATERIALIZED VIEW t1_mv INMEMORY
ASSELECT * FROM t1;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name = 'T1_MV';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
T1_MV ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
1 row selected.
SQL>
ALTER MATERIALIZED VIEW t1_mv
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name = 'T1_MV';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
T1_MV ENABLED HIGH AUTO FOR CAPACITY HIGHNO DUPLICATE
1 row selected.
SQL>
ALTER MATERIALIZED VIEW t1_mv NO INMEMORY;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name = 'T1_MV';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
T1_MV DISABLED
1 row selected.
SQL>
7. 管理表空间
设置表空间的默认内存列存储参数意味着该表空间内的所有表和物化视图将使用那些设置，显示覆盖情况除外。
下列将展示如何在表空间创建期间设置内存列存储参数。关键字DEFAULT是内存列存储子句的一部分。默认内存列存储设置可以查询DBA_TABLESPACES视图查看。
CONN sys@pdb1 AS SYSDBA
CREATE TABLESPACE new_ts
DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf'SIZE 10M
DEFAULT INMEMORY;
SELECT tablespace_name,
def_inmemory,
def_inmemory_priority,
def_inmemory_distribute,
def_inmemory_compression,
def_inmemory_duplicate
FROM dba_tablespaces
ORDER BY tablespace_name;
TABLESPACE_NAME DEF_INME DEF_INMEDEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ---------------- --------------- ----------------- -------------
NEW_TS ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
SYSAUX DISABLED
SYSTEM DISABLED
TEMP DISABLED
USERS DISABLED
5 rows selected.
SQL>
ALTER TABLESPACE命令用来修改内存列存储参数。
ALTER TABLESPACE new_ts
DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;
SELECT tablespace_name,
def_inmemory,
def_inmemory_priority,
def_inmemory_distribute,
def_inmemory_compression,
def_inmemory_duplicate
FROM dba_tablespaces
ORDER BY tablespace_name;
TABLESPACE_NAME DEF_INME DEF_INMEDEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ---------------- --------------- ----------------- -------------
NEW_TS ENABLED NONE AUTO FOR CAPACITY HIGHNO DUPLICATE
SYSAUX DISABLED
SYSTEM DISABLED
TEMP DISABLED
USERS DISABLED
SQL>
ALTER TABLESPACE new_ts
DEFAULT NO INMEMORY;
SELECT tablespace_name,
def_inmemory,
def_inmemory_priority,
def_inmemory_distribute,
def_inmemory_compression,
def_inmemory_duplicate
FROM dba_tablespaces
ORDER BY tablespace_name;
TABLESPACE_NAME DEF_INME DEF_INMEDEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ---------------- --------------- ----------------- -------------
NEW_TS DISABLED
SYSAUX DISABLED
SYSTEM DISABLED
TEMP DISABLED
USERS DISABLED
5 rows selected.
SQL>
表空间默认设置能被上面展示的对象级的设置覆盖。
8. 视图
我们已看到，有些之前的视图已经被修改以便包括内存列存储信息。下列V$视图已经增加了内存列存储相关的信息。
- V$IM_COLUMN_LEVEL
- V$IM_SEGMENTS
- V$IM_USER_SEGMENTS