Oracleで暗号化に関する検証 その7

<Oracleで暗号化に関する検証 その7>
ペンネーム:ウィーット

検証環境は以下の通りです。
OS : Asianux 3
Oracle : Oracle10gR2
CPU : Athlon 777MHz
メモリ : 512MB

前回に引き続き、今回は詳細にtkprofで整形したSQL_TRACEを見ていきます。
1つのSQL(SELECT、UPDATE、DELETE)が実行されると様々な再帰的コール(RECU
RSIVE CALL)が発行される事があります。この再帰的コールとは、ユーザーが
発行したSQLを実行するために必要となるSQLをOracleが追加発行するものです。
再帰的SQLとも言います。再帰的コールが発生すると、再帰的コールの要因と
なったSQL(ユーザーが発行したSQL)に加えて再帰的SQLの統計が表示されます。
各SQLの最後の「recursive depth」値で再帰的コールか判断します。この値が
「1」以上の場合、再帰的コールとなります。(「0」の場合はユーザーが発行し
たSQLを意味しかつ、「0」とは表示されません。)

例)再帰的コールの統計情報
最終行に「recursive depth」が表示されている(←印)。

=====================================================================
select owner#,mkeyid,encalg,intalg,colklc,klclen,nvl(flag,0)
from
 enc$ where obj#=:1

call     count     cpu   elapsed    disk     query   current     rows
-------  -----  ------  -------- -------  --------  --------  -------
Parse        1    0.02      0.01       0         0         0        0
Execute      1    0.03      0.01       0         0         0        0
Fetch        2    0.00      0.00       2         4         0        1
-------  -----  ------  -------- -------  --------  --------  -------
total        4    0.05      0.03       2         4         0        1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE

Parsing user id: SYS   (recursive depth: 1)         <-------
=====================================================================

SQL_TRACEを取得すると、その取得範囲で捕捉したSQL文全体の統計情報が以下
のように出力されます。

=====================================================================
非再帰的SQL文の統計情報の合計=
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

再帰的SQL文の統計情報の合計=
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

ユーザーが発行したSQL数
    user  SQL statements in trace file.

内部的に発行されたSQL数
    internal SQL statements in trace file.

SQL_TRACE取得中に実行された総SQL数
    SQL statements in session.
=====================================================================

それでは、前回検証時に発行したInsert文のSQL_TRACEを見てみましょう。
暗号化されることで再帰的コールが発行されるのでは??

ありました!SQL_TRACEを比較したところ暗号化の項目があるInsert処理では、
「ENC$」という表に対してアクセスがありました。

=====================================================================
select owner#,mkeyid,encalg,intalg,colklc,klclen,nvl(flag,0)
from
 enc$ where obj#=:1

call     count     cpu   elapsed    disk    query    current     rows
------- ------  ------  --------- ------ -------- ----------  -------
Parse        1    0.02      0.01       0        0          0        0
Execute      1    0.03      0.01       0        0          0        0
Fetch        2    0.00      0.00       2        4          0        1
------- ------  ------  --------- ------ -------- ----------  -------
total        4    0.05      0.03       2        4          0        1


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL ENC$ (cr=4 pr=2 pw=0 time=839 us)
=====================================================================

上記のアクセスを考慮してSQL_TRACEの最後にに表示される全体の統計情報を
みてみましょう。

TDEを使用している全体の統計情報

=====================================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count     cpu   elapsed    disk     query   current     rows
-------  -----  ------  --------  ------  --------  --------  -------
Parse        3    0.02      0.03       0         0         0        0
Execute      4    0.01      0.03      12         5        12        1
Fetch        0    0.00      0.00       0         0         0        0
-------  -----  ------  --------  ------  --------  --------  -------
total        7    0.03      0.07      12         5        12        1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count     cpu   elapsed    disk     query   current     rows
-------  -----  ------  --------  ------  --------  --------  -------
Parse       18    0.07      0.05       0         0         0        0
Execute     41    0.16      0.15       0         0         0        0
Fetch       71    0.02      0.02      20       144         0       57
-------  -----  ------  --------  ------  --------  --------  -------
total      130    0.25      0.23      20       144         0       57

Elapsed times include waiting on following events:
  Event waited on                     Times   Max. Wait  Total Waited
  --------------------------------   Waited  ----------  ------------
  db file sequential read                20        0.00          0.00
    4  user  SQL statements in session.
   41  internal SQL statements in session.        <-----ここ!
   45  SQL statements in session.
    0  statements EXPLAINed in this session.
=====================================================================

TDEを使用していない全体の統計情報

=====================================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count     cpu   elapsed    disk     query   current     rows
-------  -----  ------  --------  ------  --------  --------  -------
Parse        3    0.01      0.03       0         0         0        0
Execute      4    0.02      0.01       9         5         9        1
Fetch        0    0.00      0.00       0         0         0        0
-------  -----  ------  --------  ------  --------  --------  -------
total        7    0.03      0.04       9         5         9        1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count     cpu   elapsed    disk     query   current     rows
-------  -----  ------  --------  ------  --------  --------  -------
Parse       15    0.05      0.03       0         0         0        0
Execute     20    0.13      0.11       0         0         0        0
Fetch       34    0.01      0.01      12        66         0       24
-------  -----  ------  --------  ------  --------  --------  -------
total       69    0.19      0.16      12        66         0       24


Elapsed times include waiting on following events:
  Event waited on                     Times   Max. Wait  Total Waited
  --------------------------------   Waited  ----------  ------------
  db file sequential read                12        0.00          0.00
    4  user  SQL statements in session.
   20  internal SQL statements in session.        <-----ここ!
   24  SQL statements in session.
    0  statements EXPLAINed in this session.
=====================================================================

暗号化の処理において「ENC$」へアクセスが発生し、発行される再帰的コール
のSQL数の違いとなって出てきていると思われます。
(「ここ!」の「internal SQL statements in session.」を参照)

TDEの暗号化有無の表への「Insert」処理を検証した結果、暗号化された表への
再帰的コールの数が増えていることがわかりました。
再帰的コールが多くなり、処理するSQLが多くなったため処理時間がのびていま
す。

寒くてコタツから出れない~