前回はPostgres Plus Advanced ServerのOracle Database互換性機能を中心にご説明しました。Postgres Plus Advanced Serverは、Oracle Databaseで培ったスキルを活用することができます。そのため、PostgeSQLの活用はハードルが高そうだと感じるユーザであっても、より簡単に採用できるRDBMSの選択肢を広げることができるのではないでしょうか。
企業内で幅広く活用されるRDBMSには、高い信頼性や可用性、性能が求められますが、運用管理の容易性や、性能問題への対処方法の多様性、性能問題調査の容易性も重要な要素となります。
今回は、Postgres Plus Advanced Serverで機能強化されているSQLヒント、Postgres Enterprise ManagerのIndex Advisor、DRITA (Dynamic Runtime Instrumentation and Tracing Architecture)といった、性能問題への対処方法の多様性と、性能問題調査の容易性を実現する機能をご紹介します。
SQLヒント
PostgreSQLやOracle DatabaseなどのRDBMSでは、アプリケーションから発行されたSQLを「解析」して「実行」しています。「解析」では、SQLの構文チェック、参照されているオブジェクトの存在チェックが行われた後、データにアクセスするための経路であるアクセスパスを決定しています。例えば、友人との待ち合わせの場所に行く場合、目的地まで、どの経路を使って、どんな交通手段で移動するのが一番効率的かを考えるのと似ています。代表的なアクセスパスとしては、テーブルを1行、1行読み取って、条件に合うデータを取り出す「全表スキャン」と、本の巻末の索引と同じく、テーブルの各列のデータとそのデータの格納位置を示すアドレス情報を持つ「索引」を利用した「索引スキャン」が存在します。また、目的地に行く場合も、交通状況や交通機関の遅延情報などを考慮して、目的地までのルートを探すように、RDBMSもテーブルに格納されている行数や、列に格納されている値の種類、値の分布などの統計情報と呼ばれる情報を利用して、表に対して最もコストの低いと考えられるアクセスパスを選択します。なお、この役割を果たす機能をオプティマイザやプランナと呼んでいます。
前置きが長くなりましたが、SQLヒントとは、オプティマイザにアクセスパスを明示的に指定する機能となります。
では、なぜ、オプティマイザにアクセスパスを明示的に指定する機能が付加されているのでしょうか。
これは、オプティマイザが選択するアクセスパスが必ずしも最短ルートではない可能性があるからです。Oracle Databaseなどの商用RDBMSでもSQLヒントは実装されており、実際に活用されています。オプティマイザがアクセスパスを選定する際は、テーブルの統計情報と、SQLの条件値から、どのくらいのデータにアクセスするのかを評価し、全表スキャンを選択するのか、索引スキャンであれば、どの索引を利用するのか、テーブルを結合するのであれば、どのような順番でテーブルを結合していくのか、結合方法はソートマージ結合、ネスティッドループ結合、ハッシュ結合のいずれを選択するのかなどを、一定のコスト計算式により導き出しています。SQL実行前に統計情報やSQLで指定される条件値からアクセスパスを推定するので、オプティマイザが選定したアクセスパスとは異なるアクセスパスの方が実行時間が速くなる事象は起こりえます。特に、テーブルの統計情報と実際のデータの分布が異なっていた場合は発生しやすいと言えます。
オプティマイザが選択するアクセスパスよりも、適したアクセスパスが判明している場合、個々のSQLにヒントを埋め込んでアクセスパスを指定する（固定化する）チューニングを施すことになります。
しかし、このSQLヒント機能はPostgreSQLでは実装されていません。PostgreSQLの開発コミュニティのwikiページ「OptimizerHintsDiscussion」には、SQLヒントへの見解が示されています。PostgreSQLの開発コミュニティが示す見解のように、SQLヒントは個々のSQLにヒントを埋め込むため、アップグレードによりオプティマイザが改善されたり、テーブルに格納されているデータ量やデータの分布が変わった場合、アクセスパス選択の柔軟性が失われることになります。また、ほとんどの場合、オプティマイザは人間よりも最適なアクセスパスを選ぶのも事実です。そのため、PostgreSQLの開発コミュニティの見解のようにSQLヒント機能を実装しないという考え方も理解できるのですが、SQLヒントによってSQLをチューニングする手法が一般化している現実を考えると、Postgres Plus Advanced ServerでSQLヒントが実装されているのは、現実的な解であると考えられます。
なお、PostgreSQLの場合も、特定のアクセスパスを無効化もしくは有効化したり、コスト計算をするサーバパラメータをセッション単位で指定することができるので、ある程度、アクセスパスをコントロールすることは可能です。
Postgres Plus Advanced Server で実装されているSQLヒントは表１のように、多くの種類があるわけではありませんが、SQLチューニングで必要となる主要なヒントは網羅されています。
| ヒント | 役割 | 利用例 |
| アクセスパス | ||
| FULL | 全表スキャンを行う | /*+ FULL(表名) */ |
| INDEX | 索引スキャンを行う | /*+ INDEX(表名 索引名) */ |
| NO_INDEX | 索引スキャンの使用禁止 | /*+ NO_INDEX(表名 索引名) */ |
| 結合順序 | ||
| ORDERD | FROM句に指定した順序で表を結合 | /*+ ORDERD */ |
| 結合方法 | ||
| USE_HASH | ハッシュ結合を行う | /*+ ORDERD USE_HASH(表名) */ |
| NO_USE_HASH | ハッシュ結合の使用禁止 | /*+ NO_USE_HASH(表名) */ |
| USE_MERGE | ソートマージ結合を行う | /*+ ORDERD USE_MERGE(表名) */ |
| NO_USE_MERGE | ソートマージ結合の使用禁止 | /*+ NO_USE_MERGE(表名) */ |
| USE_NL | ネスティッドループ結合を行う | /*+ ORDERD USE_NL(表名) */ |
| NO_USE_NL | ネスティッドループ結合の使用禁止 | /*+ NO_USE_NL(表名) */ |
次に、Postgres Plus Advanced Server でのSQLヒントの使い方を確認してみましょう。
前回と同様にEMPテーブルを利用します。表２の例のように、USER_IND_COLUMNSディクショナリビューを確認するとEMPNO列に一意索引、JOB列、DEPTNO列にそれぞれ単一列索引、SAL列、COMM列に複合索引が作成されていることがわかります。EMPテーブルから職種がマネージャの情報を確認するSELECTを発行した場合に選択されるアクセスパスをOracle DatabaseではSQL*PlusのAUTOTRACE機能で、Postgres Plus Advanced ServerはEXPLAINコマンドで確認してみます。Oracle DatabaseのオプティマイザはJOB列の索引を利用した索引スキャンを選択し、Postgres Plus Advanced Serverのオプティマイザは全表スキャンを選択することがわかります。EMPテーブルは14行の小さなテーブルですので、Postgres Plus Advanced Serverのオプティマイザは全表スキャンのコストが低いと算出しているようです。INDEXヒントを使用してJOB列の索引スキャンをオプティマイザに指定すると、索引スキャンが選択されることがわかります。
次に、EMPテーブルとDEPTテーブルの結合の例です。DEPTテーブルは部門名や部門の所在地を保持しており、従業員テーブルであるEMPテーブルとの結合はDEPTNO列を条件とします。表３のように、従業員が所属する部門名の一覧を確認するSELECTを実施したところ、Oracle Databaseはソートマージ結合、Postgres Plus Advanced Serverはハッシュ結合を選択しました。ここで、DEPTテーブルを軸とし、EMPテーブルのDEPTNO列の索引であるIND_DEPTNO索引を利用したネスティッドループ結合を選択させる場合、ORDEREDヒント、USE_NLヒント、INDEXヒントを指定することで、アクセスパスをコントロールすることができます。
上記の例からも分かるように、Postgres Plus Advanced ServerはOracle Databaseと同じアクセスパスを選択するオプティマイザ機能は有していないので、アプリケーションをPostgres Plus Advanced Serverに対応させる場合、性能関連の試験を行う必要がありますが、SQLヒントを利用した従来のチューニング手法を活用することができます。
