チューニングが必要なSQLを洗い出す：Oracle SQLチューニング講座（4）（3/3 ページ）

» 2004年09月22日 00時00分公開

[亀田明裕，株式会社アゲハ]

前のページへ 1|2|3 　　　　　　

バッファ読み込みブロック数が多いSQL

　バッファ読み取り数に関しては、1実行当たりの読み取り数と累積読み取り数の2つの観点から調査します。1実行当たりのバッファ読み取り数が多いSQLを洗い出す場合はORDER

BY句の条件に“buffer_gets/executions”を、累積読み取り数が多いSQLを洗い出す場合はORDER BY句の条件に“buffer_gets”を指定します。STATSPACKレポートでは、「SQL

statements ordered by buffer gets」セクションの出力に該当する情報となります。

　大量のバッファ読み込みを行っているSQLは、適切でない索引や、連結索引の一部を使用している可能性があります。

　以下の出力例は、buffer_getsを指定した結果です。

SQL_TEXT

      -----------------------------------------------------------------

      --------------------

      ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS

 
      BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME

      -------- ---------- ----------- ---------- ----------- ---------- 

      -------------- ------------- ------------- -------------

      SELECT o_orderkey,o_orderstatus FROM orders WHERE o_orderkey=:b1

      565714CC 3088221154           1          4       96300      90649

          
      24075         22662       4930000       5201728

リスト3　バッファ読み込みブロック数の多いSQLの出力例（クリックすると別ウィンドウで表示します）

　出力例を見ると、BUFFER_GETS列の値で累積読み取り数「96300」、1実行当たりの読み取り数が「24075」と確認できます。

ディスク読み込みブロック数が多いSQL

　ディスク読み取り数が多いSQLを洗い出す場合は、ORDER BY句の条件に“disk_reads”を指定します。STATSPACKのレポートでは、「SQL ordered by Reads for DB」セクションの出力に該当します。

　ディスク読み込みが多発している場合、効率の悪い索引を使用している、もしくは全表スキャンを行っている可能性が考えられます。ディスクI/Oは、メモリI/Oと比べて大幅に処理速度が劣るため、チューニングによりディスクI/Oを減らすことができないか検討します。

SQL_TEXT

      -----------------------------------------------------------------

      -----------------------------------------------------------------

      ----------

      ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS

 
      BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME

      -------- ---------- ----------- ---------- ----------- ---------- 

      -------------- ------------- ------------- -------------

      select  100.00 * sum(case   when p_type like 'PROMO%'    then 

      l_extendedprice * (1 - l_discount)   else 0  end) / 

      sum(l_extendedprice * (1 - l_discount)) as promo_revenue from  

      lineitem,  part where  l_partkey = p_partkey  and 

      l_shipdate >= date '95-03-30'

      54AE6DD0 3192138774           1          1      114571     291138

         
      114571        291138     131140000     374139324

リスト4　ディスク読み込みブロック数が多いSQLの出力例（クリックすると別ウィンドウで表示します）

　出力例を見ると、DISK_READS列の値が「291138」、1実行当たりのDISK_READSの値を表すDISK_PER_RUN列の値も「291138」であることが確認できます。なお、データベースの起動後、初めてそのデータにアクセスする場合は、必ずディスクからの読み込みとなります。このため、これらの情報は、起動後、ある程度時間が経過してから取得した方がよいでしょう。

実行回数の多いSQL

　実行回数が多いSQLを洗い出す場合は、ORDER BY句の条件に“executions”を指定します。STATSPACKのレポートでは、「SQL ordered by Executions for DB」セクションの出力に該当します。

　実行回数の多いSQLは、1回の実行でアクセスするブロック数が少ない場合でも、合計すると非常に多くのブロック数となることがあるため、チューニング対象となります。例えば、1回の実行で20ブロックをスキャンしている（読み込む）SQLが10万回実行されている場合を考えてみてください。

　チューニングにより、スキャンする（読み込む）ブロック数をわずか5ブロック減らせれば、5ブロック*10万回で50万ブロック分、ORACLEブロックサイズが8Kbytesの場合では、4Gbytes分の論理読み込みを排除することができる計算になります。

SQL_TEXT

      -----------------------------------------------------------------

      -----------------------------------------------------------------

      ----------

      ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS

       BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME

      -------- ---------- ----------- ---------- ----------- ---------- 

      -------------- ------------- ------------- -------------

      SELECT n_nationkey,n_name FROM nation WHERE n_nationkey=:b1

      547AE7A4 1851458320         229     791679     23750370          1

            
      30             0      45650000     136377414

リスト5　実行回数の多いSQLの出力例（クリックすると別ウィンドウで表示します）

　出力例を見ると、EXECUTIONS列の値が「791679」であり、1実行当たりの読み取り数が「30」と確認できます。全体の読み取りブロック数注1に対するこのSQLの読み取りブロック数が多いと考えられる場合、SQLチューニング候補とします。

注1：全体の読み取りブロック数

全体の読み取りブロック数は、「session logical reads」統計値が該当します。V$SYSSTATで確認した場合、値はデータベース起動時からの累積値となるので、該当SQLの処理前後の値を取得して、割合を確認します。

　STATSPACKレポートには、これまで説明したセクション以外に、解析回数が多いSQL（SQL statements ordered by Parse

Calls）、共有メモリサイズが大きいSQL（SQL statements ordered by Sharable Memory）、バージョンカウントが多いSQL（SQL

statements ordered by Version Count）もレポートされます。

SQL全文の取得方法

　これまで利用してきたV$SQLビューは、SQLの先頭から1000bytesまでしか表示されませんでした。1000bytesを超える長いSQLを使用している場合には、V$SQL_TEXTを参照することで完全なSQLを取得することが可能です。

　初めに、V$SQLからADDRESS列、HASH_VALUE列の値を確認し、リスト6のSQLを実行します。

set pages 100 feed off timing off echo off lines 140

      SELECT sql_text

      FROM v$sqltext

      WHERE hash_value=1273901568

      and address='54B4DF20'

      ORDER BY piece;

リスト6　全文を取得するSQLの例

SQL_TEXT

      ----------------------------------------------------------------

      select  l_returnflag,  l_linestatus,  sum(l_quantity) as sum_qty

      ,  sum(l_extendedprice) as sum_base_price,  sum(l_extendedprice

      * (1 - l_discount)) as sum_disc_price,  sum(l_extendedprice * (1

 - l_discount) * (1 + l_tax)) as sum_charge,  avg(l_quantity) as

 avg_qty,  avg(l_extendedprice) as avg_price,  avg(l_discount) a

      s avg_disc,  count(*) as count_order from  lineitem where  l_shi

      pdate <= date '1998-12-01' - interval '1' day (3) group by  l_re

      turnflag,  l_linestatus order by  l_returnflag,  l_linestatus

リスト7　SQLを全文出力した出力例

　今回は、チューニング対象のSQLを動的パフォーマンスビューを使って洗い出す方法を説明しました。次回はSQLトレース、実行計画の取得方法と、その見方について説明します。

「次回」へ

「Oracle SQLチューニング講座」バックナンバー