Oracle 11g 不可視索引に関する検証 その１
＜Oracle 11g 不可視索引に関する検証 その１＞
ペンネーム： グリーンペペ
パフォーマンスチューニングの一環として索引を追加する際に、その効果につ
いて事前に評価する必要がある場合があります。そんな時に便利な機能が11g
より実装されました。”不可視索引”です。
■”不可視索引”とは?
“不可視索引”の詳細は「Oracle Database 管理者ガイド 11gリリース1(11.1)」
に詳細が記載されていますので抜粋します。
リリース11gからは、不可視索引を作成できます。不可視索引とは、セッシ
ョンまたはシステム・レベルでOPTIMIZER_USE_INVISIBLE_INDEXES初期化パ
ラメータを明示的にTRUEに設定しないかぎり、オプティマイザで無視される
索引です。索引を使用禁止にしたり削除するかわりに、索引を不可視にでき
ます。
不可視索引を使用すると、次の操作を実行できます。
– 索引を削除する前に、削除した状態をテストできます。
– アプリケーション全体に影響を与えずに、アプリケーションの特定の操作
またはモジュールに対して一時的な索引構造を使用できます。
■検証環境
早速以下環境でテストしてみます。
Red Hat Enterprise Linux Server release 5 (Tikanga)
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
■検証
▽optimizer_use_invisible_indexesパラメタで不可視索引の使用可否を設定します。
デフォルト値はFALSE(不可視索引を使用しない)です。
SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ----------------------------- optimizer_use_invisible_indexes boolean FALSE
▽索引作成時にinvisibleパラメタを付加すると不可視索引を作成します。
SQL> create index idx_emp on emp(ename) invisible;
▽索引作成時にvisible|invisibleパラメタを設定できます。
何も指定しないとvisibleになります。
SQL> create index idx_emp on emp(ename); SQL> select index_name,visibility from ind where index_name='IDX_EMP'; INDEX_NAME VISIBILITY ------------------------------ ---------- IDX_EMP VISIBLE
▽索引をalter文で不可視索引に変更することができます。
SQL> alter index idx_emp invisible;
▽optimizer_use_invisible_indexes=falseになっているので索引は使用されません。
SQL> select * from emp where ename='SMITH'; 経過: 00:00:00.07 実行計画 --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| |* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| --------------------------------------------------------------- 統計 ---------------------------------------------------------- 0 db block gets 2782 consistent gets 0 physical reads
▽不可視索引を使用可能にしてみます。
SQL> alter session set optimizer_use_invisible_indexes=true; SQL> select * from emp where ename='SMITH'; 経過: 00:00:00.01 実行計画 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| |* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| ---------------------------------------------------------------------------- 統計 ---------------------------------------------------------- 0 db block gets 3 consistent gets 0 physical reads
実行計画を比較すると不可視索引が使用され、経過時間も0.07秒から0.01秒に
改善されています。db block gets+consistent gets(DBバッファキャッシュか
ら取得したブロック数)も2782ブロックから3ブロックに改善されました。
■まとめ
特定セッションに限定して不可視索引を使用すれば、新規索引を本番適用する
前に効果を測定できます。
また10gまでは、新規索引を付与すると他のSQL文の実行計画が変わってしまう
おそれがありますが、不可視索引を使用すれば特定トランザクションのみ索引
を使用するように指定できます。
チューニングに威力を発揮する新機能のひとつとして挙げられるのではないで
しょうか。
中華料理三昧！！ 北京より