10gリリース2（10.2）
B19207-02
 目次 |
 索引 |
| Oracle Database パフォーマンス・チューニング・ガイド 10gリリース2（10.2） B19207-02 |
|
この章では、チューニングの目的、多くのリソースを消費するSQL文の識別方法および収集する内容の説明と、チューニングの提案を示します。
この章には次の項があります。
SQL文のチューニングは、データベース・システムのパフォーマンス・チューニングの重要な側面です。SQLチューニングには、次の3つの基本手順を実行します。
システム・パフォーマンスが十分なレベルに達するか、他にチューニングできる文がなくなるまで、この3つの手順を繰り返します。
システムをチューニングする目的は、システムのエンド・ユーザーへの応答時間を短縮したり、同じ作業の処理に使用されるリソースを削減することです。これには、次の方法があります。
一般に、SQLチューニングには、同じワークロードをより効率的に処理する方法を見つけ出すことが含まれます。機能性を変えることなく文の実行計画を変更し、リソース使用量を削減することは可能です。
リソース使用量を削減する方法の2つの例を、次に示します。
システムは、実ユーザーがシステムに接続している昼間に使用量が最大に達し、夜間には低下する傾向があります。重要でないレポートやバッチ・ジョブを夜間に実行するようにスケジューリングし、昼間の同時実行性が削減されれば、昼間の、より重要なプログラムのためにリソースが解放されます。
大量のデータにアクセスする問合せ（代表的なものは、データ・ウェアハウス問合せ）は、多くの場合、パラレル化できます。これは特に、同時実行性が低いデータ・ウェアハウスで応答時間を短縮する場合に有効です。ただし、同時実行性が高い傾向のあるOLTP環境の場合は、プログラム全体のリソース使用量を増加させることになり、他のユーザーに影響を与える可能性があります。
この項では、高負荷SQL文について、識別およびデータ収集を行う手順を説明します。高負荷のSQLとは、Oracleデータベースのパフォーマンスに影響を与える、パフォーマンスの低いリソース集中型のSQL文です。高負荷SQL文は、次の手段で識別できます。
リソース集中型のSQLを識別する最初のステップは、検討する問題の分類です。
特定のプログラム（GUIまたは3GL）をチューニングする場合、検討するSQLの識別は、プログラム内で実行されたSQLを見るだけの簡単な作業です。Oracle Enterprise Managerに用意されているツールを使用して、リソースを多く使用するSQL文の識別、EXPLAIN PLANの生成およびSQLパフォーマンスの評価ができます。
|
関連項目:
|
SQLを識別できない（たとえば、SQLが動的に生成される）場合は、SQL_TRACEを使用して、実行されたSQLを含むトレース・ファイルを生成し、次にTKPROFを使用して出力ファイルを生成します。
TKPROF出力ファイル内のSQL文は、実行経過時間（exeela）などの各種パラメータで順序付けできるため、通常は、SQL文を経過時間で順序付けする（経過時間が最も長いSQL文をファイルの最も上に置く）ことで識別に利用されます。これにより、ファイル内にSQL文が多数ある場合に、パフォーマンスの低いSQLを識別するジョブの実行が容易になります。
アプリケーション全体のパフォーマンスが十分に最適化されていない場合や、データベース・サーバーのCPUまたはI/O全体の負荷を減らそうとする場合は、次の手順で、多くのリソースを消費するSQLを識別します。
V$FILESTAT）、システム統計（V$SYSSTAT）、およびSQL統計（V$SQLAREA、V$SQLまたはV$SQLSTATS、V$SQLTEXT、V$SQL_PLANおよびV$SQL_PLAN_STATISTICS）です。 V$SQLSTATSを問い合せる方法が適しています。V$SQLSTATSには、共有プール内のすべてのSQL文に関するリソース使用率の情報が含まれています。V$SQLSTATS内のデータを、リソース使用率で順序付けしてください。共通リソースの主なものは、次のとおりです。
負荷の最も大きいSQL文を識別する方法の1つは、その期間内にSQL文で使用されたリソースを、同じ期間内に使用されたそのリソースの総量と比較することです。BUFFER_GETSの場合、各SQL文のBUFFER_GETSの回数を、期間中のバッファ取得の総数で除算します。システム内のバッファ取得の総数はV$SYSSTAT表のsession logical readsの統計情報からわかります。
同様に、V$SQL_STATS.DISK_READSをV$SYSSTAT統計の物理読込みの値で除算すると、システムによって実行されるディスク読取りの総数のうち、文によって実行されるディスク読取りの割合を計算できます。自動ワークロード・リポジトリ・レポートのSQLセクションにはこのデータが含まれているため、割合を手動で計算する必要はありません。
候補のSQL文を識別した後、文を調べるために必要な情報を収集し、チューニングします。
CPUが問題となっている場合は、一定期間内に最も多くのBUFFER_GETSを実行した上位のSQL文を調べます。その他の場合は、最も多くのDISK_READSを実行したSQL文から始めます。
チューニング・プロセスではまず、基礎となる表と索引の構造を判別します。収集する情報は、次のとおりです。
V$SQLTEXTからの完全なSQLテキスト
EXPLAIN PLAN、V$SQL_PLANまたはTKPROF出力のいずれかからのもの）のオプティマイザ計画の安定性
手動SQLチューニング・プロセスはアプリケーション開発者に多数の作業が課されるため、SQLチューニング・プロセスは自動SQLチューニング管理機能により自動化されています。これらの機能は、OLTPタイプとデータ・ウェアハウス・タイプのアプリケーションで同様に機能するように設計されています。第12章「自動SQLチューニング」を参照してください。
Automatic Database Diagnostic Monitor（ADDM）では、高負荷SQL文など、Oracleデータベースにおいて考えられるパフォーマンス上の問題について、AWRによって収集された情報が分析されます。「Automatic Database Diagnostic Monitor」を参照してください。
SQLチューニング・アドバイザでは、SQL文を変更せずにSQL文を素早く効率的に最適化できます。「SQLチューニング・アドバイザ」を参照してください。
複数のSQL文がADDMまたはSQLチューニング・アドバイザへの入力として使用される場合、SQLチューニング・セット（STS）が構成され、格納されます。STSには、SQL文のセットと、関連する実行コンテキストおよび基本実行統計が含まれます。「SQLチューニング・セット」を参照してください。
Oracleでは、SQLチューニング・アドバイザの他に、SQLアクセス・アドバイザが用意されています。これは、マテリアライズド・ビュー、索引およびマテリアライズド・ビュー・ログについてアドバイスを提供します。SQLアクセス・アドバイザでは、指定のワークロードに関するマテリアライズド・ビュー、マテリアライズド・ビュー・ログおよび索引の適切なセットが推奨されるため、パフォーマンスの目標を達成するのに役立ちます。一般に、マテリアライズド・ビューおよび索引の数と、これらに割り当てられている領域が増えるにつれて、問合せのパフォーマンスが向上します。SQLアクセス・アドバイザでは、領域使用量と問合せパフォーマンスの兼合いが考慮され、新規および既存のマテリアライズド・ビューおよび索引の最もコスト効率の高い構成が推奨されます。
Oracle Enterprise Manager Database ControlからSQLアクセス・アドバイザにアクセスする手順は、次のとおりです。
「SQLアクセス・アドバイザの使用方法」を参照してください。
この項では、SQL文の効率を高める方法を説明します。
問合せオプティマイザでは、最適な実行計画の判別時に、表と索引について収集された統計を使用します。これらの統計が収集されなかった場合、または、統計がデータベース内に格納されているデータをすでに反映しなくなっている場合、オプティマイザには最適な計画を生成するための十分な情報がありません。
チェックする内容
DBA_TABLES.NUM_ROWSの値を比較することです。また、述語列に大きなデータの偏りがある場合は、ヒストグラムを使用することを検討してください。
OLTP環境でSQL文をチューニング（または作成）する場合、最も選択性の高いフィルタを持つ表から駆動することを目標とします。つまり、次のステップに渡される行を少なくするということです。次のステップが結合である場合は、少数の行しか結合されないということになります。アクセス・パスが最適かどうかを確認してください。
オプティマイザの実行計画を調べる場合は、次の内容を確認します。
SELECT構文のリストを見て、ビューへのアクセスが必要であるかどうかを確認してください。
SQL文の述語と、表の行数を評価します。大量の行を持つ表の全表スキャンなど、WHERE句に述語を持つ疑わしいアクティビティを探します。そのような選択的な述語に索引が使用されない理由を判別します。
全表スキャンが非効率的というわけではありません。小さい表で全表スキャンを行う場合や、戻される行数に対してよりよい結合方法（たとえば、hash_join）を活用するために、全表スキャンを行う方が効率がよい場合があります。
これらの条件のうち最適でないものがある場合は、SQL文の再構成や、表で使用できる索引について考慮します。
非効率的な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はこの式を次のように変換します。
AND TO_NUMBER(charcol) = numexpr
次に示すタイプの複合式は使用しないようにしてください。
ここに示した式によって、オプティマイザは有効なカーディナリティまたは選択性の見積りを割り当てることができなくなります。この結果、全体の計画および結合方法に悪い影響を与えます。
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;
フィルタまたは述語結合でSQLファンクションを使用する必要がある場合、索引を持つ列に対しては使用しないでください。そのかわり、次の文に示すように、述語の反対側にSQLファンクションを使用してください。
TO_CHAR(numcol) = varcol
次の例は使用しません。
varcol = TO_CHAR(numcol)
SQLは、手続き型言語ではありません。1つのSQLを使用して様々なことを実行すると、通常は各タスクに最適でない結果が生じます。SQLを使用して様々なタスクを実行する場合は、1つの文にパラメータを指定して異なるタスクを実行するのではなく、様々な文を作成してください。
通常、異なるタスクには個別の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は、この排他性により論理的に有効です。これにより、問合せの両半分の結果から重複行を除外するためにコストの高いソートを実行することなく、計画を実行できます。）
ある環境では、EXISTSよりINを使用した方が適していることがあります。一般に、選択的述語が副問合せにある場合は、INを使用します。選択的述語が親問合せの中にある場合は、EXISTSを使用します。
副問合せにIN句を使用した場合に、副問合せで指定される選択性の利点を活用するために、Oracleが副問合せをリライトすることがあります。これは、最も選択性の高いフィルタが副問合せにある場合、結合列に索引がある場合に、最も有効です。これに対し、EXISTSは、最も選択性の高いフィルタが親問合せにある場合に有効です。その場合、EXISTS基準に照らして行をフィルタにかける前に、親問合せの選択的述語を適用できるからです。
「例1: INの使用: 副問合せ内の選択的フィルタ」および「例2: EXISTSの使用: 親の中の選択的述語」に、INおよびEXISTSの利点を実証する2つの例を示します。いずれの例でも、次の特性を持つ同じスキーマを使用します。
employees.employee_idフィールドに一意索引があります。
orders.customer_idフィールドに索引があります。
employees.department_idフィールドに索引があります。
employees表には27,000行あります。
orders表には10,000行あります。
OEおよびHRスキーマに、これらのセグメントがあり、ともにCOMPUTEで分析されています。
この例は、INを使用するように問合せをリライトすると、パフォーマンスがどのように向上するかを示しています。この問合せでは、顧客144のオーダーを発注したすべての社員を識別します。
EXISTSを使用するSQL文
SELECT /* EXISTS example */ e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE EXISTS (SELECT 1 FROM orders o /* Note 1 */ WHERE e.employee_id = o.sales_rep_id /* Note 2 */ AND o.customer_id = 144); /* Note 3 */
次の計画出力は、前述の文の実行計画（V$SQL_PLANからのもの）です。この計画では、employees表の全表スキャンを必要とし、そのため、多数の行が戻されます。次に、戻された各行がorders表で（索引を経由して）フィルタにかけられます。
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 FILTER 2 TABLE ACCESS FULL EMPLOYEES ANA 155 3 TABLE ACCESS BY INDEX ROWID ORDERS ANA 3 4 INDEX RANGE SCAN ORD_CUSTOMER_IX ANA 1
INを使用して文をリライトすると、使用されるリソースが大幅に減少します。
INを使用するSQL文
SELECT /* IN example */ e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE e.employee_id IN (SELECT o.sales_rep_id /* Note 4 */ FROM orders o WHERE o.customer_id = 144); /* Note 3 */
次の計画出力は、前述の文の実行計画（V$SQL_PLANからのもの）です。オプティマイザは副問合せをビューにリライトし、次にそれが一意索引でemployees表に結合されます。この結果、計画は大幅に改善されます。ビュー（すなわち、副問合せ）に選択的述語があるため、わずかなemployee_idのみが戻されるためです。次に、このわずかなemployee_idで、一意索引からemployees表にアクセスします。
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 NESTED LOOPS 5 2 VIEW 3 3 SORT UNIQUE 3 4 TABLE ACCESS FULL ORDERS ANA 1 5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1 6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA
この例は、EXISTSを使用するように問合せをリライトすると、パフォーマンスがどのように向上するかを示しています。この問合せでは、オーダーを発注した、部門80の全営業社員を識別します。
INを使用するSQL文
SELECT /* IN example */ e.employee_id, e.first_name, e.last_name, e.department_id, e.salary FROM employees e WHERE e.department_id = 80 /* Note 5 */ AND e.job_id = 'SA_REP' /* Note 6 */ AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /* Note 4 */
次の計画出力は、前述の文の実行計画（V$SQL_PLANからのもの）です。SQL文は、orders表でビューを使用するようにオプティマイザでリライトされています。この文では、orders表に存在するすべての一意のemployee_idを戻すためにデータのソートが必要です。述語がないため、多数のemployee_idsが戻されます。この多数のemployee_idからなる大きなリストが、一意索引を使用してemployees表へのアクセスに使用されることになります。
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 NESTED LOOPS 125 2 VIEW 116 3 SORT UNIQUE 116 4 TABLE ACCESS FULL ORDERS ANA 40 5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1 6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA
EXISTSを使用するSQL文
SELECT /* EXISTS example */ e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE e.department_id = 80 /* Note 5 */ AND e.job_id = 'SA_REP' /* Note 6 */ AND EXISTS (SELECT 1 /* Note 1 */ FROM orders o WHERE e.employee_id = o.sales_rep_id); /* Note 2 */
次の計画出力は、前述の文の実行計画（V$SQL_PLANからのもの）です。計画のコストは、EXISTSを使用するようにSQL文をリライトすることで削減されます。この計画は、より効率的です。これは、2つの索引を使用して親問合せ内の述語を満たすため、戻されるemployee_idがきわめて少ないためです。次に、このemployee_idを使用して、索引からorders表にアクセスします。
ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 FILTER 2 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 98 3 AND-EQUAL 4 INDEX RANGE SCAN EMP_JOB_IX ANA 5 INDEX RANGE SCAN EMP_DEPARTMENT_IX ANA 6 INDEX RANGE SCAN ORD_SALES_REP_IX ANA 8
オプティマイザのアプローチと目標の設定および問合せオプティマイザの代表的な統計の収集によって、オプティマイザの選択を変えることができます。特定のアプリケーション・データに関して、オプティマイザよりも多くの情報を持つアプリケーション設計者であれば、より効率よくSQL文を実行する方法を選択できる場合があります。SQL文のヒントを使用すれば、文を実行する方法をオプティマイザに指示できます。
/*+FULL */などのヒントは、アクセス・パスを制御します。 たとえば、次のようにします。
SELECT /*+ FULL(e) */ e.last_name FROM employees e WHERE e.job_id = 'CLERK';
結合順序は、パフォーマンスに大きな影響を与えることがあります。SQLのチューニングの主な目的は、結果に影響しない不要な行にアクセスする作業を回避することです。このことから次の3つの一般ルールが導かれます。
次の例は、結合順序を効果的にチューニングする方法を示しています。
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ビューを問い合せてこの情報を取得することは非効率的です。
副問合せのネストの解除によって、副問合せ本体が解除され、その副問合せが含まれている文の本体にマージされます。これにより、オプティマイザは、アクセス・パスと結合を評価するときに、副問合せと本体の両方をいっしょに考慮させてしまいます。
複数表のビューに対する外部結合の場合、等価述語が定義されていれば、問合せオプティマイザ（リリース8.1.6以上）は外部結合列から駆動できます。
ビュー内での外部結合は、外部結合のパフォーマンスに与える影響が読めないため、問題が発生しやすくなります。
中間の表、すなわちステージング表がリレーショナル・データベース・システムで比較的よく利用されるのは、それらの表に中間結果を一時的に格納するためです。これは、多くのアプリケーションで役に立つものですが、作成するにはさらにリソースが必要になります。したがって、これらの表による利益が、作成のコストに見合うものかどうかを常に考慮してください。ステージング表は、その情報が何回も再利用されない場合は、作成しないようにしてください。
他の考慮事項
多くの場合は、索引を再構成すると、パフォーマンスが向上します。これには、次の内容が含まれます。
索引を万能策として使用しないでください。アプリケーションの開発者は、索引を多く作成すればパフォーマンスが改善されると考えることがあります。1人のプログラマが適切に索引を作成すれば、アプリケーションのパフォーマンスは十分に改善される可能性があります。ただし、50名のプログラマが別々に索引を作成すると、アプリケーションのパフォーマンス改善は望めません。
トリガーを使用すると、システムのリソースが消費されます。使用するトリガーが多すぎると、パフォーマンスに悪影響が及ぶので、トリガーを変更または使用禁止にする必要がある場合があります。
索引と文を再構成した後で、データの再構成について検討できます。
GROUP BYの使用を回避します。
格納されている統計またはSQL実行計画を使用すると、SQL文の既存の実行計画を長期的に保持できます。表のオプティマイザの統計を格納すると、その表を参照するすべてのSQL文にその統計が適用されます。実行計画を格納すると（すなわち、プラン・スタビリティ）、単一のSQL文の計画が保持されます。統計およびストアド・プランの両方がSQL文に対して使用可能な場合は、オプティマイザはストアド・プランの方を使用します。
アプリケーションから各行へのアクセスを、可能なかぎり1回のみにします。そうすることで、ネットワークの通信量が削減され、データベースの負荷が軽減されます。次を実行することを考慮してください。
多くの場合、様々な表セットで異なった集計を行う必要があります。通常、この計算は、表に複数のスキャンを行って処理されますが、1回の単一のスキャンですべての集計を計算すると簡単です。n-1回のスキャンを排除することで、パフォーマンスを大幅に向上できます。
複数のスキャンを1つのスキャンに結合するには、各スキャンのWHERE条件の内容をCASE文の中に移動します。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回のコールでデータを選択および変更します。この技法は、データベースのコール回数を減らすことでパフォーマンスを改善します。
可能であれば、配列処理を使用します。つまり、バインド変数の値の配列が繰返し実行のためにOracleに渡されます。これは、あるセットの複数行が同じ操作の対象である場合のくり返し処理に適しています。
たとえば、次のようにします。
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に対してDELETEを発行すると、データベースは、1回のDELETE文で複数の行を自動的に削除します。
|
 Copyright © 2000, 2008, Oracle Corporation. All Rights Reserved. |
|