2 SQLパフォーマンスの方法

この章では、お薦めするSQLチューニングの方法について説明します。

この章の内容は次のとおりです。

アプリケーションの設計のガイドライン

良好なSQLパフォーマンスを得るには、パフォーマンスを考慮してアプリケーションを設計することが重要になります。

この項の内容は次のとおりです。

データのモデル化のガイドライン

データのモデル化は、アプリケーションの適切な設計に重要です。

データのモデル化によって、ビジネス・プラクティスを表現する必要があります。正しいデータ・モデルに関して様々な議論が展開される場合があります。重要なことは、最も頻繁に行われるビジネス・トランザクションの影響を受けるエンティティをモデル化の主な対象にすることです。

モデル化フェーズでは、あまり重要でないデータ要素のモデル化に時間を取られることがあり、開発の準備期間が延長される結果になります。モデル化ツールを使用すると、スキーマ定義をすばやく生成できます。また、短期間でプロトタイプを作成する必要がある場合に便利です。

効率的なアプリケーションの作成のガイドライン

システム開発の設計およびアーキテクチャ・フェーズでは、アプリケーション開発者がSQLの実行効率を理解している必要があります。

この目標を達成するには、開発環境が次の特性をサポートしている必要があります。

データベース接続の適切な管理

データベースへの接続は、コストが高くスケーラブルでない操作です。そのため、ベスト・プラクティスは、データベースへの同時接続数を最小限に抑えることです。アプリケーションの初期化時にユーザーが1人接続しているという単純なシステムが理想的です。しかし、Webベース・アプリケーションや多層アプリケーションでは、複数のアプリケーション・サーバーによりユーザーへのデータベース接続が多重化しているため、接続数を少なくするのは困難です。このようなタイプのアプリケーションの場合、データベース接続をプールし、ユーザー・リクエストごとに接続を再確立しないように設計します。
カーソルの適切な使用および管理

ユーザー接続のメンテナンスも、システムでの解析アクティビティの最小化にとっては同じように重要です。解析とは、SQL文を解釈し、そのSQL文の実行計画を作成する処理です。この処理には、構文検査、セキュリティ検査、実行計画の生成、共有プールへの共有構造のロードなど、多くのフェーズがあります。解析操作には、次の2種類があります。
- ハード解析
  
  SQL文が初めて送信され、共有プール内に一致するものがない場合です。ハード解析は、解析に関連するすべての操作を実行するため、最もリソース集中型であり、スケーラブルではありません。
- ソフト解析
  
  SQL文が初めて送信され、共有プール内に一致が見つかった場合です。別のユーザーが以前に実行した結果が一致することがあります。SQL文は共有されるため、最適なパフォーマンスが実現します。ただし、ソフト解析では、システム・リソースを消費する構文検査やセキュリティ検査が必要であり、理想的とはいえません。
解析はできるだけ最小限にする必要があるため、アプリケーション開発者は、SQL文を1回解析し、そのSQL文を複数回実行するようにアプリケーションを設計してください。これは、カーソルを使用して行います。経験のあるSQLプログラマであれば、カーソルのオープンと再実行の概念を理解しています。
バインド変数の効果的な使用

アプリケーション開発者は、SQL文が共有プール内で共有されるようにする必要もあります。この目標を達成するには、問合せの中で実行ごとに変化する部分をバインド変数として表します。これを行わないと、SQL文は1回解析された後、他のユーザーから再利用されない可能性があります。SQLの共有を確実にするには、SQL文ではバインド変数を使用し文字列リテラルは使用しないようにします。次に例を示します。

文字列リテラルがある文は、次のとおりです。
```
SELECT * 
FROM   employees 
WHERE  last_name LIKE 'KING';
```
バインド変数がある文は、次のとおりです。
```
SELECT * 
FROM   employees 
WHERE  last_name LIKE :1;
```
次の例は、単純なOLTPアプリケーションでのテスト結果です。
```
Test                         #Users Supported
No Parsing all statements           270 
Soft Parsing all statements         150
Hard Parsing all statements          60
Re-Connecting for each Transaction   30
```
このテストは、4台のCPUを搭載したコンピュータで実行されました。システム上のCPUの数が増えると、差異も大きくなります。

アプリケーションのデプロイのガイドライン

パフォーマンスを最適化するには、設計時と同様の注意を払ってアプリケーションをデプロイする必要があります。

この項の内容は次のとおりです。

テスト環境でのデプロイのガイドライン

テスト・プロセスは、主に機能テストと安定性テストで構成されます。このプロセスの途中で、パフォーマンス・テストを実行する必要があります。

アプリケーションのパフォーマンス・テストを実行するときの簡単な規則を説明したリストを次に示します。適切に文書化した場合、このリストは、アプリケーション稼働後の本番アプリケーションと容量計画プロセスにとって重要な情報を提供します。

自動データベース診断モニター(ADDM)とSQLチューニング・アドバイザを使用した設計の検証
現実的なデータ量とデータ配分によるテスト

すべてのテストは、データが完全に含まれている表を使用して行う必要があります。テスト用データベースには、データ量や表のカーディナリティという点で本番システムと同様のデータを入れておく必要があります。本番と同様の索引をすべて作成し、スキーマ統計を正しく入力します。
正しいオプティマイザ・モードの使用

すべてのテストは、本番で使用するオプティマイザ・モードで実行する必要があります。
シングル・ユーザーのパフォーマンスのテスト

アイドル状態または使用率の低いデータベースで、シングル・ユーザーのパフォーマンスが許容範囲にあるかテストします。シングル・ユーザーのパフォーマンスが理想的な条件下で許容範囲に達しない場合、実際の条件下で複数のユーザーが許容可能なパフォーマンスを得ることはできません。
全SQL文の計画の取得と文書化

各SQL文の実行計画を取得します。このプロセスを使用して、オプティマイザの実行計画が最適かどうか、およびSQL文の相対コストがCPU時間と物理I/O数の観点から把握されているかどうかを検証します。このプロセスは、将来において多くのチューニングおよびパフォーマンスの必要な頻繁に使用されるトランザクションを識別するのに役立ちます。
マルチ・ユーザーのテスト。

ユーザーのワークロードやプロファイルがまだ完全に定量化されていない可能性があるため、このプロセスを正確に実行するのは困難です。しかし、DML文を実行するトランザクションをテストして、ロックの競合やシリアライズの問題がないことは確認する必要があります。
正しいハードウェア構成でのテスト

できるだけ本番システムに近い構成でテストを行います。実際的なシステムの使用は、ネットワーク待機時間、I/Oサブシステム帯域幅およびプロセッサのタイプと速度についてテストする場合に特に重要です。このアプローチを使用しなかった場合、潜在的なパフォーマンスの問題を正しく分析できません。
安定した状態でのパフォーマンスの測定

ベンチマークを行うときは、安定した状態でパフォーマンスを測定することが重要です。各ベンチマークの実行には開始フェーズが必要です。このフェーズでは、ユーザーがアプリケーションに接続し、アプリケーションでの作業を徐々に開始します。このプロセスによって、安定した状態になる前に、頻繁にキャッシュされるデータをキャッシュに初期化し、解析などの1回のみ実行する操作を完了しておくことができます。同様に、ベンチマークの実行後は、システムがリソースを解放し、ユーザーが作業を終了して接続を切断できるようにするために、終了フェーズが役立ちます。

アプリケーションのロールアウトのガイドライン

新しいアプリケーションをロールアウトするときは、次の2つの方法が一般的に採用されます。ビッグ・バン・アプローチでは、すべてのユーザーが一度に新しいシステムに移行します。トリクル・アプローチでは、ユーザーは既存のシステムから新しいシステムに徐々に移行します。

いずれの方式にもメリットとデメリットがあります。ビッグ・バン・アプローチでは、必要な規模でアプリケーションを十分にテストしておく必要がありますが、旧システムは完全に使用されなくなるため、旧システムからのデータの変換と旧システムとの同期が最小限で済みます。トリクル・アプローチでは、ワークロードの増加に伴いスケーラビリティの問題をデバッグできますが、移行中に旧システムとの間でデータを相互に移行する必要性が発生することがあります。

いずれの手法も、それぞれ移行実施中にシステムの停止につながるリスクがあるため、どちらかを推奨することは困難です。トリクル・アプローチでは、実際のユーザーが新しいアプリケーションに移行するにつれてユーザー・プロファイルを作成できるので、システムを再構成しても、その影響を受けるのは移行済ユーザーのみです。この方式は、初期の段階で移行したユーザーの作業に影響を与えますが、サポート・サービスの負荷は限定されます。そのため、スケジュール外の停止が発生しても、影響を受けるユーザーの割合は小さくて済みます。

新規アプリケーションのロールアウト方法は、ビジネスごとに判断してください。採用するどのアプローチにも、それぞれ固有の長所と短所があります。テストを重ねて、そのテストから得られた知識が増えるほど、最善のロールアウト方式を判断できます。