「さらに高度なSQL実行計画の取得」のために理解しておくべきこと：しばちょう先生の試して納得！ DBAへの道（改）（3）（1/3 ページ）

データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。今回は「より高度なSQL実行計画を取得するために、理解しておいてほしいこと」を解説します。

[柴田長，日本オラクル株式会社]

※本連載は、日本オラクルの技術解説ブログ「Oracle Technology Network：しばちょう先生の試して納得！DBAへの道」より、提供者の許可の下、最新情報の追記とともに一部改訂して転載するものです。

連載バックナンバー

　皆さんこんにちは。日本オラクルの“しばちょう”こと、柴田長（しばたつかさ）です。

　今回は、第1回「“SQLの実行計画”から処理時間の差を理解する」、第2回「“なぜ、あのSQLは遅いのか”を正しく的確に調査する方法」で体験したSQLの実行計画の取得方法よりも「さらに高度な方法を習得する」上で、事前に理解しておくべき情報を紹介したいと思います。データベース上で実行されたSQL文の各種情報が管理されている「V$SQLビュー」や「共有プール」を理解することで、Oracle Databaseの仕組みに少しずつ近づいていきましょう。

バックナンバー

• 第1回 演習1：実演習前の環境準備
• 第1回 演習2：「バッファキャッシュ」上のデータをフラッシュする
• 第1回 演習3：初期化パラメータ「FILESYSTEMIO_OPTIONS」を適切に設定する
• 第1回 演習4：「TBL2」表と「TBL3」表から、それぞれ1レコード（col1=10000）だけ検索する実行時間を測定する
• 第2回 演習1：TRYユーザーでデータベースへ接続後、「set autotrace on」を実行する
• 第2回 演習2：SQLの実行計画の取得（1）　SELECT文の実行計画を取得する
• 第2回 演習3：SQLの実行計画の取得（2）　「explain plan for」での実行計画の取得方法も確認する
• 第2回 演習4：同じ「全レコードの総数集計」でも実行時間が違う理由を理解する
• 第2回 演習5：「dbms_xplan.display_cursor」ファンクションで原因を追及する

　早速、第2回までの演習で作成したスキーマ環境で、以下の演習にチャレンジしてみてください。

演習1：共有プールのフラッシュ

　「SQL*Plus」から「TRY」ユーザーでデータベースに接続し、「alter system FLUSH SHARED_POOL ;」を実行します。

$ sqlplus / as sysdba
SQL> alter system FLUSH SHARED_POOL;

　第1回の演習2では「バッファキャッシュ」上のデータをフラッシュしましたが、今回は「共有プール」が対象です。

　バッファキャッシュをフラッシュするSQL文が「alter system FLUSH BUFFER_CACHE ;」であったことを少しでも覚えていれば、Oracle Databaseのマニュアル「SQL言語リファレンス」の「ALTER SYSTEM」文のセクション部分で簡単に見つけることが可能になります。「マニュアルを使いこなす」スキルはこの先、大変重要になります。少しずつ慣れていってくださいね。

　ともあれこの演習では、このSQL文の存在を知っておいてもらうことだけではなく、そもそも「共有プールには何がキャッシュされているのか」を覚えておくようにしましょう。

　共有プール内には、「ライブラリキャッシュ」と呼ばれる領域があり、その中にはSQL文の解析済み表現（実行計画など）が含まれています。SQL文を実行した際、ライブラリキャッシュ内に実行計画が存在しなかった場合には、ハード解析（「ハードパース」などとも呼ばれます）を実行して実行計画を作成します。一方、実行計画が存在する（キャッシュされていた）場合には、それを再利用するように動作します。ソフト解析、ソフトパース、ライブラリキャッシュ・ヒットなどと呼ばれます。

　共有プールをフラッシュすることで、このキャッシュされている実行計画を消すことができます。パフォーマンス検証を実施する前に、バッファキャッシュのフラッシュを併せて実行するクセを付けておきましょう。

　ハードパースとソフトパースについて、もう少し詳しく説明します。

　数百、数千ユーザーから頻繁に実行されるようなオンライントランザクション系のデータベースで、索引を使用して数件のレコードの検索結果を戻す「SELECT文」やシンプルな「INSERT文」「UPDATE文」が「SQL実行のたびにハードパースされる」システムは効率的ではありません。例えば「バインド変数」を用いて、可能な限りソフトパースにチューニングすることで、データベースサーバのCPU使用率を半分程度にまで削減できることがあります。

　ハードパースでシンプルな1つのSQLの実行計画を作成するのにかかる時間は、ほんの数ミリ秒から数十ミリ秒かもしれません。しかしその過程では、何十、何百通りもの実行計画の候補が作成され、その中で一番効率的であるとデータベースから判断された実行計画が「1つだけ」選択されます。ともあれハードパースは「CPUリソースを消費するものだ」と理解していただけるでしょう。

　このためにオンライントランザクション系のデータベースでは、実行計画を共有プール内のライブラリキャッシュにキャッシュして使い回す「ソフトパースに変更するチューニング」がとても大切になってきます。

　ただし、ハードパースが常に悪いわけではありません。例えば夜間のバッチ処理やデータウェアハウス系のデータベースでは、1つのSQLの実行時間が数秒から数十秒、数百秒単位と長くなるので、ハードパースの処理時間は埋もれてしまいます。また、「バインドピーク」の課題を解決する方法として、あえてハードパースを選択させるようなチューニング手法もあります。これらについては、また次の機会にご紹介しますね。

　最後に、共有プールのフラッシュを行う際の注意点も補足しておきます。

　共有プールのフラッシュは、共有プール内の断片化を解消して、ORA-4031エラーの発生を抑制することを目的に実行されるケースもあるようですが、正直、お勧めできません。Oracle Databaseでは、バージョンが上がるにつれて共有プールの断片化が発生しにくいように改善され続けています。もしもOracle Database 11g以降のバージョンでORA-4031エラーが発生した場合には、共有プールのフラッシュではなく「共有プールのサイズ拡張」を推奨します。

　それでも運用中に共有プールのフラッシュを実行しなければならない場合には、「フラッシュを実行する際に、データベース内でロック機構が動作すること」と「フラッシュ後にはハードパースが頻発すること」を考慮してください。「データベースの負荷が低い時間帯で実行する」「オンライントランザクションへの性能影響を必ず検討、テストする」ことがポイントです。

演習2：SELECT文にコメントを追加

　「SQL*Plus」から「TRY」ユーザーでデータベースに接続し、「TBL2」表に対してコメントが含まれたSELECT文を実行します。

$ sqlplus TRY/TRY12345
SQL>
select /* practiceSQL1 */ COL1 || ' : ' || COL2 "Record"
from TBL2
where ROWNUM <=3 ;
　
Record
------------------------------------------------------------------------------------
1 : 1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2 : 2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3 : 3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

　今回示したSELECT文には、「/* practiceSQL1 */」というコメントを含めています。アプリケーション開発者の好みにもよりますが、SQL文中にコメントを含めておくと、今後のアプリケーションのメンテナンスを容易にする効果があります。SQLの実行計画をチューニングする目的のヒント句とは異なり、コメントは実行計画に影響を与えません。これはヒント句にスペルミスがあったとしてもコメントとして扱われ、ORAエラーが発生しなかった──という経験があれば、より納得感があるかなと思います。

　実践テクニックを2つ紹介しましょう。

　1つ目は「連結演算子」です。「||」を使うことで「複数の列データの戻り値を連結」できます。上記の例では、COL1とCOL2の列データの間にシングルコーテーションで囲んだ文字列「 : 」を埋め込んであります。このテクニックの応用技には「ある表に作成されている全ての索引を再作成（Rebuild）するSQL文を作る」などがあります。

SQL>
select 'alter index ' || INDEX_NAME || ' rebuild;' "SQL"
from USER_INDEXES
where TABLE_NAME='TBL2';
　
SQL
---------------------------------------------------
alter index PK_TBL2_COL1 rebuild;

全ての索引をRebuildするSQL文の例

　ちなみに、異なるキャラクターセットを持つOS環境間で連結演算子を含むSQL文を使い回す場合には、垂直バーの「||」が正しく変換されない可能性があるので注意してください。この場合に備えて、文字列を連結する「CONCATファンクション」を使うとよいでしょう。

　2つ目は「ROWNUM疑似列」です。冒頭のSELECT文では、「where ROWNUM <=3」が指定されているので、問い合わせによって戻される行数を「3」に制限しています。

　これらの詳細については、SQL言語リファレンスの「SQL文中のコメント」「連結演算子」「ROWNUM疑似列」の項目もぜひ参照してみてください。これまで何げなく使用していたものでも、あらためてマニュアルを確認すると新しい発見があるものです。