チューニングが必要なSQLを洗い出す：Oracle SQLチューニング講座（4）（3/3 ページ）
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）
バッファ読み込みブロック数が多い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を指定した結果です。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
出力例を見ると、BUFFER_GETS列の値で累積読み取り数「96300」、1実行当たりの読み取り数が「24075」と確認できます。
ディスク読み込みブロック数が多いSQL
ディスク読み取り数が多いSQLを洗い出す場合は、ORDER BY句の条件に“disk_reads”を指定します。STATSPACKのレポートでは、「SQL ordered by Reads for DB」セクションの出力に該当します。
ディスク読み込みが多発している場合、効率の悪い索引を使用している、もしくは全表スキャンを行っている可能性が考えられます。ディスクI/Oは、メモリI/Oと比べて大幅に処理速度が劣るため、チューニングによりディスクI/Oを減らすことができないか検討します。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
出力例を見ると、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分の論理読み込みを排除することができる計算になります。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
出力例を見ると、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を実行します。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
今回は、チューニング対象のSQLを動的パフォーマンスビューを使って洗い出す方法を説明しました。次回はSQLトレース、実行計画の取得方法と、その見方について説明します。
Copyright © ITmedia, Inc. All Rights Reserved.