[DBチューニングコンテスト とんがりナレッジ] インメモリ、非圧縮、パラレル をキーワードに・・・

ペンネーム:ブロンズバード
●TPCH
【チューニング】
インメモリ、非圧縮、パラレルの3つをキーワードに考え、以下の設定を行った。

/*TPCCとの共通設定*/
alter system set log_checkpoint_timeout=0;
alter system set db_block_checksum=OFF;

/*パラレルクエリの設定*/
alter system set parallel_degree_policy=AUTO scope=both;
alter system set parallel_min_time_threshold=AUTO;

/*インメモリ・非圧縮の設定*/
alter system set inmemory_size = 14G scope=spfile;
alter system set inmemory_clause_default = 'INMEMORY NO MEMCOMPRESS PRIORITY CRITICAL';
alter system set pga_aggregate_limit = 12G

/*resultキャッシュの設定*/
alter system set result_cache_mode = FORCE;
alter system set result_cache_max_size = 8G;
alter system set result_cache_max_result = 4;

【実行後の確認】
(1)時間のかかっているWaitEventを確認する。

SQL>select event, count(*) cnt, sum(TIME_WAITED) wt from v$active_session_history group by event order by wt, cnt;

EVENT					CNT	WT
--------------------------------------- ------- ----------
os thread creation	 		 1	      0
control file heartbeat			 2	      0
null event				 4	      0
cursor: pin S				 1	   1056
control file sequential read		 1	   3990
control file parallel write		 1	   7439
db file async I/O submit		 1	  13357
read by other session		 	 3	  16720
db file sequential read			15	  44308
PX qref latch				35	  50615
direct path write temp			17	 128531
oracle thread bootstrap			 4	 235306
direct path read			88	 456384
db file scattered read			90	 516003
external table read			 1	 918773
IM populate completion			 3	2998241

結果、一番時間がかかっているWaitEventが「IM populate completion」で3秒。(WTはマイクロ秒)
#「IM populate completion」はメモリにデータを移入している際のWaitEvent。
そのため、大きく時間がかかっているWaitEventも無く、この情報からチューニングできるものはないと判断した。

(2)インメモリで全て処理したかを確認する。

SQL> select segment_name, inmemory_size, BYTES, BYTES_NOT_POPULATED, populate_status from v$im_segments;

SEGMENT_NAME	INMEMORY_SIZE	BYTES		BYTES_NOT_POPULATED 	POPULATE_STATUS
--------------- --------------- --------------- ----------------------- ---------------
SUPPLIER	14811136	16777216	0 			COMPLETED
PARTSUPP	1385037824 	1543503872	0 			COMPLETED
PART		278003712  	352321536	0 			COMPLETED
ORDERS		1758527488 	1879048192	0 			COMPLETED
CUSTOMER	248250368  	260046848	0 			COMPLETED
LINEITEM	7497056256 	8589934592	0 			COMPLETED

上記の「SEGMENT_NAME」で表示されているTPCHのテーブルは「BYTES_NOT_POPULATED」が0のため、全てインメモリになった。
TPCHには上記以外に2つのテーブル(REGION,NATION)があるが、インメモリになっていない。
しかし、この2つのテーブルはサイズが小さい(64KB未満)ため、インメモリにならない。
なので、(できる範囲で)インメモリで全て処理できたと判断した。

http://www.oracle.com/technetwork/jp/database/in-memory/overview/twp-oracle-database-in-memory-2245633-ja.pdf
→「64KB未満のオブジェクトはメモリに移入されません。メモリが1MBのチャンクに割り当てられると、IM列ストア内の大量の領域が無駄に消費されるためです。」

(3)インメモリのサイズは適切かを確認する。

SQL>select * from v$inmemory_area;

POOL 		ALLOC_BYTES	USED_BYTES	POPULATE_STATUS	CON_ID
----------- ------------------- --------------- --------------- ----------- 
1MB POOL 	1.2010E+10 	1.1245E+10 	DONE 		0
64KB POOL 	3003121664 	53608448 	DONE 		0

USED_BYTESの合計は、約10.5GB(1.1245E+10 + 53608448)程度であった。
そのため、以下のSQLでも使用しているByte数を確認する。
#インメモリPOOLのサイズが1MBと64KBの2種類しか存在しないため、
#先の「64KB未満のオブジェクトがメモリに移入されない」という理由がわかる。

SQL> select sum(inmemory_size), sum(BYTES) from v$im_segments;

SUM(INMEMORY_SIZE) 	SUM(BYTES)
----------------------- --------------
1.1182E+10 		1.2642E+10

結果、インメモリのサイズは現状の14GBではなく13GBでも大丈夫そうだが、
実際に13GBにすると、(2)でのSQLで「BYTES_NOT_POPULATED」が全て0とはならなかった。
(原因は時間切れで調査できず・・・)
なので、このまま変更せずに14GBとした。