今回の第3章から「SQLチューニングのための情報分析方法」について解説していきます。

まずは3回シリーズで「SQL実行情報の分析及び活用方法」についてを説明します。

それでは、早速スタートしましょう！！

　3.　SQLチューニングのための情報分析方法

Ｑ：SQLだけ見てチューニングが可能ですか？ SQLのPlanだけ見てSQLチューニングが可能ですか？

SQLチューニングをしていると、上記のような方法でチューニングができる場合があります。
しかし、このような限られた情報だけでチューニングした結果は最善の結果だと確信できるでしょうか？

Oracleでは、DBMS_XPLAN、DBMS_SQLTUNE、10046 Traceなどのツールをユーザーに提供しています。
ユーザーはこれらのツールを利用してSQL実行履歴を抽出することで、SQLチューニング作業時に活用しています。

SQLチューニング作業は、SQLのすべての情報を徹底的に分析した結果から 問題認識と改善案 を導き出す必要があります。
そのため、SQLが持つ情報は正確性が保証されなければなりません。
したがって、このようなツールを使ってのSQLチューニング作業が行われるのです。

まず、先に挙げた各ツールが提供する情報と特徴についてよく理解しましょう。
ユーザーが正しい使用方法をきちんと習得できていれば、SQLチューニング作業を行う場合におけるスピードと精度の両方に
良い影響を与えることができるものと私たちは確信しています。

多くの情報と正確な情報を基にした分析結果は、それだけ間違いを最小限に抑えることができます。
したがって、同じSQLを何度もチューニングしなければならないと言うエラーを防ぐことができるでしょう。

これからSQL実行情報と関連したツールを使って照会できる情報と、このような情報と連携して把握しなければならない
Object情報、その他の業務情報などを把握する方法を説明します。
当該ツールから抽出した情報を活用して、SQL性能改善を通じて読者の理解を助けたいと思います。

3.1 SQL実行情報の分析及び活用方法

SQLチューニングの進行過程における核心の一つとして挙げられるのが、SQL実行情報を分析する段階だと思います。

SQLの実行計画と各Operation別の所要時間及びI/O処理量などの情報を得る方法、そしてこれら基にして分析する方法を
習得すれば、SQLの性能問題がどこでどのように発生しているか？について明確に把握することができるでしょう。

問題認識段階で正確な原因が判明した場合、SQLの性能改善作業はすでに半分以上進んだも同然です。

性能改善の対象を抽出後、問題点を把握するためによく使われる代表的なツールについて説明します。
（代表的なツール： 10046 Trace, DBMS_XPLAN,DBMS_SQLTUNE, Dictionary View など ）

3.1.1 10046 Trace情報を活用する

「 10046 Traceは SQLの実行情報を分析する代表的なツールでした 」

なぜ過去形で10046 Traceを紹介したのか？・・・
その理由は、DBMS_XPLANの方が使いやすいために、最近ではその使用が著しく減少したからです。
それでも「10046 Trace」は、Oracle 10g より以前のバージョンでSQLの実行情報を綿密に分析する必要がある場合や、
Oracle 10g以降のバージョンにおいて Wait Event情報、Functionの実行履歴などを分析する必要がある場合などでは
まだ使用されている状況にあります。
今では、DBMS_XPLANがかなりの部分を代替している状況ではあるものの「10046 Trace」が持つ意味や重要性は依然として
残っているため、ツールとしての理解を深めることは必要だと考えます。

■　基本的な使い方とTrace結果分析

10046 Traceを使う方法は難しくありません。
現在接続しているセッションで「10046 Trace」を有効にした後、性能改善の対象となるSQLを実行すると、そのSQLの
Traceファイルが生成されます。

ほとんどの場合、そのTraceファイルをTkprofコマンドでテキストファイル形式で再保存して分析することになります。
「10046 Trace」を実行する前に知っておくべきことは、TraceのLevelを付与することができるという点です。

レベルによって提供する情報が異なります。
11.1では、Tracle Levelとして16, 32が追加され、11.2.0.2バージョンではTracle Levelとして64が追加されました。
しかし、ここでは12 Levelまでについて紹介していきます。

レベル	概要
レベル1	基本情報
レベル4	基本情報、バインド情報
レベル8	基本情報、Wait Event情報
レベル12	基本情報, Bind情報, Wait Event情報

［表 3-1］ 10046 Trace Level

「10046 Trace」は、［ 表3-1 ］のように合計4つのレベルごとに提供するデータが異なります。
最も多くの情報を提供することができる Level 12 では、Traceを実行することで他のLevelと比べて多くの負荷を引き起こすことが、どうしても避けられなくなります。

しかし、性能改善が必要なSQLに対する分析をする場合では、最もおすすめできるレベルは Level 12 です。
したがって、ここから紹介する例では、全部 Level 12でTraceを実行しています。

現在のSession 10046 Traceの使用方法

Alterコマンドを使って､現在のセッションで実行されているSQLの実行情報をTraceすることができます。

Enable : alter session set events '10046 trace name context forever, level 12'；
無効：セッションセットイベント「10046トレース名コンテキストオフ」を変更します； 

特定Session 10046 Traceの使用方法

10046 Traceは現在のセッションだけでなく、他のセッションSQLの実行情報もTrace Fileを生成できます。

Enable : exec sys.dbms_system.set_ev('&v_sid','&v_serial',10046,&tlevel,'')；

Disable: exec sys.dbms_system.set_sql_trace_in_session('&n_sid','&n_serial',false)；

Tkprofを活用した10046 Trace分析

10046 Traceを実行して実行履歴ファイルをOSディレクトリに保存した場合、そのファイルをTkprofを活用して
ユーザーが見やすく下記のようにFormatして確認することができます。

Tkprof を活用した 10046 Trace の分析例

呼び出し回数 CPU経過時間 ディスククエリ現在の行数
------- ------ -------- ---------- ---------- ---------- ----------
パース 1 0.00 0.00 0.00 0 0 0 0 0
実行 1 0.01 0.01 0.00 0 0 0 0 0
フェッチ 39 0.49 0.39 0 3063 0 569
------- ------ -------- ---------- ---------- ---------- ----------
合計 41 0.51 0.40 0 3063 0 569

解析中のライブラリキャッシュのミス：1件
実行中のライブラリキャッシュのミス: 1
オプティマイザモード: ALL_ROWS
ユーザーIDの解析: 88  

行行ソース操作
------- ---------------------------------------------------
    569 HASH JOIN (cr=3063 pr=0 pw=0 time=392772 us cost=904 size=62000 card=496)
     72 TABLE ACCESS FULL PRODUCTS (cr=4 pr=0 pw=0 time=71 us cost=3 size=2160 card=72)
    569 HASH JOIN (cr=3059 pr=0 pw=0 time=391636 us cost=900 size=47120 card=496)
     36 TABLE ACCESS BY INDEX ROWID CUSTOMERS (cr=1303 pr=0 pw=0 time=70 us cost=400 size=344 card=4)
  19094 BITMAP CONVERSION TO ROWIDS (cr=3 pr=0 pw=0 time=1782 us)
      3 BITMAP INDEX SINGLE VALUE CUSTOMERS_MARITAL_BIX (cr=3 pr=0 pw=0 time=10 us)(オブジェクトID 74364)(オブジェクトID 74364)
 918843 PARTITION RANGE ALL PARTITION: 1 28 (cr=1756 pr=0 pw=0 time=298985 us cost=493 size=8269587 card=918843)
 918843 TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1756 pr=0 pw=0 time=214316 us cost=493 size=8269587 card=918843)

■　10046 Traceの利点

前述のように、10046 TraceはDBMS_XPLANが持つ強力な利点により、使用頻度と活用度が低下する傾向にあります。
しかし特定の状況では、DBMS_XPLANも10046 Traceを置き換えることはできません。
例えば、性能改善対象のSQL構文内にFunction呼び出しがある場合が代表例です。
SQLチューニングを進めていくと、SQL内にFunction使用による非効率が発生する場合が多く、このような場合では
「10046 Trace」を活用することで、より正確な性能分析を行うことが可能です。

クエリ内に含まれるFunctionのTrace

10046 Traceの最大の利点は、SQL構文内で実行されるFunctionの実行履歴を抽出できることです。
SQL構文内にFunction呼び出しがある場合、Functionが実行される時に実行されるSQL情報はRecursive Depth:1データ
ですが、DBMS_XPLANは提供しないので、SQL内のFunctionの実行履歴を詳しく分析する場合、10046 Traceをよく使います。
下記の例で、その理由を詳しく説明していきます。

Functionの生成と実行 SQL ：

[Functionの生成］

create or replace function get_max_price(v_prod_id in varchar2)
return number
is
  l_price number;
begin
    SELECT UNIT_PRICE into l_price
    FROM
     ( SELECT UNIT_PRICE FROM COSTS WHERE PROD_ID = v_prod_id ORDER BY TIME_ID DESC )
       WHERE ROWNUM <= 1;
    return l_price;
end;
/

[実行クエリ］

var lastname varchar2(20);
exec :lastname := 'Ruddy';

SELECT  c.cust_first_name|| ' ' || cust_last_name ,
        cust_gender ,
        cust_main_phone_number ,
        cust_street_address ,
        cust_city ,
        cust_state_province ,
        PROD_NAME,
        get_max_price(s.prod_id) price
FROM  sales s
       ,customers c
       ,products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND c.cust_last_name = :lastname
AND c.CUST_MARITAL_STATUS = 'single';

10046 Trace & Tkprof の使用 ：

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch       39      0.49       0.39          0       3063          0         569
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       41      0.51       0.40          0       3063          0         569

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88  

Rows     Row Source Operation
-------  ---------------------------------------------------
    569  HASH JOIN  (cr=3063 pr=0 pw=0 time=392772 us cost=904 size=62000 card=496)
     72   TABLE ACCESS FULL PRODUCTS (cr=4 pr=0 pw=0 time=71 us cost=3 size=2160 card=72)
    569   HASH JOIN  (cr=3059 pr=0 pw=0 time=391636 us cost=900 size=47120 card=496)
     36    TABLE ACCESS BY INDEX ROWID CUSTOMERS (cr=1303 pr=0 pw=0 time=70 us cost=400 size=344 card=4)
  19094     BITMAP CONVERSION TO ROWIDS (cr=3 pr=0 pw=0 time=1782 us)
      3      BITMAP INDEX SINGLE VALUE CUSTOMERS_MARITAL_BIX (cr=3 pr=0 pw=0 time=10 us)(object id 74364)
 918843    PARTITION RANGE ALL PARTITION: 1 28 (cr=1756 pr=0 pw=0 time=298985 us cost=493 size=8269587 card=918843)
 918843     TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1756 pr=0 pw=0 time=214316 us cost=493 size=8269587 card=918843)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      39        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
　SQL*Net message from client                    39        0.00          0.06

Get_max_price()の実行履歴 ：

SELECT UNIT_PRICE 
FROM
 ( SELECT UNIT_PRICE FROM SH.COSTS WHERE PROD_ID = :B1 ORDER BY TIME_ID DESC )
   WHERE ROWNUM <= 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    569      0.09       0.07          0          0          0           0
Fetch      569      0.34       0.43          0      28450          0         569
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1139      0.43       0.51          0      28450          0         569

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=50 pr=0 pw=0 time=0 us)
      1   PARTITION RANGE ALL PARTITION: 28 1 (cr=50 pr=0 pw=0 time=0 us cost=75 size=14820 card=1140)
      1    VIEW  (cr=50 pr=0 pw=0 time=0 us cost=75 size=14820 card=1140)
      1     SORT ORDER BY STOPKEY (cr=50 pr=0 pw=0 time=0 us cost=75 size=19380 card=1140)
    119      TABLE ACCESS FULL COSTS PARTITION: 28 1 (cr=50 pr=0 pw=0 time=0 us cost=74 size=19380 card=1140)

上記の結果から分かるように、10046 Traceの場合はFunction実行部分を別のSQLで実行履歴を提供します。
したがって、Functionが使用されたSQLの性能改善作業を行う場合、10046 Traceを使用することで、正確で且つ迅速な
性能改善作業を行うことができます。

■　10046 Traceの欠点

10046 Traceを使用するためには、基本的にDBサーバーへのアクセスが必要です。
セキュリティが重要視されるシステムでは使用できない場合が多くあります。
また、性能問題を把握する際によく使われている Predicate Information、RemoteSQL情報 など必要な一部の情報が
提供されないため、多くの利点があるツールであるにも関わらず、DBMS_XPLAN の方が多く使われています。

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

次回ブログテーマ

SQLチューニングブログ 2nd Season（第12回）
「 SQL実行情報の分析及び活用方法 （2/3）」 について

■　データベース運用のことなら日本エクセムにお任せください　■

当社の最新情報はSNSでも配信中（フォローをお願いいたします）

掲載内容についてのお問合せは

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

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