0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@asahide

【Oracle】再暗号化の移行が遅い？PRAGMA UDFとPARALLEL_ENABLEの効果を4パターンで実測

0
Posted at

1. はじめに

既存システムで DBMS_CRYPTO を使って DES で暗号化されているカラムを、AES-256 にマイグレーションしたい。そんな要件で「新テーブルにデータを流し込んでリネーム」という移行方式を採るとき、再暗号化を挟む INSERT SELECT で時間がかかります。

本記事では、4 パターンのベンチマークで PRAGMA UDF と PARALLEL_ENABLE それぞれの効果の大きさ を実測した結果を紹介します。

結論を先に:

  • INSERT句・SELECT句への Parallel ヒント追加だけではダメ！
  • 最速構成（両方指定）にすることで、未指定時と比べて実行時間が 約4分の1（約4倍の高速化） になった
  • PRAGMA UDF 単体の効果は Elapsed で約 11% 短縮
  • PARALLEL_ENABLE 単体の効果は Elapsed で約 72% 短縮
  • 両方セットで使うのが最速

2. 検証環境

項目 値
DB Oracle AI Database 26ai Enterprise Edition Release 23.26.2.1.0
サービス Autonomous Database (Tokyo region)
コンピュート 2 OCPU（4 スレッド）
対象表行数 500,000 行
対象表サイズ 約 176 MB
暗号化列 RAW(2000) × 3 カラム

3. 再暗号化ファンクションの実装

IV（初期化ベクトル）とは
AES-CBC などのブロック暗号モードで、同じ平文を暗号化しても毎回違う暗号文になるように混ぜるランダム値です。本実装では行ごと・列ごとに 16 バイトのランダム IV を生成し、IV + 暗号文 として 1 カラムに格納します。

3.1. 最適化のための 2 つの指定

今回フォーカスするのは以下の 2 つです。

指定 書く場所 役割
PRAGMA UDF パッケージ本体の関数宣言直後 SQL ⇔ PL/SQL 間のコンテキストスイッチを削減
PARALLEL_ENABLE パッケージ仕様の関数宣言 SQL 文からの呼び出しで並列実行を許可

3.2. パッケージの実装（両方ありの版）

CREATE OR REPLACE PACKAGE crypto_migr AS

  -- 旧 DES キー（移行期間中のみ保持）
  C_KEY_DES CONSTANT RAW(8)  := HEXTORAW('xxxxxxxxxxxxxxxx'); -- 適切な値に
  -- 新 AES キー
  C_KEY_AES CONSTANT RAW(32) := HEXTORAW(
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ||
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); -- 適切な値に

  FUNCTION reencrypt_des_to_aes(p_src IN RAW) RETURN RAW
    PARALLEL_ENABLE;

END crypto_migr;
/

CREATE OR REPLACE PACKAGE BODY crypto_migr AS

  FUNCTION reencrypt_des_to_aes(p_src IN RAW) RETURN RAW IS
    PRAGMA UDF;
    v_plain RAW(32767);
    v_iv    RAW(16);
    v_enc   RAW(32767);
  BEGIN
    IF p_src IS NULL THEN
      RETURN NULL;
    END IF;

    -- 旧 DES 復号
    v_plain := DBMS_CRYPTO.DECRYPT(
                 src => p_src,
                 typ => DBMS_CRYPTO.ENCRYPT_DES
                      + DBMS_CRYPTO.CHAIN_CBC
                      + DBMS_CRYPTO.PAD_PKCS5,
                 key => C_KEY_DES);

    -- 新しいランダム IV を生成
    v_iv  := DBMS_CRYPTO.RANDOMBYTES(16);

    -- 新 AES-256 暗号化
    v_enc := DBMS_CRYPTO.ENCRYPT(
               src => v_plain,
               typ => DBMS_CRYPTO.ENCRYPT_AES256
                    + DBMS_CRYPTO.CHAIN_CBC
                    + DBMS_CRYPTO.PAD_PKCS5,
               key => C_KEY_AES,
               iv  => v_iv);

    -- IV を暗号文の先頭に連結
    RETURN UTL_RAW.CONCAT(v_iv, v_enc);
  END;

END crypto_migr;
/

4. ベンチマーク設計

PRAGMA UDF と PARALLEL_ENABLE の組み合わせ 4 パターンで INSERT SELECT の実行時間を比較します。

パターン PRAGMA UDF PARALLEL_ENABLE
P1 naked なし なし
P2 UDF only あり なし
P3 PE only なし あり
P4 both あり あり

関数の中身はすべて同じ（DES 復号 → IV 生成 → AES 暗号化）で、PRAGMAとPARALLEL_ENABLE 句の有無だけが違います。

実行する SQL の骨格は以下の通りです。#FN# の部分に各パターンの関数名を入れ替えて実行しました。

ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION ENABLE PARALLEL DDL;

INSERT /*+ APPEND PARALLEL(n 4) */ INTO customers_e2e_bench n
  (id, name, secret1, secret2, secret3, created_at)
SELECT /*+ PARALLEL(c 4) */
       c.id, c.name,
       #FN#(c.secret1), #FN#(c.secret2), #FN#(c.secret3),
       c.created_at
  FROM customers_e2e c;

各パターン 3 回ずつ実行した平均を利用します。

5. 計測結果

5.1. 4 パターン比較

3 回の平均値を、P1（両方なし）を 1.00 とした相対比率で示します。

パターン PRAGMA UDF PARALLEL_ENABLE Elapsed 比 (P1=1.00) CPU 比 (P1=1.00) CPU/Elapsed 比
P1
naked		 なし なし 1.00 1.00 0.98
P2
UDF only		 あり なし 0.89 0.89 0.98
P3
PE only		 なし あり 0.28 1.09 3.77
P4
both		 あり あり 0.26 0.99 3.75

Elapsed 比（処理時間の比）で見ると、最速の P4 は P1 の 約 1/4（約 4 倍の高速化） になっています。

CPU/Elapsed 比 は、並列化の実効性を示す指標として使えます。

  • 比が 1 に近い → シングルスレッドで動作
  • 比が 並列度 (= 4) に近い → 理想的な並列実行

P1・P2 は CPU/Elapsed 比が 0.98 で、PARALLEL(4) と指示しているのに実効並列度が 1 です。P3・P4 は比が 3.7 を超えており、並列度 4 の実効効率 94% を達成しています。

5.2. 効果の切り分け

各指定を単独で追加したときの効果を見ると、差が鮮明になります。

比較 分離される要素 Elapsed 短縮率
P1 → P2 PRAGMA UDF を追加 11% 短縮
P1 → P3 PARALLEL_ENABLE を追加 72% 短縮
P2 → P4 （UDF あり状態から）PARALLEL_ENABLE を追加 71% 短縮
P3 → P4 （PE あり状態から）PRAGMA UDF を追加 9% 短縮

支配的なのは PARALLEL_ENABLE です。PRAGMA UDF も 1 割前後の効果はあるものの、PARALLEL_ENABLE の 方が当然ながら効果が大きいです。

ただし CPU 時間で見ると PRAGMA UDF の効果はもう少し明確です。

  • P1 → P2: CPU 時間で 11% 削減
  • P3 → P4: CPU 時間で 10% 削減

PRAGMA UDF は「1 行あたりの CPU コスト」を約 10% 削減する効果がある、と整理できます。並列化されていれば、この CPU 削減がそのまま Elapsed の短縮として現れます。

5.3. 実行計画の比較

PARALLEL_ENABLE の有無で、実行計画そのものが別物になります。

PARALLEL_ENABLE なし (P1, P2)

-----------------------------------------------------------------------------------
| Id  | Operation                          | Name                |  TQ  |IN-OUT  |
-----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |                     |      |        |
|   1 |  LOAD AS SELECT                    | CUSTOMERS_E2E_BENCH |      |        |
|   2 |   PX COORDINATOR FORCED SERIAL     |                     |      |        |
|   3 |    PX SEND QC (RANDOM)             | :TQ10000            | Q1,00| P->S   |
|   4 |     OPTIMIZER STATISTICS GATHERING |                     | Q1,00| PCWC   |
|   5 |      PX BLOCK ITERATOR             |                     | Q1,00| PCWC   |
|   6 |       TABLE ACCESS STORAGE FULL    | CUSTOMERS_E2E       | Q1,00| PCWP   |
-----------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
   - PDML disabled because function is not pure and not declared parallel enabled

Note 欄に PDML disabled because function is not pure and not declared parallel enabled と明記されています。Oracle 自身が「関数が pure でも parallel enabled でもないので、並列 DML を無効化した」と教えてくれているわけです。

Operation 側でも PX COORDINATOR FORCED SERIAL となっており、読み取りは並列だが書き込みは直列という構成です。

PARALLEL_ENABLE あり (P3, P4)

--------------------------------------------------------------------------------------
| Id  | Operation                             | Name                |  TQ  |IN-OUT |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |                     |      |       |
|   1 |  PX COORDINATOR                       |                     |      |       |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001            | Q1,01| P->S  |
|   3 |    INDEX MAINTENANCE                  | CUSTOMERS_E2E_BENCH | Q1,01| PCWP  |
|   4 |     PX RECEIVE                        |                     | Q1,01| PCWP  |
|   5 |      PX SEND RANGE                    | :TQ10000            | Q1,00| P->P  |
|   6 |       LOAD AS SELECT (HYBRID TSM/HWMB)| CUSTOMERS_E2E_BENCH | Q1,00| PCWP  |
|   7 |        OPTIMIZER STATISTICS GATHERING |                     | Q1,00| PCWP  |
|   8 |         PX BLOCK ITERATOR             |                     | Q1,00| PCWC  |
|   9 |          TABLE ACCESS STORAGE FULL    | CUSTOMERS_E2E       | Q1,00| PCWP  |
--------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
  • PX COORDINATOR FORCED SERIAL が消えて純粋な PX COORDINATOR に
  • LOAD AS SELECT (HYBRID TSM/HWMB) → 並列ダイレクトパスロード
  • PX SEND RANGE → 各並列スレッドに範囲分散
  • INDEX MAINTENANCE も並列化

Note 欄からも「PDML disabled」のメッセージが消えています。

6. 2 つの指定の役割整理

今回の実測をふまえ、2 つの指定の違いを整理するとこうなります。

指定 働くレイヤー 効果の大きさ 付け忘れの症状
PRAGMA UDF SQL ⇔ PL/SQL 間 CPU コスト 約 10% 削減 関数呼び出しが少し重い
PARALLEL_ENABLE オプティマイザ Elapsed 約 70% 削減 PDML が無効化され並列度が 1 に

両者は役割が違うので、片方だけでは効果が出切らない という話です。PRAGMA UDF の知名度は高いですが、性能インパクトは圧倒的に PARALLEL_ENABLE 側にあります。

最速構成は両方セット（P4） なので、実装ルールとしては常にペアで書くのがおすすめです。

7. 大規模環境への適用イメージ

スレッド数に比例することが確認できたので、行数に対する所要時間は概ね線形に増えます（※1）。

※1 完全な線形スケーリングは仮定
データ量が数十 GB を超えると、物理 I/O のオーバーヘッドなどが顕在化し、完全に線形にはスケールしません。実際のデータ量に近いボリュームで事前検証することを推奨します。

本検証の結果では、同一データ量に対する処理時間は PARALLEL_ENABLE あり版がなし版の約 1/4 に短縮されました。物理コアではなくスレッドにきちんと比例する、というのは分かりやすいですね。

8. まとめ

DES → AES 再暗号化を伴うデータ移行における、PL/SQL ファンクションの性能最適化 TIPS をまとめます。

ポイント 理由
実装ルールは PRAGMA UDF + PARALLEL_ENABLE をペアで 役割が違うが両方セットで最速
効果の支配的要素は PARALLEL_ENABLE Elapsed で約 70% 削減
PRAGMA UDF は CPU コスト 10% 削減 の底上げ効果 並列化されていれば Elapsed にも反映
実行計画の Note 欄を確認 PDML disabled で付け忘れに気づける
CPU/Elapsed 比 で並列化の実効性を判断 値が並列度に近ければ OK、1 に近ければ要改善

PRAGMA UDF、PARALLEL_ENABLEを両方付けることで、性能が引き出せることが確認できました。

なお、乱数生成や索引有無、DBMS_PARALLEL_EXECUTEの活用など、他の方法もありますので、余裕があったらまた検証してみます。

参考

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?