皆さんこんにちは、まだ梅雨明けはしていませんがもう暑いですね。この原稿が公開される頃には、もう梅雨が明けて今年も厳しい夏になっていますね。
今回は、Oracle Database 12cR2（Oracle12cR2）で強化されたOracle Database In-Memory（DBIM）の機能について説明しようと思います。強化された機能の中で、特にパフォーマンスに影響する「インメモリ式」と「結合グループ」について説明しますので、参考にしてください。
1. インメモリ式（In-Memory Expressions）
インメモリ式（IM式）は、演算式の計算結果を仮想列として、IM列ストアに格納することができる機能です。これには、データベースが自動的に作成する自動インメモリ式と手動で作成するインメモリ仮想列があります。仮想列は、Oracle Database 11gからの実体がない列を作成できる機能ですが、Oracle12cR2からのIM式では計算済みの値で格納されるので、以下のようなメリットにより効果的に使用することができます。
- 問合せで毎回計算する必要がないので、計算によるオーバーヘッドが削減される
- 通常のIM列と同じように使用できる（SIMDベクター処理、ストレージ索引、圧縮などが使用できる）
また、マテリアライズド・ビュー（MView）も計算済みの値で格納できますが、MViewよりは以下のようなメリットがあり使いやすくなっています。
- 永続的に格納される訳ではない（IM列ストア上だけに格納する）
- クエリー・リライトのような制限がない（問合せでリストされる列をMViewにすべて含めるなど）
- データベースでアクティブな式が追跡されるので、明示的に作成する必要がない（自動インメモリ式）
仮想列について
ご存知ない方のために、ここでOracle Database 11gからの仮想列について簡単に説明します。
仮想列は、式を列として指定することで、通常の列と同じように使用できる機能です（式には、同じ表の列、定数、SQLファンクションおよびユーザー定義ファンクションが使用でき、同じ式は指定できないようになっています）。この列は、式の結果をデータとして格納する必要がないので（必要なときに式から導出されるので）、表サイズを増加させずにSQLを簡略化できるメリットがあります。それ以外にも、以下のようなパフォーマンスのメリットがあるので、式を使用しているSQLでは有効な場合が多い機能になります。
- パーティション・キーとして使用できる（パーティション・プルーニングが使用できる）
- WHERE句の絞込み条件として使用できる（索引も作成できる）
オプティマイザ統計の列統計が収集されるので、式統計（拡張統計）を作成する必要はありません。以下のように、仮想列を追加後にオプティマイザ統計を収集した実行計画（右側）はRowsが正確になっています。
|
SQL> ALTER TABLE t1 ADD (v_id AS (ROUND(id*12/52,2))); SQL> SELECT * FROM t1 WHERE ROUND(id*12/52,2) <= 2; 8 rows selected. 実行計画（オプティマイザ統計を未収集） —————————————— | Id | Operation | Name | Rows | —————————————— | 0 | SELECT STATEMENT | | 200K| |* 1 | TABLE ACCESS FULL| T1 | 200K| —————————————— Predicate Information (identified by operation id): ————————————————— 1 – filter(“T1″.”V_ID“<=2) |
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(NULL,’t1′); SQL> SELECT * FROM t1 WHERE ROUND(id*12/52,2) <= 2; 8 rows selected. 実行計画（オプティマイザ統計を収集） —————————————— | Id | Operation | Name | Rows | —————————————— | 0 | SELECT STATEMENT | | 8 | |* 1 | TABLE ACCESS FULL| T1 | 8 | —————————————— Predicate Information (identified by operation id): ————————————————— 1 – filter(“T1″.”V_ID“<=2) |
（１）インメモリ仮想列（IM仮想列）
まずは、手動で明示的に作成するIM仮想列について説明します。
予め頻繁に使用する式が分かっている場合には、明示的に式を仮想列として、IM列ストアに追加することができます。これは表結合、SELECTリスト、述語条件などに使用される式で作成できます。手順としては、以下のように仮想列を作成して、それをIM列ストアにポピュレートするだけです。
ALTER TABLE <表名> ADD (<仮想列名> [<データ属性> GENERATED ALWAYS] AS <式> [VIRTUAL]); ALTER TABLE <表名> INMEMORY (<仮想列名>);
仮想列をポピュレートするには、初期化パラメータINMEMORY_VIRTUAL_COLUMNSを’DISABLE’以外にする必要があります（デフォルトは’MANUAL’です）。このパラメータを’ENABLE’にすると、仮想列を個別にINMEMORYにする必要はありません（明示的に’NO INMEMORY’と指定しない限りポピュレートされます）。このとき表をIMCUにポピュレート/再ポピュレートすると、対応する仮想列のIMEU（インメモリ式ユニット）がポピュレートされます。そのため、表のIMCUがすべてポピュレート済みの場合（ポピュレート済みで仮想列を追加した場合）には、以下のように再ポピュレートする必要があるので注意してください（FORCEパラメータをTRUEにして、すべて再ポピュレートする必要があります）。
exec DBMS_INMEMORY.REPOPULATE('<スキーマ名>', '<表名>', FORCE=>TRUE);
IMEUのポピュレートの確認は、以下のSQL（IMEUのリスト、IMEU内のCU）で行うことができます（以下のUTL_RAW.CAST_TO_NUMBERファンクションは、列がNUMBERデータ型に対するものになるので、その他のデータ型については第58回を参照してください）。
-- IMEUのリスト
SQL> SELECT allocated_len/1024/1024 alloc_mb, num_rows, num_VIRcols, i.timestamp
2 FROM v$imeu_header i, dba_objects o WHERE i.objd = o.object_id AND o.object_name = 'T1';
ALLOC_MB NUM_ROWS NUM_VIRCOLS TIMESTAMP
---------- ---------- ----------- -----------------
6 490860 1 17-07-07 08:33:56
6 517766 1 17-07-07 08:33:54
6 503424 1 17-07-07 08:33:53
6 487950 1 17-07-07 08:33:52
-- IMEU内のCU
SQL> SELECT column_name, sql_expression, length, UTL_RAW.CAST_TO_NUMBER(minimum_value) min_value,
2 UTL_RAW.CAST_TO_NUMBER(maximum_value) max_value
3 FROM v$im_imecol_cu i, dba_objects o WHERE i.objd = o.data_object_id AND o.object_name = 'T1';
COLUMN_NAME SQL_EXPRESSION LENGTH MIN_VALUE MAX_VALUE
-------------------- ------------------------------ ---------- ---------- ----------
V_ID ROUND("ID"*12/52,2) 5501673 .23 113275.38
V_ID ROUND("ID"*12/52,2) 5849407 113275.62 232759.85
V_ID ROUND("ID"*12/52,2) 5687384 232760.08 348934.62
V_ID ROUND("ID"*12/52,2) 5512570 348934.85 461538.46
これにより、式によるオーバーヘッドが削減されるので、以下のようにパフォーマンスを改善することができます。この例は、SELECTリストに式を使用したSQLに対して、IM仮想列を使用時と未使用時に、200万行のデータで実行した結果になります（実行時間が2.02秒から0.17秒と速くなっているのが分かります）。ただし、計算済みの値を格納するためのサイズが増えるので注意してください。
この式がもっと複雑な式になると、効果が大きくなるということです。また、元データが更新された場合には、IMCUが再ポピュレートされたときに、対応するIMEUも再ポピュレートされます。
（２）自動インメモリ式（自動IM式）
次に、データベースが自動的に作成する自動IM式について説明します。
自動IM式は、式統計ストア（ESS）を使用して、ホットな式のトップ20を非表示の仮想列（SYS_IMEから始まる列名）として作成します。自動IM式のポピュレートは、初期化パラメータINMEMORY_EXPRESSIONS_USAGEを’ENABLE’（デフォルト）または’DYNAMIC_ONLY’にする必要があります（自動IM式では、バイナリJSON列もサポートされていますが、それについては別の機会とさせていただきます）。自動IM式の作成は、DBMS_INMEMORY_ADMINパッケージを使用して、以下の①～③のように行います。
SQL> exec DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS(); — ②
SQL> SELECT table_name, column_name, sql_expression FROM dba_im_expressions; — ③
TABLE_NAM COLUMN_NAME SQL_EXPRESSION
——— ————————- ———————————————
EMPLOYEES SYS_IME00010000001746FD 12*(“SALARY”*NVL(“COMMISSION_PCT”,0)+”SALARY”)
EMPLOYEES SYS_IME00010000001746FE ROUND(“SALARY”*12/52,2)
① ESSで記録された演算式のトップ20をキャプチャする（非表示の仮想列を作成する）
IME_CAPTURE_EXPRESSIONSプロシージャのsnapshotパラメータに、以下のいずれかを指定して実行します。IM列ストアに格納
（または一部が格納）されている表が対象になり、デフォルトINMEMORY列圧縮句が適用されます。
- CUMULATIVE：データベース作成以降のすべてのESSが考慮される
- CURRENT：過去24時間のESSのみが考慮される
② 非表示の仮想列をポピュレートする
表のIMCUがポピュレートしたときに、IMEUもポピュレートされますが、表がポピュレートされないときには IME_POPULATE_EXPRESSIONSプロシージャを使用して、強制的にポピュレートする必要があります。SYS_IME列があるすべての
表が対象になるので、個別の表だけをポピュレートするときにはIM仮想列と同じようにDBMS_INMEMORY.REPOPULATEプロシージャを
使用します。
③ 非表示の仮想列を確認する
DBA_IM_EXPRESSIONSビューを使用して確認します。
ただし、表に作成できる非表示仮想列の最大数は50個です。表の式が上限の50個に達した後は、データベースにより新しいSYS_IME列は追加されないので、DBMS_INMEMORY.IME_DROP_EXPRESSIONSまたはDBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONSプロシージャを使用して、SYS_IME列を削除する必要があります。
また、SQLで式や関数を使用すると、オプティマイザ統計の式統計（拡張統計）が自動的に作成されないことにより、効果的な実行計画を作成できない場合が多いです。Oracle12cR2からの自動IM式を使用することで、頻繁に使用される式を自動的に非表示の仮想列にできるので、式統計を手動で作成する必要がなくなります（私はこれが非常に嬉しいです）。そのため、BIツールなどで式を多く使用しているシステムでは有効な場合が多いと言えます。
（３）式統計ストア（Expression Statistics Store）
最後に、自動IM式に使用されているOracle12cR2からの式統計ストア（ESS）について説明します。
ESSは、式評価についての統計を格納するために、オプティマイザによって保持されるリポジトリです。頻繁に評価される(ホットな)式が自動的に追跡されます（問合せのハード解析時に、SELECTリスト、WHERE句、Group By句などに含まれているアクティブな式が対象になります）。
IM列ストアが有効になっている場合、自動IM式のためにESSが利用されますが（頻度とコストをベースに判断されます）、ESSはIM列ストアから独立したデータベースの永続的コンポーネントであり、無効にはできません。ただし、これからオプティマイザ統計の式統計（拡張統計）を自動的に作成することはありません。そのため、ESSを直性参照して、ホットな式の拡張統計を作成するような使い方もあるかと思います（ESSは、以下のようにDBA_EXPRESSION_STATISTICSビューで参照することができます）。
SQL> SELECT c3 FROM abc1 WHERE c1+c2 < 105; SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SQL> SELECT table_name, expression_id, snapshot, evaluation_count, fixed_cost, 2 dynamic_cost, expression_text, last_modified 2 FROM dba_expression_statistics WHERE table_name = 'ABC1'; TABLE_NAME EXPRESSION_ID SNAPSHOT EVALUATION_COUNT FIXED_COST DYNAMIC_COST EXPRESSION_TEXT LAST_MOD ---------- ------------- ---------- ---------------- ---------- ------------ -------------------- -------- ABC1 1.6850E+19 LATEST 1 3.1710E-08 0 "C3" 16-09-09 ABC1 1.1808E+19 LATEST 1 1.5855E-06 0 "C1"+"C2" 16-09-09
2. 結合グループ（Join Groups）
結合グループは、フィルター条件で使用するディレクトリ圧縮（圧縮した状態での処理）が、結合でも使用できるようにする機能です。結合は、これまで圧縮の状態で処理できなかったので、結合対象の行数が多くなるとパフォーマンスの問題になることもありました。Oracle12cR2からは、結合グループを作成することで、ハッシュ結合が圧縮した状態で結合できるようになり、Probe表（後からアクセスする表）の対象行が多いSQLでも、効果的に結合できるようになります（ハッシュ結合については、第46回を参照してください）。ただし、結合グループが効果的なのは、以下のような結合前に行数を削減する機能が有効でないときです。
- ブルーム・フィルターの効果が少ないまたは動作しない（Probe表の行数が多く有効なフィルター条件がない）
- 第29回のGroup By Placementが動作しない（Group By句があるSQLでProbe表の結合列に重複値が多くない）
- 第54回のベクターGroup Byが動作しない（集計による行数の削減効果が少ない）
例えば、スター・スキーマのように複数の結合キーで一意になり、それに複数の表が結合され、ベクターGroup byが動作しないようなSQLに効果があります。
それでは、結合グループの詳細として、動作、作成方法、確認方法、実行例について説明していきます。
（１）結合グループの動作
まずは、結合グループが作成されたときの動作について説明します。
結合グループは、結合列で共有ディクショナリを作成することで、ディクショナリ・コードで結合できるようになります。そのため、ハッシュ結合するときに、PGA上にハッシュ・テーブルではなく、ディクショナリ・コードの配列を作成して、結合するようになります。この共通ディクショナリには、以下のようなメリットもあります。
- 共通ディクショナリのコードでローカル・ディクショナリ内の値をエンコードする
ローカル・ディクショナリ（これまでのディクショナリ）には、元データ値ではなく、共通ディクショナリのコードが格納され、それにより圧縮が提供されキャッシュ効率が向上します。 - ディクショナリ・コードを使用して結合できる
PGA上に非重複ディクショナリ・コードで配列を作成するため、使用するサイズやオーバーヘッド（行ソースのコピー、ハッシュ結合するときのハッシュ関数、圧縮の解凍）を削減することができます。
（２）結合グループの作成
次に、結合グループの作成方法について説明します。
結合グループの作成は、CREATE INMEMORY JOIN GROUP文で作成するだけですが、このときポピュレート済みの表は無効になるので注意してください。そのため、結合グループの作成後にポピュレートするようにしてください（以下の例のように実行します）。
-- 1.結合グループを作成 -- CREATE INMEMORY JOIN GROUP <結合グループ名> ( <表名1>(<結合列名1>), <表名2>(<結合列名2>) ); SQL> CREATE INMEMORY JOIN GROUP sales_products_jg ( sales(prod_id), products(prod_id) ); -- 2.結合グループに関する情報を確認する SQL> SELECT joingroup_name, table_name, column_name, gd_address FROM dba_joingroups; JOINGROUP_NAME TABLE_NA COLUMN_ GD_ADDRESS ------------------ -------- ------- ---------------- SALES_PRODUCTS_JG SALES PROD_ID 00000000A142AE50 SALES_PRODUCTS_JG PRODUCTS PROD_ID 00000000A142AE50 -- 3.結合グループで参照している表のINMEMORY属性を有効にして、表のポピュレートと完了の確認 SQL> ALTER TABLE sales INMEMORY; SQL> ALTER TABLE products INMEMORY; SQL> SELECT /*+ FULL(s) */ COUNT(*) FROM sales s; -- sales表をポピュレート SQL> SELECT /*+ FULL(p) */ COUNT(*) FROM products p; -- products表をポピュレート SQL> SELECT segment_name name, partition_name, populate_status status, bytes, bytes_not_populated 2 FROM v$im_segments; NAME PARTITION_NAME STATUS BYTES BYTES_NOT_POPULATED -------------------- -------------------- ------------- ---------- ------------------- PRODUCTS COMPLETED 19218432 0 SALES COMPLETED 78028800 0
（３）結合グループの使用の確認
次に、結合グループの使用の確認方法について説明します。
結合グループが使用されたかは、SQL監視を使用して確認を行います。以下の例は、 DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML.EXTRACTファンクションを使用したものです（Enterprise ManagerのSQL監視でも確認できます）。
- 監視する必要があるSQLのSQL IDを取得する
例えば、監視する必要があるSQLを実行して、V$SESSION.PREV_SQL_IDを問合せます。 - DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML.EXTRACTファンクションで結合グループの使用を調べる
このSQLで行が返された場合は、結合グループが使用されたことになります（以下のSQLは、SQL監視情報をXML形式で取得して、
OperationがHASH JOINのid=’9′(Columnar Encodings Leveraged)の統計を取得しています）。
（４）実行例
最後に、結合グループの実行例について説明します。
以下は、二つの表（t1が8000万行、t2が1万行）を作成して、結合グループを作成したときと未作成のときに実行した結果になります（実行時間が3.39秒から1.31秒と速くなっているのが分かります）。これがもっと行数が多いProbe表になると効果が大きくなるということです。
SQL> CREATE TABLE t1 AS — 80,000,000(10,000 * 8,000)行のデータを生成
2 SELECT MOD(ROWNUM, 8000000) col1, — 10%が一意なデータ
3 MOD(ROWNUM, 1000000) col2, LPAD(‘*’, 20, ‘*’) col3
4 FROM (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10000),
5 (SELECT LEVEL FROM DUAL CONNECT BY LEVE <= 8000);
SQL> CREATE TABLE t2 AS — 10,000行のデータを生成
2 SELECT ROWNUM col1, — すべて一意なデータ
3 MOD(ROWNUM, 100) col2, LPAD(‘*’, 20, ‘*’) col3
4 FROM DUAL CONNECT BY LEVEL <= 10000;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(NULL,’t1′);
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(NULL,’t2′);
— 結合グループの作成
SQL> CREATE INMEMORY JOIN GROUP t1_t2_jg1 ( t1(col1), t2(col1) );
…<ポピュレート>…
SQL> SELECT /*+ NO_VECTOR_TRANSFORM NO_PX_JOIN_FILTER(t1) MONITOR */ COUNT(*)
2 FROM t1,t2 WHERE t1.col1=t2.col1;
COUNT(*)
———-
100000
Elapsed: 00:00:01.31
— 結合グループの未作成
SQL> DROP INMEMORY JOIN GROUP t1_t2_jg1;
…<ポピュレート>…
SQL> SELECT /*+ NO_VECTOR_TRANSFORM NO_PX_JOIN_FILTER(t1) MONITOR */ COUNT(*) FROM t1,t2 … ;
…
Elapsed: 00:00:03.39
上記のSQLでは、結合前に対象行数を削減しないように、NO_VECTOR_TRANSFORMヒント（ベクターGroup Byが動作しないように）とNO_PX_JOIN_FILTERヒント（ブルーム・フィルターが動作しないように）を使用しています（このように二つの表の結合では、大きな効果が出る条件は難しいということです）。また、結合グループの使用をSQL監視で確認するので、MONITORヒントを使用して実行時間が5秒未満でも参照できるようにしています。
3. おわりに
今回はOracle Database 12cR2のDatabase In-Memoryについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。