OracleとSQL Server、チューニングの違いを知る：RDBMSアーキテクチャの深層（5）（2/2 ページ）

» 2004年10月28日 00時00分公開

[太田亮, 安間裕，アクセンチュア・テクノロジー・ソリューションズ]

オプティマイザと統計情報

　SQL ServerもOracleもSQLを実行するに当たり、「オプティマイザ」と呼ばれるプログラムが、どう処理したら効率がよいかを解析し、「実行計画」を立てます。SQLの実行パフォーマンスを向上させるためには、どうすれば効率のよい実行計画になるかを探る必要があります。

SQL実行計画の確認手順

　SQL ServerとOracleのどちらも、GUIまたはテキストから実行計画を確認できます。Oracleの場合はまずutlxplan.sqlという製品付属のスクリプトを実行し、実行計画を取得する準備を整える必要があります。SQL Serverの場合、特に実行計画を取得するための準備はありません。

　実行計画をGUIで確認するには、Oracleでは「Performance Manager」のSQL実行計画ツールを使用します（図5）。SQL Serverでは「クエリアナライザ」の「実行プラン」タブより、SQLの実行前後で確認します（図6）。

図5　OracleのPerformance Managerで実行計画を表示

図6　SQL Serverのクエリアナライザで実行プランを表示

　また、両製品ともSQLを実行した直後に、実行計画を出力する機能があります（図7）。Oracleの場合はSQL*Plusで接続後に「SET AUTOTRACE ON」コマンドを実行すると、次回のSQL実行から実行計画が出力されます。utlxpls.sqlという付属のスクリプトがあり、これを実行すると、より読みやすい実行計画が出力されます。

　SQL Serverのクエリアナライザでは、「SET SHOWPLAN_TEXT ON」を実行しておくと、次回のSQL実行から実行計画が出力されます。

図7　OracleとSQL Serverで実行計画を出力^注2

（注：⇒記号は表示の都合で折り返していることを表します）

注2

図7でSQL Serverの実行結果に間違ったデータを掲載していたため、正しいデータに差し替えました。またこれに伴い、Oracleの実行結果のデータもSQL Serverと同じオブジェクトに対して同じSQLを発行したものに差し替えました（2004年10月28日）。

統計情報のメンテナンス

　SQL実行計画は「コストベース」、つまりSQLの実行結果について統計を取り、それに基づいて「最も速そうな検索方法」の計画が立てられます。刻一刻と変化するデータベースの使用状況に応じた、最適な方法でSQLが実行されるには、統計情報のメンテナンスが不可欠です。

　SQL Serverは自動で統計情報が収集され、任意のタイミングで手動でも統計情報を作成／更新できる、というスタンスです。一方のOracleは、ANALYZE文かDBMS_STATSパッケージを実行しない限り統計情報は収集されません（Oracle 10gでは自動収集になりました）。Oracleのジョブスケジューリング機能（DBMS_JOB）やOS機能のタスクスケジューラ機能などと組み合わせて、自動化することは可能です。

　また、統計情報を更新したり、同じデータベースを新しい環境へ移行するときには、それまでと急激に実行計画が変わり、一時的にパフォーマンスが落ちることがありますが、Oracleには「プラン・スタビリティ」という機能があり、特定のSQLに対する統計情報を別環境へ移送して、効率的な実行計画を維持することができます。

Point

実行計画をGUIで確認するには、Oracleでは「Performance Manager」、SQL Serverでは「クエリアナライザ」を使用する。
統計情報はSQL Serverでは自動でメンテナンスされるが、Oracleでは手動でメンテナンスするか、自動化するには明確に条件を定めてスケジュールする必要がある。

ディスク・ファイルとメモリの領域管理

ディスク・ファイル領域管理のチューニング

　ハードウェアの視点から考慮されるべきパフォーマンス・チューニングは、ディスク上のファイル配置とメモリです。OracleとSQL Serverでは、似て非なる領域管理の単位になっています（図8）。

図8　OracleとSQL Serverの領域管理

　ここで両製品の違いは、「領域が拡張するときの制御」についてOracleの方が細かく指定できる／しなければならないことです。これは、それぞれのアーキテクチャとコンセプトの違いに由来するものです。

　Oracleの場合、多くのパラメータでち密な領域管理が可能ですが、設定値を決定するためには、インプットとなる非常に多くの要件を集める必要があります。そもそも領域の拡張パラメータは、ディスクを使いきれない特殊な理由がない限りは、サイズ見積もりで耐用年数分が収まりきる「拡張しない」設定にすべきです。テーブルやインデックスのサイズについては、データの想定件数が分かれば、マニュアルなどに記載された方法でほぼ正確に見積もることが可能です。

　そのほかの領域に関するパラメータに対し、システムごとに最適値を計算するには、相当のノウハウが必要です。可能であればOracle認定コンサルタントに総合的な判断を仰ぐか、まずは実運用に近いデータでトランザクションを実行するテストを行ったうえで、領域の使用率や断片化の度合い、待ちイベントなどを調査し、明らかに問題となる部分から修正していく、というアプローチになります。

　また、特にハードウェアRAID構成にできない場合、複数のファイルを別々のディスク上に配置して表領域・ファイルグループを作成することで、ソフトウェア的にRAID0の効果を得ることができます。ただしOracleの場合、テーブルやインデックスの作成時に、明示的にエクステントを複数割り当てる（MINEXTENTSをデータファイルの数だけ指定する）必要があります。これを指定しない場合、データファイルがいっぱいになるまで1つずつ順番に消費されていくだけなので、I/O分散になりません。

メモリ管理のチューニング

　メモリ管理のチューニングにおいても、領域管理同様、Oracleの方が多くのパラメータで調整できますし、調整する必要があります。

　Oracleの場合、基本はサーバの物理メモリをちょうど使いきる設定を目指します。最も長いトランザクションで使用される大きなテーブル・インデックスと、最大同時並行で何ユーザーが使用するかが主な判断材料となります。また、オプティマイザがコストベースに設定されていれば、統計情報のメンテナンスを怠ると、メモリが有効に使われないこともあります。

　これに対してSQL Serverは、トータルで使用するメモリの最小・最大サイズを指定するのみです（図9）。同じサーバ上でSQL Server以外のソフトウェアを稼働させる必要がある場合や、複数のSQL Serverを並行稼働させる場合、時間帯によって負荷が異なるといった理由がなければ、最小＝最大サイズか固定サイズで問題ありません。

図9　SQL Serverのメモリ管理

Point

OracleはSQLServerに比較して、細かい単位で領域（ファイル・オブジェクトなど）とメモリの管理が可能／必要である。
Oracleのち密なパラメータ管理には、まめなメンテナンスと多くの判断材料が必要になる。

今回のまとめ

　今回はOracleとSQL Serverのパフォーマンス・チューニングの方法について比較しました。このように、RDBMSのアーキテクチャの違いから、チューニングできる範囲も方法も異なりますが、内容的には共通することが多いのではないでしょうか。

　Oracleはマニュアル車、SQL Serverはオートマ車のイメージに例えられます。新バージョンが出るにつれて両製品ともますます「オートマ化」される傾向にありますが、必ずしも最適なチューニングが自動で行われるとは限りません。そういった場合に備えて、チューニングすべきポイントは、しっかり押さえておきたいものです。

　次回は、Oracle、DB2、SQL Serverを運用管理の点から比較して解説します。（次回に続く）