データベース・パフォーマンス・チューニング(初級編)データベース・パフォーマンス・チューニング(初級編)はじめに索引について表の結合操作SQL文の診断SQL文の改良はじめにオラクルのパフォーマンスチューニングには、設計段階で行なうものと、アプリケーション開発時に行なうものがあります。ここでは、アプリケーション開発時に効率的なSQL文を書くためのポイントをまとめます。一般的なチューニング作業の流れ適切なデータベース設計SQL文のチューニングアプリケーションのチューニングメモリのチューニングI/Oのチューニング競合のチューニング索引についてテーブル(表)内のデータは、物理入力順に格納されている。そのため、表内の特定の列に「索引」を作成して、よりよい検索パフォーマンスを引き出すことができる。連結型索引−−複数の列から構成された索引。最大32列まで指定できる。※連結型索引でも、先頭列だけは単一型索引と同様に使用される。※オラクルでは、表に対して作成できる索引の数に制限はない。更新頻度が高い表では、同時に索引も更新されるためオーバヘッドが大きくなるので、考慮が必要である。表の結合操作Oracleでは、複数の表を結合する際に以下の結合方法の1つを選択し、実行する。オプティマイザが、どちらかひとつの表を「外部表」として選択し、もう一方を「内部表」とする。結合条件で使用される列をソートキーとして、各表をソートする2つの表をマージして、結果を戻す。一方の表をハッシュ関数を用いてメモリ上にロードし、ハッシュテーブルを作成するハッシュテーブルを利用して、ディスク上のもう一方の表の各行に対応する行を結合し、結果を返す。※ハッシュ結合は、コストベースの時で、各表が等価結合のときのみ使用されるSQL文の診断1.オプティマイザSQL文の実行時、表への最適なアクセス方法(実行計画)を選択する機能。DML文(SELECT、INSERT、UPDATE、DELETE)を発行する際にもっとも効率よく表にアクセスする方法を選択してくれるオプティマイザの設定は、初期化パラメータ「OPTIMIZER_MODE」で指定する。●ルールベース・アプローチOPTIMIZER_MODE=RULEオプティマイザはSQL構文と表、索引定義を情報として、アクセス・パスの優先順位に基づいて実行計画を作成する。WHERE文中の条件が複数ある場合、もっとも優先順位の高いアクセス・パスを採用する。●コストベース・アプローチOPTIMIZER_MODE=CHOOSEオプティマイザはANALYZE文を使用して収集した統計情報に基づいて、最適なアクセス・パスを採用する。初期化パラメータでコストベースが選択されていても、統計情報が無ければルールベースで実行計画が立てられる。TABLEコストベースの場合、まず、オプティマイザが使用する統計情報をテーブルに格納する。適切な実行計画判断のために、統計情報は常に最新状態を保つことが望ましい。STATISTICS;表が分析されました。STATISTICS;表が分析されました。PLAN文オプティマイザが選択したSQL文の実行計画をPLAN_TABLEに格納する。(PLAN_TABLE表は、オラクル標準提供の「UTLXPLAN.SQL」スクリプトを実行して作成する。TRACEオプティマイザが選択したSQL文の実行計画を、SQL*Plus画面上に表示する。PLAN_TABLE表、及びPLUSTRACEロールが必要。dept.deptno;16行が選択されました。Planを用いて検証する。索引を効率的に使用していない場合は、以下の指標に沿ってSQL文を改善するとよい。※暗黙の型変換に注意するOracleでは、SQLで明示的に指定しなくても数値型⇔文字型の変換を行なう。索引列では関数が使用されたのと同じことになり、索引が使用されない。0;否定演算子を使用した場合、全表走査が行なわれるので、下のように記述する。0;NULL列は索引に入らないので、索引を使用するように下のように記述する。UNIQUE索引のみを表検索に利用し、もう一方の索引は無視される。複合索引の構成列の先頭の列を単独条件で使用している場合に利用される。再度同じ文字列を持つSQL文が発行された場合にこの情報を使用して処理速度を向上させる。大文字と小文字が異なるだけでも異なる文と判断される。ホスト変数を使用する、などの注意が必要である。効率的なSQL文の実行ができる。ヒントは、指定が相反しないものであればブランクを空けて複数指定できる。