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

Oracle SQLチューニング講座(4):チューニングが必要なSQLを洗い出す (3/3)

[亀田明裕,株式会社アゲハ]
前のページへ 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トレース、実行計画の取得方法と、その見方について説明します。


前のページへ 1|2|3       

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。