月別アーカイブ: 2016年5月

インデックスとSQLの関係を調査する

前回の投稿他

前回の投稿ではsys.col_usage$表を使って、あるカラムに関するWHERE句(Predicate)の状況を分析する要領を紹介した。

一方、昨年「V$SQL_PLANでCRUD表モドキを作ってみる③」という記事を書いたのだが、応用編として


インデックス – SQL
V$SQL_PLANを使えば、テーブルとSQLの関係だけでなく、インデックスとSQLの関係を分析することもできます。
例えば、あるインデックスの定義を変更しようとする場合、1つのSQLだけに注目してしまうと他のSQLに影響があることに気づかず新たな問題を引き起こしてしまうかもしれません。
そのような場合、インデックスとSQLの相関表が役に立ちます。


ということを紹介しただけで終わっていた。

最近、実業務でインデックスとSQL(SQL_ID)の関係を一覧化する機会があったので、その要領を紹介しておこうと思う。

調査用SQL

基本はV$SQL_PLANでこれにDBA_INDEXESを結合して所有者とテーブルの情報を取得している。

21行目の「OPERATION = 'INDEX'」の条件でインデックス検索に関わるオペレーションに絞り込み
22行目(ハイライト行)で分析対象となるスキーマ名(例では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が複数存在している。1つのインデックスに紐付くSQLが多いものほど、インデックスを変更することによる影響範囲が広いことがわかる。

さらに、1つの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句の分析結果を突き合わせることにより、パフォーマンス問題解決上有益な情報が得られるかもしれない。

今日はここまで

sys.col_usage$表でWHERE句を分析する

sys.col_usage$とは

sys.col_usage$を理解するためにはまずヒストグラムを理解する必要がある。

ヒストグラムとはCBOが使用する列分布情報を保持するものであり、列データの分布が不均一な場合はヒストグラムの情報を使用してより良い実行計画を選択する。

ヒストグラムは列データの偏りが高い場合に有用なので、次のような状況では有用ではなく、つまりヒストグラムを作成する意味がない。

  • WHERE句内で指定しない列
    • 絞り込み条件として使用しない列にヒストグラムを作っても無駄
  • 均一な分布
    • データの偏りがない場合
  • 一意な列を含む等価述語

OracleはDBMS_STATSパッケージよって統計情報を取得する際、ヒストグラムを取得すべき列を特定する情報を収集している。
この情報はSMONによって取得されsys.col_usage$表に保持される。

「CBOに関する統計情報は、バックグラウンドプロセスのシステムモニタ(SMON)によってテーブルに記録される。そのようなテーブルの1つ、COL_USAGE$テーブルは、SELECTクエリで使用される述語、つまり、WHERE節で使用される列、および、等号、LIKE、範囲など、述語の種類に関する情報を記録するのに使われる。10g Release 2では、SMONプロセスが20分ごとにこのテーブルを更新する。」
Oracleフォレンジック 第5部 無監査時のデータ窃盗の証拠調査 から

sys.col_usage$表のカラム

sys.col_usage$表各カラムには当該WHERE条件が実行された回数が格納される

  • EQUALITY_PREDS :等価条件
  • EQUIJOIN_PREDS:等価結合条件
  • NONEQUIJOIN_PREDS:不等価結合条件
  • RANGE_PREDS:範囲検索条件
  • LIKE_PREDS:LIKE(またはNOT LIKE)検索条件
  • NULL_PREDS:NULL(またはNOT NULL)検索条件

sys.col_usage$表でWHERE句の分析をする。

sys.col_usage$表は前述のとおり、本来はOracleがヒストグラム作成のために情報を格納する内部表であるが、この表を使えば、(スキーマ)、テーブル、カラム単位でWHERE句にどのような条件が指定されているかを一覧表示させることができる。
19〜33行目で表示させたくないスキーマを指定している。
(右スクロールしてCOLUMN_NAME列よりも右側を表示させる。)

SQL> select
  2   u.NAME             OWNER
  3  ,o.NAME             TABLE_NAME
  4  ,c.NAME             COLUMN_NAME
  5  ,us.EQUALITY_PREDS
  6  ,us.EQUIJOIN_PREDS
  7  ,us.NONEQUIJOIN_PREDS
  8  ,us.RANGE_PREDS
  9  ,us.LIKE_PREDS
10  ,us.NULL_PREDS
11  --,to_char(us.TIMESTAMP,'yyyy/mm/dd hh24:mi:ss') TIMESTAMP
12  from
13        sys.col_usage$ us
14   join sys.obj$       o  on us.OBJ#    = o.OBJ#
15   join sys.col$       c  on us.OBJ#    = c.OBJ#
16                         and us.INTCOL# = c.INTCOL#
17   join sys.user$      u  on o.OWNER#   = u.USER#
18  where u.NAME not in (
19   'APEX_030200'
20  ,'CTXSYS'
21  ,'DBSNMP'
22  ,'EXFSYS'
23  ,'FLOWS_FILES'
24  ,'IX'
25  ,'MDSYS'
26  ,'OLAPSYS'
27  ,'ORDDATA'
28  ,'ORDSYS'
29  ,'SYS'
30  ,'SYSMAN'
31  ,'SYSTEM'
32  ,'WMSYS'
33  ,'XDB'
34  )
35  order by
36   u.NAME
37  ,o.NAME
38  ,c.INTCOL#
39  ;
 
OWNER  TABLE_NAME                     COLUMN_NAME                    EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ----------
OE     CATEGORIES_TAB                 CATEGORY_ID                                 1              0                 0           0          0          0
OE     INVENTORIES                    PRODUCT_ID                                  1              0                 0           0          0          0
OE     INVENTORIES                    WAREHOUSE_ID                                0              1                 0           0          0          0
OE     PRODUCT_INFORMATION            CATEGORY_ID                                 1              0                 0           0          0          0
OE     WAREHOUSES                     WAREHOUSE_ID                                0              1                 0           0          0          0
SCOTT  EMP                            EMPNO                                       2              0                 0           1          0          0
SH     CHANNELS                       CHANNEL_ID                                  0              1                 0           0          0          0
SH     CHANNELS                       CHANNEL_CLASS_ID                            0              1                 0           0          0          0
SH     CHANNELS                       CHANNEL_TOTAL_ID                            0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_ID                                  0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_SUBREGION_ID                        0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_REGION_ID                           0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_TOTAL_ID                            0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_ID                                     0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_CITY_ID                                0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_STATE_PROVINCE_ID                      0              1                 0           0          0          0
SH     CUSTOMERS                      COUNTRY_ID                                  0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_TOTAL_ID                               0              1                 0           0          0          0
SH     PRODUCTS                       PROD_ID                                     0              1                 0           0          0          0
SH     PRODUCTS                       PROD_SUBCATEGORY_ID                         0              1                 0           0          0          0
SH     PRODUCTS                       PROD_CATEGORY_ID                            0              1                 0           0          0          0
SH     PRODUCTS                       PROD_TOTAL_ID                               0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_ID                                    0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_SUBCATEGORY_ID                        0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_CATEGORY_ID                           0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_TOTAL_ID                              0              1                 0           0          0          0
SH     SALES                          PROD_ID                                     1              1                 0           0          0          0
SH     SALES                          TIME_ID                                     1              1                 0           0          0          0
SH     TIMES                          TIME_ID                                     0              1                 0           0          0          0
SH     TIMES                          WEEK_ENDING_DAY_ID                          0              1                 0           0          0          0
SH     TIMES                          CALENDAR_MONTH_ID                           0              1                 0           0          0          0
SH     TIMES                          FISCAL_MONTH_ID                             0              1                 0           0          0          0
SH     TIMES                          CALENDAR_QUARTER_ID                         0              1                 0           0          0          0
SH     TIMES                          FISCAL_QUARTER_ID                           0              1                 0           0          0          0
SH     TIMES                          CALENDAR_YEAR_ID                            0              1                 0           0          0          0
SH     TIMES                          FISCAL_YEAR_ID                              0              1                 0           0          0          0
 
36 rows selected.

昭和記念公園でサイクリング

〜ちょっと時間が経ってしまったけど、とりあえず記録として〜

昭和記念公園でBBQ

取引先の会社から、「5月5日の子供の日に昭和記念公園でバーベキューをするので来ませんか?」とのお誘いを受けたので、せっかくだから自転車を持っていって昭和記念公園のサイクリングコースを走ってみることにした。

昭和記念公園サイクリングコース

昭和記念公園内マップ にあるピンク色の道路がサイクリングコースである。高速道路のインターチェンジのような4つのサークルを結ぶコースには交差点が全く無く、出会い頭の衝突の恐れなしに自由に走り回ることができる。

愛車ブロンプトンで輪行

公園にはレンタサイクルもあるのだが、今回は愛車ブロンプトンをかついで公園に隣接する西立川駅まで輪行していった。

2016-05-05 10.33.06

自転車入り口がわからず自転車をかついで入場

2016-05-05 10.36.44

初めてだったので自転車でどこから入ってよいのかわからず、歩道橋左手の正門で入場券410円を買って自転車をかついで入場する。子供の日は子供の入場料がタダなので非常に混んでいる。

入ってからわかったのだが下の道に自転車用の入り口があって、行列無しで入場することができる。

5月の風は爽やか

昭和記念公園サイクリングコース

5月上旬は1年の中で最も爽快な季節ではないだろうか。サイクリングコースは基本的に一方通行で十分な幅もあるのでマイペースで走ることができる。ロードバイクの人はあまりいなかったがママチャリくらいでまったり走るのには最高なコースだ。

IMG_9849

適当なアップダウンもあり、決して単調なコースではない。走っていてとても楽しい!

昭和記念公園サイクリング

バーベキューが始まるまでの45分間、園内を思う存分約10km走り回って最高にリラックスできた。

帰りは町田駅までサイクリング

立川から町田まで

バーベーキューが終わって解散した後は、小田急線町田駅までの約24キロをサイクリングした。

途中多摩川の土手をしばらく走るところがあったが、空いていたのでのんびり走ることができた。

IMG_9852

ゴールデンウィーク中の幸福な時間だった。