前回の投稿他
前回の投稿ではsys.col_usage$表を使って、あるカラムに関するWHERE句（Predicate）の状況を分析する要領を紹介した。
一方、昨年「V$SQL_PLANでCRUD表モドキを作ってみる③」という記事を書いたのだが、応用編として
インデックス – SQL
V$SQL_PLANを使えば、テーブルとSQLの関係だけでなく、インデックスとSQLの関係を分析することもできます。
例えば、あるインデックスの定義を変更しようとする場合、１つのSQLだけに注目してしまうと他のSQLに影響があることに気づかず新たな問題を引き起こしてしまうかもしれません。
そのような場合、インデックスとSQLの相関表が役に立ちます。
ということを紹介しただけで終わっていた。
最近、実業務でインデックスとSQL(SQL_ID)の関係を一覧化する機会があったので、その要領を紹介しておこうと思う。
調査用SQL
基本はV$SQL_PLANでこれにDBA_INDEXESを結合して所有者とテーブルの情報を取得している。
２１行目の「OPERATION = ‘INDEX’」の条件でインデックス検索に関わるオペレーションに絞り込み
２２行目（ハイライト行）で分析対象となるスキーマ名（例ではSCOTT）を指定している。
前述のCRUD表を作る試みでは、INSERT,SELECT,UPDATE,DELETEを区別していたが、インデックスとSQLの関係においては、WHERE条件によりインデックスを使用してデータにアクセスしたりフィルタリングしたりすることはINSERTを除き同じ挙動であるので、この調査用SQLではSELECT,UPDATE,DELETEの区別を特に行っていない。
select distinct i.OWNER ,i.TABLE_NAME ,s.OBJECT_NAME INDEX_NAME ,s.SQL_ID ,s.PLAN_HASH_VALUE ,s.ID ,s.OPERATION ||' '|| s.OPTIONS operation ,case when s.ACCESS_PREDICATES is not null then 'access: '||ACCESS_PREDICATES when s.FILTER_PREDICATES is not null then 'filter: '||FILTER_PREDICATES else null end as PREDICATES from V$SQL_PLAN s join DBA_INDEXES i on s.OBJECT_OWNER = i.OWNER and s.OBJECT_NAME = i.INDEX_NAME where OPERATION = 'INDEX' and i.OWNER = 'SCOTT' order by i.OWNER ,i.TABLE_NAME ,s.OBJECT_NAME ,s.SQL_ID ,s.PLAN_HASH_VALUE ,s.ID ;
実行例
SQL> select distinct 2 i.OWNER 3 ,i.TABLE_NAME 4 ,s.OBJECT_NAME INDEX_NAME 5 ,s.SQL_ID 6 ,s.PLAN_HASH_VALUE 7 ,s.ID 8 ,s.OPERATION ||' '|| s.OPTIONS operation 9 ,case when s.ACCESS_PREDICATES is not null 10 then 'access: '||ACCESS_PREDICATES 11 when s.FILTER_PREDICATES is not null 12 then 'filter: '||FILTER_PREDICATES 13 else null 14 end as PREDICATES 15 from 16 V$SQL_PLAN s 17 join 18 DBA_INDEXES i 19 on s.OBJECT_OWNER = i.OWNER 20 and s.OBJECT_NAME = i.INDEX_NAME 21 where OPERATION = 'INDEX' 22 and i.OWNER = 'SCOTT' 23 order by 24 i.OWNER 25 ,i.TABLE_NAME 26 ,s.OBJECT_NAME 27 ,s.SQL_ID 28 ,s.PLAN_HASH_VALUE 29 ,s.ID 30 ; OWNER TABLE_NAME INDEX_NAME SQL_ID PLAN_HASH_VALUE ID OPERATION PREDICATES ------ ----------- ----------- ------------- --------------- ---------- -------------------- --------------------- SCOTT EMP PK_EMP 0dmdbcy6g6qsf 169057108 2 INDEX RANGE SCAN access: "EMPNO">0 SCOTT EMP PK_EMP 64xm083yya03t 204855851 2 INDEX UNIQUE SCAN access: "EMPNO"=7876 SCOTT EMP PK_EMP 861msw504avnz 1749432681 2 INDEX FULL SCAN SCOTT EMP PK_EMP 9gs6uhh2jvrb1 2949544139 2 INDEX UNIQUE SCAN access: "EMPNO"=7369 SCOTT EMP PK_EMP caggwncwwcugc 3659136155 2 INDEX UNIQUE SCAN access: "EMPNO"=7369 SCOTT EMP PK_EMP dq4cyhcs58mys 2400378433 2 INDEX FULL SCAN SCOTT EMP PK_EMP fmt934hxtucpa 2949544139 2 INDEX UNIQUE SCAN access: "EMPNO"=7566 7 rows selected.
この例では、PK_EMPというインデックスのみであるが、インデックス毎にSQL_IDが複数存在している。１つのインデックスに紐付くSQLが多いものほど、インデックスを変更することによる影響範囲が広いことがわかる。
さらに、１つのSQL_IDに複数のPLAN_HASH_VALUEが存在している場合があれば、一つのPLAN_HASH_VALUEが複数のSQL_IDに紐付くこともある。（例：PLAN_HASH_VALUE=2949544139、ちなみにPLAN_HASH_VALUEとは実行計画に紐付くユニークな値である。）
つまり、SQL_IDとPLAN_HASH_VALUEは多対多の関係にある。
ID列は、実行計画ツリーから容易に当該インデックスの使用箇所を追跡しやすように表示させてみた。
OPERATION列はインデックス・スキャンの様々な種類を示している。
PREDICATES列は、「ACCESS/FILTER」の違いと実際のWHERE条件の抜粋を示している。
この情報と前回紹介したsys.col_usage$表を使ったWHERE句の分析結果を突き合わせることにより、パフォーマンス問題解決上有益な情報が得られるかもしれない。
今日はここまで
