今回のSQLチューニングブログ 2nd Season（第16回）は
「SQL チューニングのための情報分析方法」における「業務情報分析及び活用方法」について解説していきます。

3.3　業務情報分析及び活用方法

前述したように性能問題を解決するためには、SQLの実行情報及び関連Objectの情報を把握することが重要です。
そして、それ以外に業務と関連した部分まで確認できれば、もっと簡単にパフォーマンス問題を解決することができるでしょう。
例えば、SQLの照会パターンや照会されるBind変数の値のパターンを把握できれば、インデックスを作成したり、パーティションを
構成するときに参考にすることができるとても良い情報となります。
また、Hintを使用してのパフォーマンス問題を解決する場合にも多くの助けになってきます。
また、SQLの情報を把握する時も同様に、そのテーブルのCommentをよく活用すれば、どのような業務に関連したSQLなのか
把握しやすくなります。このすべてのことは、当該SQLの使用目的と照会パターンなど業務的な性格と密接に関係しているのです。

3.3.1 Access Pattern情報を活用する

SQLチューニングの基本原理は、使用可能な条件を効率的に使うことです。
与えられた条件を効率的に使う上で最も重要なことは、使用可能なIndexの存在有無です。
では、Indexをどのように生成するのが一番効率的でしょうか？SQLを基準に見ると、常に’=’条件で入ってくるカラムが優先され、
その次にNDV(Number of Distinct Value)値が高い(分布が多様な)カラムをIndex生成対象として優先的に考慮することができます。
SQL基準で考えれば、上記の方法でアプローチすれば、大きな無理なくIndexを生成することができますが、実際の業務では
もう少し複雑な確認手続きが必要です。それはシステム状況と業務環境によってIndexの生成を躊躇する理由が存在するからです。

Note. CPU使用率が非常に高いシステム、0.1秒の性能が生産性に影響を与えるMES(Manufacturing Executing System)に関連のクエリ、 空きディスク容量が多くて新規Index作成が容易でない状況、Index作成時に他のクエリに影響を与える状況など、その場合の数が 多いかもしれません。

したがって、Indexを生成するためには、必ず生成しなければならない理由と根拠を確認する必要があります。
更には、カラムの順番と組み合わせを最適化する必要もあります。

一度間違って作られたIndexを削除することは、Indexを生成することよりも難しい作業です。
性能まで考慮した最適なIndexを作る必要があり、更にはAccess Patternの確認と一緒にSQLが持つ性能も一緒に確認しなければ
ならなくなります。

その一方で、Access Patternの確認することは、思った以上に簡単に行うことができます。
私たちは、V$SQLAREA, V$SQLAREA, V$SQL_PLAN, V$SQL_TEXT のような性能ビューがあるため、簡単なジョインによって欲しい
結果を抽出することができます。
意味のあるデータを確認する目的で、ジョインを一つずつ追加していきながら、アルゴリズムに反映すればするほど、パフォーマンス
問題という大きな壁にぶつかります。

パフォーマンス問題を解決するための情報照会が、パフォーマンスビューを構成する様々なテーブルが結合される過程において、
別のパフォーマンス問題を引き起こしてしまう可能性が高まります。
回避する方法として考えられるのは、パフォーマンスビューを使って必要なデータを特定のテーブルに収集して、Indexを生成する
ことから始めていくことです。
また、照会性能以外にもデータを周期的に収集しなければならない理由として、DictionaryデータのAging Outにあります。
Cacheに存在しているデータだけを利用する場合や、特定の曜日だけが実行される場合、もしくは月末だけに実行されるクエリなどが
ある場合には、少なからず必要となるデータが欠落してしまう可能性があります。

■　Access pattern確認Processは大きく収集/加工/抽出の合計４段階で構成されます。

テーブルのカラムが､実際のSQLでどのように使われているのか？を一目で把握することができます。
上記の結果を参考にIndex生成候補のカラムがどのように使われているのか？を確認して、最適なIndexの生成を検討すれば良いのです。
また、特定のテーブルが使われるSQLは性能負荷が高い順に確認することができるので、重要なテーブルの性能管理面においても
活用することが可能です。

3.3.2 Bind Pattern情報を活用する

SQLに含まれて実行されるBind変数情報はとても便利な情報です。
この章のテーマとなるBind Patternとは、SQLに使われるBind変数にどんな値が入力されるかを把握することを意味します。

つまり、Bind Pattern情報は、SQLの照会パターンを意味します。
このデータを活用することで、パーティションを変更する必要があるかどうか？、パーティションの種類、キーなど様々
な情報に活用することができます。また、照会パターンによっては、性能改善方法やアクセス方法などを決定するのに便利です。

■　Bind Patternは dba_hist_bindsql を活用すると照会できます。

[ 特定のSQLに入力されたBind変数の照会 ]

SELECT name 、
        datatype_string 、
        value_string 、
        last_captured
FROM dba_hist_sqlbind s 、
        dba_hist_snapshot sn
WHERE sn.snap_id = s.snap_id
AND s.sql_id = :sql_id
ORDER BY last_captured, name

また、Elapsed Timeの面でパフォーマンス問題を引き起こすSQLが存在する場合、リアルタイムで実行されているパフォーマンス
問題を引き起こすSQLに入力されたBind変数もOracle 11gバージョンから照会が可能です。
上で紹介したスクリプトのように、特定のSQLに入力されたBind変数の値を全て抽出してPatternを見るのも意味がありますが、
現在リアルタイムで実行され、パフォーマンス問題を引き起こしているSQLに入力されたBind変数の照会することも特別な場合に
おいては、とても意味があります。

特別な場合とは、当該SQLのSummary Dataの面では性能上大きな問題にはならないが、特定のBind変数が入力される場合に限り、Elapsed Time、I/O量などの部分で性能問題を引き起こす場合や、照会期間が通常より長く入力された場合など、様々な予期せぬ
Long Running SQLの実行を意味します。

このような問題に直面した場合、下記のようなスクリプトを活用することで、パフォーマンス問題を引き起こしているSQLのBind
変数を簡単に照会して結果を得ることができます。

[ リアルタイムで実行されているSQLのBind変数照会 ]

SELECT bv.name name 、
       bv.posの位置、
       bv.type type、
       bv.value value
FROM v$sql_monitor s 、
       xmltable( '/binds/bind')
                  passing xmltype( s.binds_xml )
                  COLUMNS name VARCHAR2( 30 ) path '@name' 、
                          pos NUMBER path '@pos'、
                          type VARCHAR2( 15 ) path '@dtystr' 、
                          value VARCHAR2( 4000 ) path '.'.
               ) bv
WHERE s.sql_id=:sql_id
AND s.sid=:sid
ORDER BY bv.pos；

3.3.3 Segment Sizeの増加量を活用する

SQLの実行計画のうち、Table Full ScanやIndex Fast Full Scanなど、該当Segmentの全体をI/OするOperationが存在します。
必要に応じてIndexを生成したり、既存に生成されていたIndexでIndex Scanを誘導してみることもできますが、時にはSegment全体を
読む方が性能上有利な場合も存在します。
この場合、Segment Sizeの増加量を確認する手順は必ず必要です。

現状では、Table Full ScanまたはIndex Fast Full Scanで実行することが最適な方法ではあるものの、該当するSegment Sizeが急激に
増加する場合や、特定の業務状況の影響によって指数関数的に増加が見込まれる様な場合では、近い将来にパフォーマンス問題を引き起こす
主な原因として作用する可能性があるからです。

dba_hist_seg_stat, dba_hist_seg_stat_obj を活用すれば、該当情報を抽出することが可能です。

■　尚、抽出方法は下記の通りです。

SELECT TO_CHAR( begin_interval_time , 'yy/mm/dd hh24:mm' ) c1 、
        object_name c2 、
        space_used_total c3
FROM dba_hist_seg_stat s 、
        dba_hist_seg_stat_obj o 、
        dba_hist_snapshot sn
WHERE o.owner = :owner
AND s.obj# = o.obj# とします。
AND sn.snap_id = s.snap_id
AND object_name = :obj_name
ORDER BY begin_interval_time ；

3.3.4 Comment 情報を活用する

SQLの根本的な性能問題を解決するためには、SQLがどのような目的で作成されたのか？・・・を正確に把握しておくことは時として、
重要な情報になることがあります。

SQLの作成意味を把握するのに役立つのが、Table Commentです。
Comment情報と一緒にSQLを把握すれば、クエリの作成意図や業務を理解するのに大きな助けになります。

CommentはALTER文を使ってTABLEとCOLUMNに付与することができます。
最近では、テーブル定義書の内容をComment情報に登録したサイトが多く存在しているので、Comment情報の活用はもう選択肢の1つではなく必須条件であると言えるでしょう。

dba_tab_columns、dba_col_commentsを活用すれば、その情報を抽出することができます。

■　抽出する方法は下記の通りです。

SELECT c.column_name 、
       c.data_type 、
       c.data_length 、
       c.density 、
       c.nullable 、
       c.num_nulls 、
       c.num_distinct 、
       c.num_buckets 、
       c.sample_size 、
       c.last_analyzed 、
       c.data_default 、
       s.column_name 、
       s.コメント
FROM dba_tab_columns c 、
       dba_col_comments s
WHERE c.owner = s.owner
AND c.table_name = s.table_name
AND c.column_name = s.column_name
AND c.owner=:owner
AND c.table_name=:table_name

SQLチューニングブログ 2nd Season（第16回）　終

次回のSQLチューニングブログは

SQLチューニングブログ2nd Season（第17回）
「情報分析による性能改善事例」について

私たちは日本のITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。

日本エクセムのデータベースソリューション

最新情報は公式SNSでも好評発信中！！

---

SQLチューニングブログについてのお問合せは

日本エクセム株式会社
営業推進部
✉ sales@ex-em.co.jp

---