インデックス生成とV$SESSION_LONGOPSビュー

こんなおもむきの質問を受けました。

インデックス生成が長い間(数分以上)修行中なのに、これをV$SESSION_LONGOPSビューを通じてモニタリングできるのか

これに対する返事はV$SESSION_LONGOPSビューに対するマニュアルである程度得られます。

多分6秒以上掛かるクェリならV$SESSION_LONGOPSビューを通じてモニタリング可能だと思われます。

       
UKJA@ukja1021> create table t2
  2  as select level as c1, rpad('x',4000) as c2, rpad('x',4000) as c3
  3  from dual
  4  connect by level <= 1000
  5  ;

Table created.

Elapsed: 00:00:01.17

--　インデックス生成時間をわざと遅延させるための関数(6秒以上）
UKJA@ukja1021> create or replace function fdelay(p1 number, p2 number)
  2  return number
  3  deterministic
  4  is
  5  begin
  6     dbms_lock.sleep(p2);
  7     return 1;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.00

セッション＃1でインデックスを作る途中でセッション＃2でV$SESSION_LONGOPSビューをモニタリングしてみます。

UKJA@ukja1021> -- session #1
UKJA@ukja1021> create index t2_n1 on t2(fdelay(c1,1)+c1);

結果は失望的です。6秒ではなく6分が経ってもV$SESSION_LONGOPSビューには現れません。

UKJA@ukja1021> exec print_table('select * from v$session_longops where sid = 141');

PL/SQL procedure successfully completed.

問題は何でしょうか。マニュアルで説明していないいくつかの制限があるからです。例えばこんな制限たちがあります。

• フルテーブルスキャンの場合テーブルブロック数が10,000個以上の場合にだけモニタリングされます。
  
• インデックスファストフルスキャンの場合にはインデックスブロック数が1,000個以上の場合にだけモニタリングされます。
  
• ハッシュジョインはモニタリングされますけどネステッドループジョインはモニタリングされません。
  
• 同じ理由でインデックスレンジスキャン等はモニタリングされません。
  

(細かい情報はここを参照してください)

テーブルのブルック数を20,000個まで大きくしてみましょう。

UKJA@ukja1021> -- increase the data over 10,000 block
UKJA@ukja1021> insert into t2
  2  select level as c1, rpad('x',4000) as c2, rpad('x',4000) as c3
  3  from dual
  4  connect by level <= 20000
  5  ;

20000 rows created.

Elapsed: 00:00:56.23

そして同一にモニタリングしてみれば次のようにV$SESSION_LONGOPSビューに現れます。

UKJA@ukja1021> -- session #1
UKJA@ukja1021> create index t2_n1 on t2(fdelay(c1,1)+c1);

UKJA@ukja1021> exec print_table('select * from v$session_longops where sid = 141');
SID                           : 141
SERIAL#                       : 593
OPNAME                        : Table Scan
TARGET                        : UKJA.T2
TARGET_DESC                   :
SOFAR                         : 17
TOTALWORK                     : 42276
UNITS                         : Blocks
START_TIME                    : 2009/09/11 09:48:48
LAST_UPDATE_TIME              : 2009/09/11 09:48:57
TIMESTAMP                     :
TIME_REMAINING                : 22372
ELAPSED_SECONDS               : 9
CONTEXT                       : 0
MESSAGE                       : Table Scan:  UKJA.T2: 17 out of 42276 Blocks
done
USERNAME                      : UKJA
SQL_ADDRESS                   : 2FBD3900
SQL_HASH_VALUE                : 3367173127
SQL_ID                        : 2d01s2z4b5z07
QCSID                         : 0
-----------------

UKJA@ukja1021> exec print_table('select * from v$session_longops where sid = 141');

SID                           : 141
SERIAL#                       : 593
OPNAME                        : Table Scan
TARGET                        : UKJA.T2
TARGET_DESC                   :
SOFAR                         : 61
TOTALWORK                     : 42276
UNITS                         : Blocks
START_TIME                    : 2009/09/11 09:48:48
LAST_UPDATE_TIME              : 2009/09/11 09:49:18
TIMESTAMP                     :
TIME_REMAINING                : 20761
ELAPSED_SECONDS               : 30
CONTEXT                       : 0
MESSAGE                       : Table Scan:  UKJA.T2: 61 out of 42276 Blocks
done
USERNAME                      : UKJA
SQL_ADDRESS                   : 2FBD3900
SQL_HASH_VALUE                : 3367173127
SQL_ID                        : 2d01s2z4b5z07
QCSID                         : 0
-----------------

ここで注意すべきのはインデックスを生成する作業自体がモニタリングされるのではなくて、インデックスを生成するためにテーブルフルスキャンをする作業がモニタリング対象ということです。インデックスリビルド作業も同じ原理でモニタリングされます。

V$SESSION_LONGOPSビューは名前だけみれば本当に有用なように見えるが、実際に使おうとすればそんなこんな制約のためにため息つくようになる時が多いですね。