Oracle 12C In-Memory特性研究
- 一、Oracle In-Memory
-
- 1.1 In-Memory 開啟方法
- 1.2 開啟與關閉IM column store
- 1.3 inmemory優先級調整
- 1.4 加載對象到IM
- 二、In-Memory測試
-
- 2.1 全字段查詢
- 2.2 索引字段比較測試
- 2.3 批量update測試
- 2.4 大批量insert測試
- 2.5 大量delete測試
- 2.6 壓縮方式測試
- 2.7 查詢大量列性能測試
- 三、評估對象在IMO大小
- 四、參考文獻
一、Oracle In-Memory
行格式與列格式：
Oracle 資料庫傳統上以行格式存儲資料。在行格式資料庫中，資料庫中存儲的每個新事務或新記錄都表示為表中的一個新行，而在查詢資料時是利用傳統BUFFER CACHE。
列格式資料庫将表以單獨的列結構存儲到記憶體中。列格式适用于報表類，分析，選擇少量列但是查詢要通路大部分的資料的場景。
1.1 In-Memory 開啟方法
啟用IMO非常簡單,12.1.0.2及之後版本下，設定INMEMORY_SIZE 為非0值便可啟用IM column store特性。
INMEMORY_SIZE 是個執行個體級參數，預設為0，設定一個非0值時，最小值為100M。
通常情況下，sys使用者下的對象及SYSTEM、SYSAUX表空間上的對象無法使用IMO特性，但通過設定“_enable_imc_sys”隐含參數也可以使用
開啟DB In-Memory過程如下：
1、修改INMEMORY_SIZE參數：
SQL> ALTER SYSTEM SET INMEMORY_SIZE=1G SCOPE=SPFILE;
2、檢查sga參數的設定，確定在設定完inmemroy_size參數之後資料庫執行個體還可以正常啟動。如果資料庫使用了ASMM，則需要檢查sga_target參數。如果使用了AMM，則需要檢查MEMORY_TARGET參數，同時也需要檢查SGA_MAX_TARGET（或MEMORY_MAX_TARGET）。
備注：從 12.2 開始，可以動态增加 In-Memory 區域的大小，為此，隻需 通過 ALTER SYSTEM 指令增加 INMEMORY_SIZE 參數值即可
3、重新開機資料庫執行個體
4、檢視IM特性是否開啟
SQL> SHOW PARAMETER inmemory;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled boolean TRUE
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 2
inmemory_query string ENABLE
inmemory_size big integer 1G
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL
optimizer_inmemory_aware boolean TRUE
1.2 開啟與關閉IM column store
1、TABLE 級啟用：
可以通過如下初始建表或後續修改表 inmemory 屬性的方式進行啟用：
create table test (id number) inmemory;
alter table test inmemory;
2、COLUMN 級啟用：
僅啟用表中某列前，該表必須先設定為 inmemory 模式：
alter table imo_t1 inmemory (id) no inmemory (name,type); alter table imo_t2 inmemory (name) no inmemory (id,type);
SELECT table_name, segment_column_id seg_col_id, column_name, inmemory_compression FROM v$im_column_level WHERE owner = 'IMOTEST' and table_name in ('IMO_T1','IMO_T2') ORDER BY 1,3;
TABLE_NAME SEG_COL_ID COLUMN_NAME INMEMORY_COMPRESSION
---------- ---------- --------------- -----------------------
IMO_T1 1 ID DEFAULT
IMO_T1 2 NAME NO INMEMORY
IMO_T1 3 TYPE NO INMEMORY
IMO_T2 1 ID NO INMEMORY
IMO_T2 2 NAME DEFAULT
IMO_T2 3 TYPE NO INMEMORY
3、表空間級啟用：
可以通過如下初始建立表空間或後續修改表空間 inmemory 屬性的方式進行啟用，在屬性為 inmemory 的表空間中建立的對象自動加載 inmemory 屬性，除非顯示設定對象為 no inmemory：
create tablespace imotest datafile '/u01/app/oracle/oradata/orcl/imotest01.dbf' size 100M default inmemory;
或
alter tablespace imotest default inmemory;
select tablespace_name, def_inmemory from dba_tablespaces where tablespace in ('IMOTEST');
TABLESPACE_NAME DEF_INMEMORY
------------------------------ ---------------
IMOTEST ENABLED
4、如果需要知道具體哪些列開啟了IM column store則需要到V$IM_COLUMN_LEVEL中進行檢視
5、查詢有關IM清單屬性
SQL> SELECT OWNER, SEGMENT_NAME,bytes,INMEMORY_SIZE,POPULATE_STATUS, BYTES_NOT_POPULATED FROM V$IM_SEGMENTS;
OWNER SEGMENT_NA BYTES INMEMORY_SIZE POPULATE_STAT BYTES_NOT_POPULATED
---------- ---------- ---------- ------------- ------------- -------------------
CS MEM 391880704 52428800 COMPLETED 0
CS TEST 99426304 15138816 COMPLETED 0
CS DUP 5095424 3407872 COMPLETED 0
6、關閉inmemory
1.3 inmemory優先級調整
啟用了 IMO 的對象，會按照一定的優先級進入 SGA 中配置好的 IN-MEMORY 區域，同時，在 IN-MEMORY 區域用滿後，依次置換出優先級較低的對象。下表為關于 IMO 對象優先級說明：
優先級描述
PRIORITY NONE 預設級别；執行 SQL 引起對象掃描後，觸發進入 IN-MEMORY
PRIORITY CRITICAL 最高優先級；資料庫啟動後立即進入 IN-MEMORY
PRIORITY HIGH 在具有 CRITICAL 優先級的對象之後進入 IN-MEMORY
PRIORITY MEDIUM 在具有 CRITICAL、HIGH 優先級的對象之後進入 IN-MEMORY
PRIORITY LOW 在具有 CRITICAL、HIGH、MEDIUM 優先級的對象之後進入 IN-MEMORY
修改示例：
SQL> alter table test inmemory priority high;
SQL> col owner format a30
SQL> col segment_name format a30
SQL> SELECT INMEMORY,INMEMORY_PRIORITY,INMEMORY_COMPRESSION,INMEMORY_DISTRIBUTE, INMEMORY_DUPLICATE
FROM USER_TABLES WHERE TABLE_NAME = 'TEST';
INMEMORY INMEMORY INMEMORY_COMPRESS INMEMORY_DISTRI INMEMORY_DUPL
-------- -------- ----------------- --------------- -------------
ENABLED HIGH FOR QUERY LOW AUTO NO DUPLICATE
1.4 加載對象到IM
1、通過全表掃描objects加載資料到IM
SQL> SELECT /*+ FULL (s) */ COUNT(*) FROM table s;
查詢V$IM_SEGMENTS中的資料
SQL> col owner for a10
SQL> col SEGMENT_NAME for a10
SQL> SELECT OWNER, SEGMENT_NAME, POPULATE_STATUS, BYTES_NOT_POPULATED FROM V$IM_SEGMENTS;
OWNER SEGMENT_NA POPULATE_ BYTES_NOT_POPULATED
---------- ---------- --------- -------------------
CS TEST COMPLETED 0
2、DBMS_INMEMORY來加載資料
DBMS_INMEMORY包提供了兩個PROCEDURE來手工加載資料到IM中：
POPULATE：強制加載給定的表
REPOPULATE：強制重新加載給定的表，在該表已經至少加載過一次之後才可使用。
手工執行DBMS_INMEMORY.POPULATE procedure來加載TEST表到IM中
SQL> EXEC DBMS_INMEMORY.POPULATE('CS','TEST');
通過DBMS_INMEMORY.REPOPULATE prcedure及FORCE=>TRUE選項來加載TEST表到IM中。FORCE=>TRUE選項強制進行一次完整重新載入，類似于full refresh
3、設定PRIORITY clause自動加載資料
将測試表TEST的PRIORITY級别改為HIGH。
二、In-Memory測試
2.1 全字段查詢
通過CTAS方式建立表TEST,普通行式掃描與開啟inmemory特性分别測試，比對執行計劃。
no inmemory查詢測試
set timing on
set autotrace traceonly
set pagesize 200 linesize 200
col TABLE_NAME for a20
col INMEMORY_PRIORITY for a20
col INMEMORY_DISTRIBUTE for a20
col INMEMORY_COMPRESSION for a20
全字段查詢，執行計劃
SQL> select count(*) from test;
COUNT(*)
----------
74329
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 403 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 74329 | 403 (1)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
1460 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
開啟in memory查詢測試
SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables;
TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION
-------------------- -------------------- -------------------- --------------------
TEST NONE AUTO FOR QUERY LOW
已開啟inmemory
SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id
from V$INMEMORY_AREA;
POOL ALLOC_BYTES/1024/1024 USED_BYTES/1024/1024 POPULATE_STATUS CON_ID
------------ ------------ --------------------- ---------- ------------------
1MB POOL 815 0 DONE 3
64KB POOL 192 0 DONE 3
--因為隻是把該表設定了INMEMORY,但是未查詢過,是以查詢V$INMEMORY_AREA中未使用相關記憶體--
全字段查詢，執行計劃
第一次執行：
SQL> select count(*) from test;
COUNT(*)
----------
74329
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 403 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS INMEMORY FULL| TEST | 74329 | 403 (1)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5 recursive calls
5 db block gets
1467 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
第二次執行：
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS INMEMORY FULL| TEST | 74329 | 16 (7)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
9 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
查詢inmemory記憶體使用：
SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id
from V$INMEMORY_AREA;
POOL ALLOC_BYTES/1024/1024 USED_BYTES/1024/1024 POPULATE_STATUS CON_ID
-------------------------- --------------------- -------------------- -------------------------- ----------
1MB POOL 815 4 DONE 3
64KB POOL 192 .25 DONE 3
--再次檢視，已經使用了配置設定的In-Memory中記憶體
性能比對：
| sql plan | no-inmemory | inmemory |
|---|---|---|
| consistent gets | 1460 | 9 |
| physical reads | ||
| Cost | 403 | 16 |
結果：
開啟inmemory之後性能提升162倍
2.2 索引字段比較測試
在已開啟IMO特性條件下，通過給表test增加列索引，比較IM與索引執行計劃
1、在object_name上建立索引
2、檢視表TEST是否加載到IM中
col owner for a20
col SEGMENT_NAME for a20
col POPULATE_STATUS for a20
col BYTES_NOT_POPULATED for 99999
SELECT OWNER, SEGMENT_NAME, POPULATE_STATUS, BYTES_NOT_POPULATED FROM V$IM_SEGMENTS;
OWNER SEGMENT_NAME POPULATE_STATUS BYTES_NOT_POPULATED
-------------------- -------------------- -------------------- -------------------
CS TEST COMPLETED 0
表已被加入到IM中
3、使用索引簡單列查詢
SQL> set autotrace traceonly
SQL> select count(*) from test where object_name='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 3197243274
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST_OBNAME | 10 | 350 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
6 consistent gets
2 physical reads
0 redo size
542 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀：6
實體：2
4、強制全表掃描查詢
SQL> select /*+full(s)*/count(*) from test s where object_name='test';
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 124 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS INMEMORY FULL| TEST | 9 | 315 | 124 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - inmemory("OBJECT_NAME"='test')
filter("OBJECT_NAME"='test')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
541 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀：10
實體讀：0
總結：在資料離散度較高，且通過索引條件過濾的掃描場景中，IM特性對性能并沒有提升，傳統的索引+行式存儲的執行計劃已經足夠，在預設情況下還是會根據查詢索引傳回rowid的方式查找資料。
2.3 批量update測試
1、建立測試表disk、emp，分别為im表以及rows表，總數相同
SQL> create table disk as select * from dba_objects;
SQL> insert into disk select * from disk;
74360 rows created.
SQL> /
148720 rows created.
SQL> /
297440 rows created.
SQL> /
594880 rows created.
SQL> /
1189760 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from disk;
COUNT(*)
----------
2379520
SQL> create table mem as select * from disk inmemory;
更新磁盤表
update disk set owner=‘tom’;
更新記憶體表
update mem set owner=‘tom’;
普通表：
時間：14.49
邏輯讀：3202294+894128
實體讀：5602
cost：403
IM表：
時間：18.94
邏輯讀：3231179+254
實體讀：92476
cost：483
總結：IM特性在update性能有所下降
2.4 大批量insert測試
從磁盤插到磁盤
insert into disk select * from cs;
從磁盤插到記憶體
insert into mem select * from cs;
普通表：
時間：0.13
邏輯讀：9332+2903
實體讀：2895
cost：404
IM表：
時間：0.17
邏輯讀：3231179+254
實體讀：92476
cost：483
2.5 大量delete測試
delete from disk where rownum <10000;
delete from mem where rownum <10000;
普通表：
時間：0.08
邏輯讀：11470+202
實體讀：253
cost：12986
IM表：
時間：0.07
邏輯讀：11472+9
實體讀：0
cost：493
2.6 壓縮方式測試
| 壓縮方式 | 描述 |
|---|---|
| NO MEMCOMPRESS | IMO 中存儲無壓縮 |
| MEMCOMPRESS FOR DML | 最小化壓縮，優化 DML 操作 |
| MEMCOMPRESS FOR QUERY LOW | 預設方式：查詢性能最優、空間壓縮效果好于DML方式 |
| MEMCOMPRESS FOR QUERY HIGH | 查詢性能次優(excellent)、空間壓縮效果好于 QUERY LOW |
| MEMCOMPRESS FOR CAPACITY LOW | 查詢性能良好(good)、空間壓縮效果好于 QUERY HIGH |
| MEMCOMPRESS FOR CAPACITY HIGH | 預設設定、空間壓縮效果最優 |
檢視壓縮比
col owner for a5
col SEGMENT_NAME for a10
col POPULATE_STATUS for a10
col INMEMORY_COMPRESSION for a10
SQL> SELECT V.OWNER, V.SEGMENT_NAME,V.BYTES/1024/1024 ORIG_SIZE_MB,V.INMEMORY_SIZE/1024/1024 IN_MEM_SIZE_MB,BYTES_NOT_POPULATED,POPULATE_STATUS status,INMEMORY_COMPRESSION,V.BYTES/V.INMEMORY_SIZE COMP_RATIO FROM V$IM_SEGMENTS V WHERE SEGMENT_NAME = 'TEST';
OWNER SEGMENT_NAME ORIG_SIZE_MB IN_MEM_SIZE_MB BYTES_NOT_POPULATED STATUS INMEMORY_COMPRESS COMP_RATIO
----- -------------------- ------------ -------------- ------------------- -------------------- ----------------- ----------
CS TEST 1466.78125 110.625 0 COMPLETED FOR QUERY LOW 13.2590395
表TEST在磁盤上占用1466MB,采用預設壓縮方式，記憶體中占用110.625MB，壓縮比為13.26：1
壓縮比測試：
表TEST大小1466.78125MB，資料行9514112
1、NO MEMCOMPRESS：
alter table test inmemory MEMCOMPRESS NO MEMCOMPRESS;
邏輯讀：14
實體讀：0
COST: 126
時間： 0.01
壓縮比：1.19
2、MEMCOMPRESS FOR DML：
alter table test inmemory MEMCOMPRESS FOR DML;
邏輯讀：14
實體讀：0
COST: 126
時間：0.01
壓縮比：1.22
3、MEMCOMPRESS FOR QUERY LOW：
alter table test inmemory MEMCOMPRESS FOR QUERY LOW;
邏輯讀：14
實體讀：0
COST:125
時間：0.01
壓縮比：7.20
4、MEMCOMPRESS FOR QUERY HIGH：
alter table test inmemory MEMCOMPRESS FOR QUERY high;
邏輯讀：14
實體讀：0
COST: 125
時間：0.01
壓縮比：9.67
5、MEMCOMPRESS FOR CAPACITY LOW：
alter table test inmemory MEMCOMPRESS FOR CAPACITY LOW;
邏輯讀：14
實體讀：0
COST: 125
時間：0.01
壓縮比：12.91
6、MEMCOMPRESS FOR CAPACITY HIGH：
alter table test inmemory MEMCOMPRESS FOR CAPACITY HIGH;
邏輯讀：14
實體讀：0
COST: 25
時間：0.01
壓縮比：19.14
測試結果彙總：
| 壓縮方式 | 壓縮比 |
|---|---|
| NO MEMCOMPRESS | 1.19 |
| MEMCOMPRESS FOR DML | 1.22 |
| MEMCOMPRESS FOR QUERY LOW | 7.20 |
| MEMCOMPRESS FOR QUERY HIGH | 9.67 |
| MEMCOMPRESS FOR CAPACITY LOW | 12.91 |
| MEMCOMPRESS FOR CAPACITY HIGH | 19.14 |
總結：壓縮方式不同，表加載到IM中的時間也會不一樣。壓縮比越大加載到記憶體中的時間越長。而對于select查詢消耗時間不影響。
2.7 查詢大量列性能測試
三、評估對象在IMO大小
DBMS_COMPRESSION.GET_COMPRESSION_RATIO
在對一張表使用COMPRESSION clause進行IM壓縮級别設定之前，我們可以通過Oracle的COMPRESSION ADVISOR對表放入到IM中的大小進行提前計算。
SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio PLS_INTEGER;
l_comptype_str VARCHAR2(100);
BEGIN
dbms_compression.get_compression_ratio (
-- Input parameters
scratchtbsname => 'CS',
ownname => 'CS',
objname => 'TEST',
subobjname => NULL,
comptype => dbms_compression.comp_inmemory_QUERY_LOW,
-- Output parameter
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => dbms_compression.comp_ratio_allrows);
dbms_output.put_line('Comp. ratio (QUERY LOW):'||l_cmp_ratio);
END;
/
Comp. ratio (QUERY LOW):7
估計結果壓縮比7，實際壓縮比為7.20
#五、RAC環境測試
rac環境獨有參數DUPLICATE clause、DISTRIBUTE clause：
DUPLICATE clause：
此參數為EXADATA一體機專用，在RAC環境中，每個節點擁有自己的IM Area。一個objects根據DUPLICATE clause的設定将一樣的資料加載到多個IM Area中。
預設是NO DUPLICATE設定，表示在資料庫的IM中對一個objects在所有節點中合起來隻儲存一份。舉例說明，比如三節點的RAC中，對于分區表SALES來講可能2012年份的資料在1節點，2013年份的資料在2節點，2014年份的資料在3節點，每個分區隻儲存在一個節點上。
為了提升可用性，也可以設定為DUPLICAET ALL，在每個節點上都儲存一份。舉例說明，還是剛才那個SALES表的請款下，1，2，3三個節點各儲存一份完整sales表資料到各自的IM中。在任意一個節點上都可以擷取查詢需要的資料。
在設定為DUPLICATE ALL的情況下
DISTRIBUTE clause：
如果一個objects因為太大無法被加載到一個IM Area中，還可以通過DISTRIBUTE clause的設定将它分成幾個資料片分别加載到不同的節點中。
預設情況下DISTRIBUTE clause的預設值為AUTO-DISTRIBUTE，這時候是否将objects分布式分布在不同的節點上由Oracle内部算法決定。這個參數對于單執行個體沒有影響，在RAC環境中，預設存在IM中的表會分布在各個節點之中。
RAC環境并不像單執行個體一樣隻需修改表的IM屬性即可啟用，如果要使用IMO，必須在系統層面修改“并行度政策”為自動。下面對參數“parallel_degree_policy”，分别測試2個場景
1、先檢視要測試的表TEST是否已加載到IM STORE
col SEGMENT_NAME for a5
col POPULATE_STATUS for a10
col INMEMORY_DISTRIBUTE for a10
col INMEMORY_DUPLICATE for a20
SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;
INST_ID SEGME INMEMORY_SIZE/1024/1024 BYTES/1024/1024 BYTES_NOT_POPULATED/1024/1024 INMEMORY_D INMEMORY_DUPLICATE POPULATE_S
---------- ----- ----------------------- --------------- ----------------------------- ---------- -------------------- ----------
2 TEST 9.125 192 123.53125 AUTO NO DUPLICATE COMPLETED
1 TEST 16.1875 192 66.9140625 AUTO NO DUPLICATE COMPLETED
1、開啟AUTO DOP
alter system set parallel_degree_policy=AUTO sid='*';
alter system set parallel_force_local=false sid='*';
alter system flush buffer_cache;
alter system flush shared_pool;
2、檢視執行計劃
SQL> select /*+ parallel */count(*) from test;
COUNT(*)
----------
1455168
Execution Plan
----------------------------------------------------------
Plan hash value: 2661943167
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
232 recursive calls
4 db block gets
163 consistent gets
15 physical reads
0 redo size
545 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀：163
實體讀：15
3、不使用并行查詢查詢
SQL> select count(*) from test;
COUNT(*)
----------
1455168
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
9081 consistent gets
9077 physical reads
0 redo size
545 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀：9081
實體讀：9077
4、關閉AUTO DOP
alter system set parallel_degree_policy=MANUAL sid='*';
alter system set parallel_force_local=false sid='*';
alter system flush buffer_cache;
alter system flush shared_pool;
5、檢視執行計劃
SQL> select /*+ parallel */count(*) from test;
COUNT(*)
----------
1455168
Execution Plan
----------------------------------------------------------
Plan hash value: 2661943167
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
209 recursive calls
4 db block gets
17909 consistent gets
17766 physical reads
0 redo size
545 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀：17909
實體讀：17766
總結：
1、調整系統參數(parallel_degree_policy)之後，rac環境下的IM列查詢大幅降低了邏輯讀與實體讀。
2、在多執行個體的并發查詢中執行個體之間傳輸的并不是IMCU，而是每個節點都會對本節點的資料運作相同的sql語句，之後把自己的結果集發送給發起sql語句的執行個體，組成最終的結果傳回給使用者。
| parallel_degree_policy | AUTO | MANUAL |
|---|---|---|
| 邏輯讀 | 163 | 17909 |
| 實體讀 | 15 | 17766 |
備注：
在沒有顯式使用并行sql時，rac環境im全表掃描并沒有使用并行。oracle的優化器會通過一系列的計算比較cost。
SQL> select count(*) from test;
執行計劃Note
-----
automatic DOP:
Computed Degree of Parallelism is 1 because of no expensive parallel operation
In some cases, even with Auto DOP set correctly, the optimizer may calculate the cost of a serial access to be less than the cost of a parallel access. This has been identified as bug 18960760, and will usually only happen when very smalltables in the IM column store are involved in the query.
來自Oracle Blog
https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-i
四、參考文獻
1、Oracle Database In-Memory on RAC - Part I
https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-i
2、Oracle 12c DB In-Memory入門實驗手冊
https://blog.csdn.net/badly9/article/details/49777993
3、Oracle12c IMO 測試
https://www.jianshu.com/p/966ee0182e1c
4、rac并行查詢
http://blog.sina.com.cn/s/blog_74a7d3390102wegl.html
5、Oracle In-Memory白皮書
http://www.oracle.com/technetwork/cn/database/in-memory/overview/twp-oracle-database-in-memory-2245633-zhs.pdf
![ORACLE 12c索引分裂引起的會話夯[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)