| Oracle® Databaseパフォーマンス・チューニング・ガイド 11gリリース2 (11.2) B56312-06 |
|
 前 |
 次 |
この章では、チューニングの目的、多くのリソースを消費するSQL文の識別方法および収集する内容の説明と、チューニングの提案を示します。また、SQLテスト・ケースを作成してSQLの問題をトラブルシューティングする方法を解説します。
この章には次の項があります。
|
関連項目:
|
SQLチューニングには次の基本手順が含まれます。
システムで使用可能な過去のSQL実行履歴を検討し、アプリケーションのワークロードおよびシステム・リソースの大部分に関係している高負荷または上位SQL文を識別します。
これらの文について問合せオプティマイザにより生成される実行計画が適切に実行されるかどうかを確認します。
修正アクションを実装し、パフォーマンスの低いSQL文について適切な実行計画を生成します。
システム・パフォーマンスが十分なレベルに達するか、他にチューニングできる文がなくなるまで、前の手順を繰り返します。
システムをチューニングする目的は、システムのエンド・ユーザーへのレスポンス時間を短縮したり、同じ作業の処理に使用されるリソースを削減することです。これには、次の方法があります。
一般に、SQLチューニングには、同じワークロードをより効率的に処理する方法を見つけ出すことが含まれます。機能性を変えることなく文の実行計画を変更し、リソース使用量を削減することは可能です。
リソース使用量を削減する方法として、次に2つの例を示します。
よく実行する問合せが、表のほんの一部のデータにアクセスする場合は、索引を使用すると、効率的に実行できます。索引を作成すれば、使用するリソースの量を削減できます。
特定のソート順序で戻される10,000行の最初の20行を参照する場合、および索引によって問合せ(およびソート順序)が満たされる場合、最初の20行を参照するために、10,000行にアクセスしてソートする必要はありません。
システムは、実ユーザーがシステムに接続している昼間に使用量が最大に達し、夜間には低下する傾向があります。重要でないレポートやバッチ・ジョブを夜間に実行するようにスケジューリングし、昼間の並行性を削減することで、日中は重要なプログラムのためにリソースを解放できます。
多くの場合、大量のデータにアクセスする問合せ(一般的なデータ・ウェアハウス問合せ)は、パラレルに実行できます。並列化は、並行性の低いデータ・ウェアハウスのレスポンス時間の短縮に非常に役立ちます。ただし、並行性の高いOLTP環境の場合は、プログラムの総リソース使用量が増加し、他のユーザーに影響を与える可能性があります。
この項では、高負荷SQL文の識別およびデータ収集を行う手順を説明します。高負荷のSQLとは、Oracleデータベースのパフォーマンスに影響を与える、非効率的なリソース集中型のSQL文です。負荷の高いSQL文を識別するには、次のツールを使用できます。
自動データベース診断モニター
自動SQLチューニング
自動ワークロード・リポジトリ
V$SQLビュー
カスタム・ワークロード
SQLトレース
リソース集中型のSQLを識別する最初のステップは、検討する問題の分類です。
単一(または少数)のプログラムに固有の問題であるか。
アプリケーション全体にわたる一般的な問題であるか。
特定のプログラム(GUIまたは3GL)をチューニングする場合、検討するSQLの識別は、プログラム内で実行されたSQLを見るだけの簡単な作業です。Oracle Enterprise Manager(Enterprise Manager)に用意されているツールを使用して、リソースを多く使用するSQL文の識別、EXPLAIN PLANの生成およびSQLパフォーマンスの評価ができます。
SQLを識別できない(たとえば、SQLが動的に生成される)場合は、SQL_TRACEを使用して、実行されたSQLを含むトレース・ファイルを生成し、次にTKPROFを使用して出力ファイルを生成します。
TKPROF出力ファイル内のSQL文は、実行経過時間(exeela)などの各種パラメータで順序付けできるため、通常は、SQL文を経過時間で順序付けする(経過時間が最も長いSQL文をファイルの一番上に置く)ことで識別に利用されます。これにより、ファイル内にSQL文が多数ある場合に、パフォーマンスの低いSQLを識別するジョブの実行が容易になります。
アプリケーション全体のパフォーマンスが悪い場合、またはデータベース・サーバーのCPUまたはI/Oの全体的な負荷を削減する場合、多くのリソースを消費するSQLを識別するには、次の手順を実行します。
調査する時間帯を決定します。通常は、アプリケーションの処理のピーク時間です。
その期間の開始時と終了時に、オペレーティング・システムおよびOracle Databaseの統計を収集します。収集する必要のある最小限のOracle Database統計は、ファイルI/O(V$FILESTAT)、システム統計(V$SYSSTAT)、およびSQL統計(V$SQLAREA、V$SQLまたはV$SQLSTATS、V$SQLTEXT、V$SQL_PLANおよびV$SQL_PLAN_STATISTICS)です。
|
関連項目:
|
ステップ2で収集したデータを使用して、多くのリソースを使用するSQL文を識別します。候補のSQL文を識別するには、V$SQLSTATSを問い合せる方法が適しています。V$SQLSTATSには、共有プール内のすべてのSQL文に関するリソース使用率の情報が含まれています。V$SQLSTATS内のデータは、リソース使用率で順序付けします。共通リソースの主なものは、次のとおりです。
バッファ読取り(V$SQLSTATS.BUFFER_GETS。CPU使用率が高い文の場合)
ディスク読取り(V$SQLSTATS.DISK_READS。I/Oが高い文の場合)
ソート(V$SQLSTATS.SORTS。ソートが多い場合)
負荷の最も大きいSQL文を識別する方法の1つは、その期間内にSQL文で使用されたリソースを、同じ期間内に使用されたそのリソースの総量と比較することです。BUFFER_GETSの場合、各SQL文のBUFFER_GETSの回数を、期間中のバッファ読取りの総数で除算します。システム内のバッファ読取りの総数はV$SYSSTAT表のsession logical readsの統計情報からわかります。
同様に、V$SQL_STATS.DISK_READSをV$SYSSTAT統計の物理読取りの値で除算すると、システムによって実行されるディスク読取りの総数のうち、文によって実行されるディスク読取りの割合を計算できます。自動ワークロード・リポジトリ・レポートのSQLセクションにはこのデータが含まれているため、割合を手動で計算する必要はありません。
|
関連項目: 動的パフォーマンス・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
候補のSQL文を識別した後、文を調べるために必要な情報を収集し、チューニングします。
CPUが問題となっている場合は、一定期間内に最も多くのBUFFER_GETSを実行した上位のSQL文を調べます。その他の場合は、最も多くのDISK_READSを実行したSQL文から始めます。
チューニング・プロセスではまず、基礎となる表と索引の構造を判別します。収集する情報は、次のとおりです。
V$SQLTEXTからの完全なSQLテキスト
SQL文で参照される表の構造(通常はSQL*Plusの表を記述)
索引(列、列の順序)の定義と、各索引が一意かどうか
セグメントのオプティマイザ統計(表ごとの行数、索引列の選択性など)、およびセグメントが最後に分析された日付
SQL文で参照されるビューの定義
ステップ5で検出された、ビュー定義で参照されている表について、ステップ2、3および4を繰り返します。
SQL文(EXPLAIN PLAN、V$SQL_PLANまたはTKPROF出力のいずれかからのもの)のオプティマイザ計画
そのSQL文の以前のオプティマイザ計画
|
注意: アプリケーション内の主要なSQL文すべてについて、実行計画を生成し、見直すことが重要です。これにより、SQL文が効率よく実行されたときのオプティマイザの実行計画と、そうでないときの計画とを比較できます。データ量の変化などの情報とあわせて比較を行うと、パフォーマンスの低下の原因を正確に識別できます。 |
手動SQLチューニング・プロセスはアプリケーション開発者に多数の作業が発生するため、SQLチューニング・プロセスはOracle Databaseの自動SQLチューニング機能により自動化されています。これらの機能は、OLTPタイプとデータ・ウェアハウス・タイプのアプリケーションで同様に機能するように設計されています。
自動データベース診断モニター(ADDM)では、高負荷SQL文など、Oracle Databaseにおいて考えられるパフォーマンス上の問題について、AWRによって収集された情報が分析されます。「自動データベース診断モニターの概要」を参照してください。
SQLチューニング・アドバイザでは、高負荷SQL文として識別されたSQL文が最適化されます。デフォルトでは、Oracle Databaseは問題のあるSQL文を自動的に識別します。また、高負荷SQL文の実行計画を改善する方法を調査し、自動化メンテナンス・タスクとしてシステム・メンテナンス・ウィンドウ中にSQLチューニング・アドバイザを使用してチューニング推奨事項を実装します。パフォーマンスを向上するために、任意のSQLワークロードに対していつでもSQLチューニング・アドバイザを実行できます。「SQLチューニング・アドバイザによる事後対応のチューニング」を参照してください。
複数のSQL文をADDM、SQLチューニング・アドバイザまたはSQLアクセス・アドバイザに入力する場合は、SQLチューニング・セット(STS)を作成し、格納します。STSには、SQL文のセットと、関連する実行コンテキストおよび基本実行統計が含まれます。「SQLチューニング・セットの管理」を参照してください。
SQLチューニング・アドバイザに加え、SQLアクセス・アドバイザは、マテリアライズド・ビュー、索引およびマテリアライズド・ビュー・ログについてアドバイスを提供します。SQLアクセス・アドバイザでは、指定のワークロードに関するマテリアライズド・ビュー、マテリアライズド・ビュー・ログおよび索引の適切なセットが推奨されるため、パフォーマンスの目標を達成するのに役立ちます。一般に、マテリアライズド・ビューおよび索引の数と、これらに割り当てられている領域が増えるにつれて、問合せのパフォーマンスが向上します。SQLアクセス・アドバイザでは、領域使用量と問合せパフォーマンスの兼合いが考慮され、新規および既存のマテリアライズド・ビューおよび索引の最もコスト効率の高い構成が推奨されます。「SQLアクセス・アドバイザの使用方法」を参照してください。
この項では、SQL文の効率を高める方法を説明します。
|
注意: この項で説明するガイドラインは、頻繁に実行されるSQLの生成に関するものです。この項で推奨されないほとんどの技法は、パフォーマンスが重要でない非定型文または頻繁に実行されないアプリケーションでは使用してもかまいません。 |
問合せオプティマイザでは、最適な実行計画の判別時に、表と索引について収集された統計を使用します。これらの統計が収集されなかった場合、または、統計がデータベース内に格納されているデータをすでに反映しなくなっている場合、オプティマイザには最適な計画を生成するための十分な情報がありません。
チェックする内容
データベース内のいくつかの表に関する統計が必要な場合は、すべての表に関する統計を収集するのが望ましい方法です。このことは、結合を実行するSQL文がアプリケーションに含まれている場合に特に言えます。
データ・ディクショナリ内のオプティマイザ統計が表と索引内のデータを反映しなくなっている場合は、新しい統計を収集します。ディクショナリ統計が失効しているかどうかをチェックする方法の1つは、表の実際のカーディナリティ(行数)と、DBA_TABLES.NUM_ROWSの値を比較することです。また、述語列に大きなデータの偏りがある場合は、ヒストグラムを使用することを検討してください。
OLTP環境でSQL文をチューニング(または作成)する場合、最も選択性の高いフィルタを持つ表から駆動することを目標とします。つまり、次のステップに渡される行を少なくするということです。次のステップが結合である場合は、少数の行しか結合されないということになります。アクセス・パスが最適かどうかを確認してください。
オプティマイザの実行計画を調べる場合は、次の内容を確認します。
駆動表には最適なフィルタがある。
各ステップの結合順序が、最も少ない行数を次のステップに戻す(つまり、可能な場合、結合順序は最適な未使用フィルタでの処理を反映する必要があります)。
結合方法が、それによって戻される行数からみた場合に、適切なものであること。たとえば、文が多数の行を戻す場合、索引でのネステッド・ループ結合は最適でない場合があります。
データベースでのビューの効率的な使用。SELECT構文のリストを見て、ビューへのアクセスが必要であるかどうかを確認してください。
意図しないデカルト積(小さい表の場合も含む)があるかどうか。
各表が効率的にアクセスされていること。
SQL文の述語と、表の行数を評価します。大量の行を持つ表の全表スキャンなど、WHERE句に述語を持つ疑わしいアクティビティを探します。そのような選択的な述語に索引が使用されない理由を判別します。
全表スキャンが非効率的というわけではありません。小さい表で全表スキャンを行う場合や、戻される行数に対してよりよい結合方法(たとえば、hash_join)を活用するために、全表スキャンを行うほうが効率がよい場合があります。
これらの条件のうち最適でないものがある場合は、SQL文の再構成や、表で使用できる索引について考慮します。
非効率的なSQL文は、修正するよりも書きなおす方が簡単なことがよくあります。元の文の意図を理解していれば、要件を満たす新しい文を迅速かつ容易に作成できます。
変換されない列値を使用します。たとえば、次の例のように使用します。
WHERE a.order_no = b.order_no
次の例は使用しません。
WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1)) = TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
述語句またはWHERE句では、SQLファンクションを使用しないでください。列を引数として持つ関数など、列を使用する式は、使用できる定義済ファンクション索引がないかぎり、その列の索引を使用できる可能性をオプティマイザが無視する原因となります(一意索引も例外ではありません)。
型が混在する式は避け、暗黙的な型変換に注意してください。VARCHAR2列charcolの索引を使用するときに、WHERE句が次のようであるとします。
AND charcol = numexpr
numexprは数値型の式(たとえば、1、USERENV('SESSIONID')、numcol、numcol+0、...)であり、Oracle Databaseではこの式を次のように変換します。
AND TO_NUMBER(charcol) = numexpr
次に示すタイプの複合式は使用しないようにしてください。
col1 = NVL (:b1,col1)
NVL (col1,-999) = ….
TO_DATE()、TO_NUMBER()など
ここに示した式によって、オプティマイザは有効なカーディナリティまたは選択性の見積りを割り当てることができなくなります。この結果、全体の計画および結合方法に悪い影響を与えます。
NVL()のかわりに述語を追加します。
たとえば、次のようにします。
SELECT employee_num, full_name Name, employee_id FROM mtl_employees_current_view WHERE (employee_num = NVL (:b1,employee_num)) AND (organization_id=:1) ORDER BY employee_num;
これは次のようにします。
SELECT employee_num, full_name Name, employee_id FROM mtl_employees_current_view WHERE (employee_num = :b1) AND (organization_id=:1) ORDER BY employee_num;
述語をリテラル値でフィルタするためにNUMBER型の列をWHERE句で使用する場合には、WHERE句の述語でTO_NUMBER関数を使用してその索引がNUMBER列で使用できるようにしてください。たとえば、numcolがNUMBER型の列である場合、numcol=TO_NUMBER('5')を含むWHERE句によってデータベースでnumcolの索引を使用できるようになります。
問合せが2つの表を結合し、結合列が異なるデータ型(たとえば、NUMBERとVARCHAR2)を持つ場合には、Oracle Databaseにおいてデータ型の変換が暗黙的に行われます。たとえば、結合条件がvarcol=numcolであるとすると、データベースはこの条件をTO_NUMBER(varcol)=numcolに暗黙的に変換します。索引がvarcol列に存在する場合、型変換を明示的にvarcol=TO_CHAR(numcol)に設定して、データベースが索引を使用できるようにします。
SQLは、手続き型言語ではありません。1つのSQLを使用して様々なことを実行すると、通常は各タスクに最適でない結果が生じます。SQLを使用して様々なタスクを実行する場合は、1つの文にパラメータを指定して異なるタスクを実行するのではなく、様々な文を作成してください。
|
注意: Oracle FormsとOracle Reportsは、PL/SQL(トリガーまたはプログラム・ユニット)を使用してアプリケーション・ロジックをコード化するための強力な開発ツールです。FormsまたはReportsで複雑なロジックを処理することによって、SQL文の複雑さを減らすことができます。また、規模の大きな単一の複雑なSQL文のかわりに、少数のSQL文を実行するサーバー側のPL/SQLパッケージを起動することもできます。このパッケージはサーバー側のユニットであるため、クライアントとデータベースの間のラウンドトリップやネットワークの通信量の問題は発生しません。 |
通常、異なるタスクには個別のSQL文を作成するほうが適していますが、使用するSQL文を1つにする必要がある場合は、UNION ALL演算子を使用することによって、非常に複雑な文を多少簡略化できます。
最適化(実行計画の決定)は、問合せに代入される値をデータベースが認識する前に行われます。したがって、実行計画はそれらの値が何であるかに依存しません。たとえば、次のようにします。
SELECT info FROM tables WHERE ...
AND somecolumn BETWEEN DECODE(:loval, 'ALL', somecolumn, :loval) AND DECODE(:hival, 'ALL', somecolumn, :hival);
この例では、データベースはsomecolumn列に対して索引を使用できません。この列を含む式が、BETWEENの両辺で同じ列を使用するためです。
このことは、選択性の高い、索引作成可能な他の条件が別にあって、それを使用して駆動表にアクセスできる場合には問題になりません。ただし、これにあてはまらない場合もよくあります。この例のような条件で索引を使用することは多くありますが、:lovalなどの値を、あらかじめ知っておく必要があります。この情報があれば、索引を使用できないALLのケースを除外できます。
:lovalと:hivalに実際の値が指定されたときに索引を使用する場合(:lovalと:hivalの間が狭く、多くの場合等しいことが期待できる場合)は、この例を論理的に等しい次の形式にリライトできます。
SELECT /* change this half of UNION ALL if other half changes */ info FROM tables WHERE ...
AND somecolumn BETWEEN :loval AND :hival AND (:hival != 'ALL' AND :loval != 'ALL')
UNION ALL SELECT /* Change this half of UNION ALL if other half changes. */ info FROM tables WHERE ...
AND (:hival = 'ALL' OR :loval = 'ALL');
この新しい問合せでEXPLAIN PLANを実行した場合、望ましい実行計画と望ましくない実行計画の両方が得られるように思われます。しかし、UNION ALLの前半と後半のどちらを実行するかを決めるためにデータベースが最初に評価する条件は、:hivalと:lovalがALLであるかどうかの複合条件です。データベースは、問合せの前半と後半のどちらかの実行計画から実際に行を取得する前に、この条件を評価します。
UNION ALL問合せの一方に関して条件がfalseであれば、その部分はそれ以上評価されません。与えられている値に関して最適な実行計画の部分のみが実際に実行されます。:hivalと:lovalに関する最終条件はどちらか一方のみがtrueであることが保証されているので、実際に行を戻すのはUNION ALLの半分のみです。(UNION ALL内のALLは、この排他性により論理的に有効です。これにより、問合せの両半分の結果から重複行を除外するためにコストの高いソートを実行することなく、計画を実行できます。)
オプティマイザのアプローチと目標の設定および問合せオプティマイザの代表的な統計の収集によって、オプティマイザの選択を変えることができます。特定のアプリケーション・データに関して、オプティマイザよりも多くの情報を持つアプリケーション設計者であれば、より効率よくSQL文を実行する方法を選択できる場合があります。SQL文のヒントを使用すれば、文を実行する方法をオプティマイザに指示できます。
/*+FULL */などのヒントは、アクセス・パスを制御します。たとえば、次のようにします。
SELECT /*+ FULL(e) */ e.last_name FROM employees e WHERE e.job_id = 'CLERK';
結合順序は、パフォーマンスに大きな影響を与えることがあります。SQLのチューニングの主な目的は、結果に影響しない不要な行にアクセスする作業を回避することです。このことから次の3つの一般ルールが導かれます。
索引を介して必要な行を取得するほうが効率的な場合には、全表スキャンを避けてください。
100行をフェッチする別の索引を使用できる場合には、駆動表から10,000行をフェッチする索引を使用するようなことは避けてください。
結合順序の後ろへ行くほど結合する行が少なくなるように結合順序を選択してください。
次の例は、結合順序を効果的にチューニングする方法を示しています。
SELECT info FROM taba a, tabb b, tabc c WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000 AND c.ccol BETWEEN 10000 AND 20000 AND a.key1 = b.key1 AND a.key2 = c.key2;
駆動表と駆動索引(存在する場合)を選択します。
前述の例における最初の3つの条件は、それぞれ1つの表にのみ適用されるフィルタ条件です。最後の2つの条件は結合条件です。
フィルタ条件は、駆動表と駆動索引の選択を左右します。一般に、表内の排除される部分の比率が最も高くなるフィルタ条件を含む表は駆動表です。この例では、100から200の範囲はacolの範囲に比べて狭く、10000から20000の範囲は相対的に大きいため、tabaは駆動表であり、他はすべて同じです。
ネステッド・ループ結合の場合、結合はすべて結合索引を介して行う必要があります。この結合索引は、主キーまたは外部キーに付けられているもので、その表を結合ツリー内のそれより前にある表に結びつけるために使用します。駆動表を除いて、非結合条件にこの結合索引を使用することはほとんどありません。そのため、tabaを駆動表として選択すると、b.key1とc.key2の索引を使用してtabbとtabcをそれぞれ駆動します。
未使用の最適なフィルタを最初に駆動する最適な結合順序を選択します。
最適な未使用フィルタを持つ表に先に結合することで、その後の結合の作業を削減できます。したがって、「bcol BETWEEN ...」が「ccol BETWEEN ...」よりも限定的な(表示される行をより高い比率で排除する)場合は、tabbをtabcよりも前に結合すると、最後の結合が容易になります(行数が少なくなります)。
ORDEREDまたはSTARヒントを使用して、結合順序を強制的に設定できます。
ビューの結合、ビューへの外部結合、および既存ビューの新規目的への再利用に対しては、注意が必要です。
複合ビューへの結合、特に、ある複合ビューから別の複合ビューへの結合はお薦めできません。そのような結合を行うと、多くの場合、ビュー全体がインスタンス化され、ビュー・データに対して問合せが行われる結果になります。
たとえば、次の文は従業員および部門をリストするビューを作成します。
CREATE OR REPLACE VIEW emp_dept
AS
SELECT d.department_id, d.department_name, d.location_id,
e.employee_id, e.last_name, e.first_name, e.salary, e.job_id
FROM departments d
,employees e
WHERE e.department_id (+) = d.department_id;
次の問合せは指定した状態の従業員を検索します。
SELECT v.last_name, v.first_name, l.state_province FROM locations l, emp_dept v WHERE l.state_province = 'California' AND v.location_id = l.location_id (+);
次の計画表出力では、emp_deptビューがインスタンス化されます。
-------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | | | | | | | FILTER | | | | | | | | NESTED LOOPS OUTER | | | | | | | | VIEW |EMP_DEPT | | | | | | | NESTED LOOPS OUTER | | | | | | | | TABLE ACCESS FULL |DEPARTMEN | | | | | | | TABLE ACCESS BY INDEX|EMPLOYEES | | | | | | | INDEX RANGE SCAN |EMP_DEPAR | | | | | | | TABLE ACCESS BY INDEX R|LOCATIONS | | | | | | | INDEX UNIQUE SCAN |LOC_ID_PK | | | | | | --------------------------------------------------------------------------------
ある用途のために作成したビューを他の用途に使用することは、不適切な場合があるため注意してください。ビューから問合せを行うと、データを戻すために、そのビューに関連するすべての表がアクセスされます。ビューを再利用する前に、ビュー内のすべての表にアクセスしてデータを戻す必要があるかどうかを判別してください。その必要がない場合は、ビューを使用しないでください。かわりに、実表を使用するか、必要に応じて新しいビューを定義してください。その目的は、必要なデータを戻すために参照する表およびビューの数を最小限にすることにあります。
次の例を見てください。
SELECT department_name FROM emp_dept WHERE department_id = 10;
ビュー全体ではまず、employees表とdepartments表の結合によりインスタンス化され、次にデータが集計されます。ただし、department_nameとdepartment_idは、departments表から直接取得できます。emp_deptビューを問い合せてこの情報を取得することは非効率的です。
副問合せのネストの解除によって、副問合せ本体が解除され、その副問合せが含まれている文の本体にマージされます。これにより、オプティマイザは、アクセス・パスと結合を評価するときに、副問合せと本体の両方をいっしょに考慮させてしまいます。
|
関連項目: 副問合せのネスト解除における危険性の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
複数表のビューに対する外部結合の場合、等価述語が定義されていれば、問合せオプティマイザ(リリース8.1.6以上)は外部結合列から駆動できます。
ビュー内での外部結合は、外部結合のパフォーマンスに与える影響が読めないため、問題が発生しやすくなります。
中間の表、すなわちステージング表がリレーショナル・データベース・システムで比較的よく利用されるのは、それらの表に中間結果を一時的に格納するためです。これは、多くのアプリケーションで有効ですが、これらの作成にさらなるリソースが必要になります。したがって、これらの表による利益が、作成のコストに見合うものかどうかを常に考慮してください。ステージング表は、その情報が何回も再利用されない場合は、作成しないようにしてください。
他の考慮事項
ステージング表に中間結果を格納することで、アプリケーション・パフォーマンスを向上できる場合があります。一般に、中間結果が、引き続きその後の複数の問合せで使用可能であれば、その結果をステージング表に格納する価値があります。中間結果の再利用により、複雑な文を使用して何度もデータの取得を行わないことの利点は、中間結果をマテリアライズするコストより優れています。
長く複雑な問合せは、理解し、最適化することが困難です。ステージング表を使用することで、複雑なSQL文をいくつかの小さい文に分解でき、このとき、各ステップの結果を格納します。
マテリアライズド・ビューを使用することを検討してください。マテリアライズド・ビューは、ファクト表やディメンション表からの集計データや結合データを格納する、あらかじめ計算済の表です。
|
関連項目: マテリアライズド・ビューの使用方法の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
多くの場合は、索引を再構成すると、パフォーマンスが向上します。これには、次の内容が含まれます。
非選択的な索引を削除して、DMLの速度を上げます。
パフォーマンス重視のアクセス・パスの索引を作成します。
既存の連結索引で列を並び換えることを考慮します。
索引に列を追加して、選択性を向上します。
索引を万能策として使用しないでください。アプリケーション開発者は、索引を多く作成すればパフォーマンスが改善されると考える場合があります。1人のプログラマが適切に索引を作成すれば、アプリケーションのパフォーマンスは十分に改善される可能性があります。ただし、50人の開発者が別々に索引を作成すると、アプリケーションのパフォーマンス改善は望めません。
トリガーを使用すると、システムのリソースが消費されます。トリガーを多用すると、パフォーマンスに悪影響を及ぼす場合があります。この場合、トリガーを変更または無効にする必要があります。
索引と文を再構成した後で、データの再構成について検討します。
導出された値を用意しておきます。レスポンス重視のコードでは、GROUP BYの使用を回避します。
データ設計を検討します。変更によりパフォーマンスの向上が見込める場合は、システムの設計を変更します。
必要に応じて、パーティション化を考慮します。
格納されている統計またはSQL計画ベースラインを使用すると、SQL文の既存の実行計画を長期的に保持できます。表のオプティマイザの統計を格納すると、その表を参照するすべてのSQL文にその統計が適用されます。実行計画をSQL計画ベースラインとして格納すると、SQL文セットに対する計画が保持されます。SQL文で統計およびSQL計画ベースラインが使用可能な場合、オプティマイザは最初に、コストベースの検索方法を使用してコストが最適な計画の構築を試み、次にSQL計画ベースラインで一致する計画を検索します。一致が見つかった場合、オプティマイザはこの計画を使用します。それ以外の場合は、SQL計画ベースラインの承認された各計画のコストを評価し、コストが最も低い計画を選択します。
アプリケーションから各行へのアクセスを、可能なかぎり1回のみにします。そうすることで、ネットワークの通信量が削減され、データベースの負荷が軽減されます。次を実行することを考慮してください。
多くの場合、様々な表セットで異なった集計を行う必要があります。通常、この目的を達成するには、表に複数のスキャンを実行しますが、単一のスキャンですべての集計を計算すると簡単です。n-1回のスキャンを排除することで、パフォーマンスが大幅に向上する可能性があります。
複数のスキャンを1つのスキャンに結合するには、各スキャンのWHERE条件をCASE式の中に移動して、集計するデータをフィルタします。各集計では、データを取り出す別の列があっても構いません。
次の例では、収入が毎月2000より少ない社員、2000から4000の社員、4000を超える社員の数を問い合せています。この結果を得るには、次の3つの個別の問合せを実行します。
SELECT COUNT (*) FROM employees WHERE salary < 2000; SELECT COUNT (*) FROM employees WHERE salary BETWEEN 2000 AND 4000; SELECT COUNT (*) FROM employees WHERE salary>4000;
しかし、1つの文で問合せ全体を実行するほうが効率的です。各数値は1つの列として計算されます。countファンクションは、CASE式でフィルタを使用して、条件が一致する行のみを数えます。たとえば、次のようにします。
SELECT COUNT (CASE WHEN salary < 2000
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN salary BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN salary > 4000
THEN 1 ELSE null END) count3
FROM employees;
これは、きわめて単純な例です。範囲が重なっていたり、集計の関数が異なっていることもあります。
適時、INSERT、UPDATEまたはDELETE...RETURNINGを使用して、1回のコールでデータを選択および変更します。この技法は、データベースのコール回数を減らすことでパフォーマンスを改善します。
|
関連項目: INSERT文、UPDATE文、およびDELETE文の構文については、『Oracle Database SQL言語リファレンス』を参照してください。 |
可能であれば、配列処理を使用します。これは、バインド変数値の配列をOracle Databaseに渡して繰返し実行することを意味します。これは、あるセットの複数行が同じ操作の対象である場合の繰り返し処理に適しています。
たとえば、次のようにします。
BEGIN
FOR pos_rec IN (SELECT *
FROM order_positions
WHERE order_id = :id) LOOP
DELETE FROM order_positions
WHERE order_id = pos_rec.order_id AND
order_position = pos_rec.order_position;
END LOOP;
DELETE FROM orders
WHERE order_id = :id;
END;
別の方法として、ordersに対するカスケード制約を定義できます。前述の例では、1つのSELECTに対してn個のDELETEが実行されます。ordersに対するDELETEとしてDELETE FROM orders WHERE order_id = :idを発行すると、データベースは、1回のDELETE文で複数の行を自動的に削除します。
|
関連項目: 分散問合せのチューニング方法の詳細は、『Oracle Database管理者ガイド』またはOracle Database Heterogeneous Connectivityユーザーズ・ガイドを参照してください。 |
多くのSQL関連の問題では、再現可能なテスト・ケースを取得することで問題の解決が容易になります。Oracle Database 11gリリース2(11.2)以上には、SQLテスト・ケース・ビルダーが含まれます。このSQLテスト・ケース・ビルダーは、問題とその問題が発生した環境に関する情報を可能なかぎり収集および再現するという、多少困難で時間のかかるプロセスを自動化します。
SQLテスト・ケース・ビルダーは、別のデータベースで問題を再現およびテストできるように、SQL関連の問題に関する情報と、その問題が発生した正確な環境を取得します。テスト・ケースの準備ができたら、ユーザーは問題をOracleサポートにアップロードし、サポート担当者にその問題の再現とトラブルシューティングを依頼できます。
SQLテスト・ケース・ビルダーによって収集される情報には、実行された問合せ、表および索引の定義(実際のデータではありません)、PL/SQLファンクション、プロシージャおよびパッケージ、オプティマイザ統計、初期化パラメータ設定が含まれます。
SQLテスト・ケース・ビルダーには、Enterprise Managerからアクセスできます。または、DBMS_SQLDIAGパッケージを使用して手動でもアクセスできます。
Enterprise ManagerからSQLテスト・ケース・ビルダーにアクセスできるのは、SQLインシデントが発生した場合のみです。SQL関連の問題は、SQLインシデントと呼ばれ、各SQLインシデントはインシデント番号で識別されます。SQLテスト・ケース・ビルダーには、Enterprise Managerの「サポート・ワークベンチ」ページからアクセスできます。
「サポート・ワークベンチ」ページには、次のいずれかの方法でアクセスできます。
Enterprise Managerの「データベース」の「ホーム」ページで、「診断サマリー」の下にある「アクティブなインシデント」(アクティブ・インシデントの数を示す)へのリンクをクリックします。これにより、インシデントがリストされた表を含む「サポート・ワークベンチ」ページが表示されます。
「関連リンク」の「アドバイザ・セントラル」をクリックすると、「アドバイザ・セントラル」ページが表示されます。次に、「SQLアドバイザ」をクリックしてから「ここをクリックしてサポート・ワークベンチに移動します。」をクリックすると、「サポート・ワークベンチ」ページが表示されます。
「サポート・ワークベンチ」ページから、次のようにSQLテスト・ケース・ビルダーにアクセスします。
インシデントIDをクリックすると、その特定のインシデントに関する問題の詳細が表示されます。
次に、「調査と解決」セクションで「Oracleサポート」をクリックします。
「追加的なダンプとテスト・ケースの生成」をクリックします。
特定のインシデントについて、「タスクに移動」列のアイコンをクリックし、SQLテスト・ケース・ビルダーを実行します。
SQLテスト・ケース・ビルダーの出力は、すべての必須オブジェクトおよび環境を再生成するのに必要なコマンドを含むSQLスクリプトです。SQLテスト・ケース・ビルダーは、次の場所にファイルを格納します。inc_numはインシデント番号を、run_numは実行番号を指します。
$ADR_HOME/incident/incdir_inc_num/SQLTCB_run num
たとえば、有効な出力ファイル名は次のようになります。
$ORACLE_HOME/log/diag/rdbms/dbsa/dbsa/incident/incdir_2657/SQLTCB_1
SQLテスト・ケース・ビルダーは、DBMS_SQLDIAGパッケージを使用して手動で起動することもできます。このパッケージは、SQLテスト・ケース・ビルダー用の様々なサブプログラムで構成されます。表16-1に、そのサブプログラムの一部を示します。
表16-1 DBMS_SQLDIAGのSQLテスト・ケース・ビルダーのプロシージャ
| プロシージャ名 | 機能 |
|---|---|
|
|
SQLテスト・ケースを生成します。 |
|
|
引数で渡されたインシデントIDに対応するSQLテスト・ケースを生成します。 |
|
|
引数で渡されたSQLテキストに対応するSQLテスト・ケースを生成します。 |
このパッケージと、そのすべてのプロシージャおよびパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
