12.3 CPUオーバーヘッドの最小化

ここでのトピック

12.3.1 SQL文のチューニング

PL/SQLプログラムの速度が低下する最も一般的な原因は、速度の遅いSQL文です。PL/SQLプログラムのSQL文をできるだけ効率的にするには:

12.3.2 問合せにおけるファンクションの起動のチューニング

問合せで起動されるファンクションは、数百万回実行される可能性があります。問合せで不必要にファンクションを起動しないようにして、起動はできるだけ効率的にします。

問合せの表にファンクション索引を作成してください。CREATE INDEX文には少し時間がかかる場合もありますが、各行のファンクション値がキャッシュされるため、問合せを非常に高速に実行できます。

問合せによって列をファンクションに渡す場合、問合せではその列でユーザー作成索引を使用できないため、表(非常に大きい可能性がある)の行ごとにファンクションが起動される可能性があります。ファンクションの起動数を最小限に抑えるには、ネストした問合せを使用します。内側の問合せで結果セットが少ない行数になるようにフィルタリングし、外側の問合せでそれらの行のみを対象にファンクションを起動します。

関連項目:

例12-5 ネストした問合せによるパフォーマンスの改善

この例では、2つの問合せで同じ結果セットを生成しますが、2番目の問合せは1番目の問合せより効率的です。(この例では、EMPLOYEES表が非常に小さいため、回数および時間の違いはごくわずかです。非常に大きい表では、これらの違いが顕著になります。) 

DECLARE
  starting_time  TIMESTAMP WITH TIME ZONE;
  ending_time    TIMESTAMP WITH TIME ZONE;
BEGIN
  -- Invokes SQRT for every row of employees table:
 
  SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;
 
  FOR item IN (
    SELECT DISTINCT(SQRT(department_id)) col_alias
    FROM employees
    ORDER BY col_alias
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);
  END LOOP;
 
  SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;
 
  DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));
 
  -- Invokes SQRT for every distinct department_id of employees table:
 
  SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;
 
  FOR item IN (
    SELECT SQRT(department_id) col_alias
    FROM (SELECT DISTINCT department_id FROM employees)
    ORDER BY col_alias
  )
  LOOP
    IF item.col_alias IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);
    END IF;
  END LOOP;
 
  SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;
 
  DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));
END;
/

結果は次のようになります。

Square root of dept. ID = 3.16227766016837933199889354443271853372
Square root of dept. ID = 4.47213595499957939281834733746255247088
Square root of dept. ID = 5.47722557505166113456969782800802133953
Square root of dept. ID = 6.32455532033675866399778708886543706744
Square root of dept. ID = 7.07106781186547524400844362104849039285
Square root of dept. ID = 7.74596669241483377035853079956479922167
Square root of dept. ID = 8.36660026534075547978172025785187489393
Square root of dept. ID = 8.94427190999915878563669467492510494176
Square root of dept. ID = 9.48683298050513799599668063329815560116
Square root of dept. ID = 10
Square root of dept. ID = 10.48808848170151546991453513679937598475
Time = +000000000 00:00:00.046000000
Square root of dept. ID = 3.16227766016837933199889354443271853372
Square root of dept. ID = 4.47213595499957939281834733746255247088
Square root of dept. ID = 5.47722557505166113456969782800802133953
Square root of dept. ID = 6.32455532033675866399778708886543706744
Square root of dept. ID = 7.07106781186547524400844362104849039285
Square root of dept. ID = 7.74596669241483377035853079956479922167
Square root of dept. ID = 8.36660026534075547978172025785187489393
Square root of dept. ID = 8.94427190999915878563669467492510494176
Square root of dept. ID = 9.48683298050513799599668063329815560116
Square root of dept. ID = 10
Square root of dept. ID = 10.48808848170151546991453513679937598475
Time = +000000000 00:00:00.000000000

12.3.3 サブプログラムの起動のチューニング

サブプログラムにOUTまたはIN OUTパラメータがある場合、NOCOPYヒントを使用してこれらのパラメータを宣言することによって、起動のオーバーヘッドを減らすことができます。

OUTパラメータまたはIN OUTパラメータが、コレクション、レコード、ADTのインスタンスなどの大規模なデータ構造を表す場合、それらのコピーによって実行速度が低下し、(特にADTのインスタンスで)メモリー使用量が増大します。

ADTメソッドが起動されるたびに、PL/SQLによってそのADTのすべての属性がコピーされます。メソッドが正常に終了すると、そのメソッドが属性に対して行った変更が、PL/SQLによって適用されます。メソッドが未処理例外を戻して終了すると、PL/SQLは、属性を変更しません。

プログラムで、サブプログラムが未処理例外を戻して終了したときにOUTパラメータまたはIN OUTパラメータに起動前の値を保持する必要がない場合、パラメータ宣言にNOCOPYヒントを含めます。NOCOPYヒントは、コンパイラが対応する実パラメータを値ではなく参照によって渡すように要求しますが、その保証はしません。

注意:

サブプログラムが未処理例外を戻して終了したときに実パラメータまたはADT属性で起動前の値を確実に保持するためには、NOCOPYに依存しないでください(特定の起動について、コンパイラはこのヒントに従う場合と従わない場合があります)。かわりに、サブプログラムですべての例外が処理されるようにします。

関連項目:

例12-6 NOCOPYサブプログラム・パラメータ

この例では、コンパイラがdo_nothing2の起動に関してNOCOPYヒントに従うと、do_nothing2の起動がdo_nothing1の起動より高速に実行されます。 

DECLARE
  TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;
  emp_tab EmpTabTyp := EmpTabTyp(NULL);  -- initialize
  t1 NUMBER;
  t2 NUMBER;
  t3 NUMBER;

  PROCEDURE get_time (t OUT NUMBER) IS
  BEGIN
    t := DBMS_UTILITY.get_time;
  END;

  PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
  BEGIN
    NULL;
  END;

  PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
  BEGIN
    NULL;
  END;

BEGIN
  SELECT * INTO emp_tab(1)
  FROM employees
  WHERE employee_id = 100;

  emp_tab.EXTEND(49999, 1);  -- Copy element 1 into 2..50000
  get_time(t1);
  do_nothing1(emp_tab);  -- Pass IN OUT parameter
  get_time(t2);
  do_nothing2(emp_tab);  -- Pass IN OUT NOCOPY parameter
  get_time(t3);
  DBMS_OUTPUT.PUT_LINE ('Call Duration (secs)');
  DBMS_OUTPUT.PUT_LINE ('--------------------');
  DBMS_OUTPUT.PUT_LINE ('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
  DBMS_OUTPUT.PUT_LINE ('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/

12.3.4 ループのチューニング

PL/SQLアプリケーションはループの周囲に構築されることが多いため、ループ自体とループ内のコードの両方を最適化することが重要です。

結果セットを2回以上ループしたり、結果セットのループ時に他の問合せを発行する必要がある場合、元の問合せを変更して、目的の結果を正確に取得することが可能な場合があります。複数の問合せを結合できるSQLの集合演算子を調査してください(『Oracle Database SQL言語リファレンス』を参照)。

副問合せを使用して、複数の段階でフィルタリングおよびソートを行うこともできます(「副問合せによる問合せ結果セットの処理」を参照)。

関連項目:

「バルクSQLおよびバルク・バインド」

12.3.5 計算集中型PL/SQLコードのチューニング

ここでの推奨事項は、特に計算集中型PL/SQLコードに適用されます(ただし、それらのコードに限定されません)。

ここでのトピック

12.3.5.1 ハードウェア算術を使用するデータ型の使用

NUMBERデータ型ファミリ(「NUMBERデータ型ファミリ」を参照)のデータ型を使用することは避けてください。これらのデータ型の内部的な表現形式は、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。これらの型のデータに対する操作では、ライブラリ算術計算が使用されますが、PLS_INTEGER型、BINARY_FLOAT型およびBINARY_DOUBLE型のデータに対する操作では、ハードウェア算術計算が使用されます。

ローカル整変数には、PLS_INTEGERを使用します(「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照)。パフォーマンスを重視するコードで使用する変数(NULL値を持つことがなく、オーバーフロー・チェックを必要としないもの)には、SIMPLE_INTEGERを使用します(PLS_INTEGERのSIMPLE_INTEGERサブタイプを参照)。

浮動小数点変数には、BINARY_FLOATまたはBINARY_DOUBLEを使用します(『Oracle Database SQL言語リファレンス』を参照)。パフォーマンスを重視するコードで使用する変数(NULL値を持つことがなく、オーバーフロー・チェックを必要としないもの)には、SIMPLE_FLOATまたはSIMPLE_DOUBLEを使用します(「BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQLサブタイプ」を参照)。

ノート:

BINARY_FLOATおよびBINARY_DOUBLEとそれらのサブタイプは、正確さが非常に重要な財務処理コードにはあまり適していません。これらの型は、常に正確に小数値を表すわけではなく、NUMBER型とは異なり四捨五入処理が行われるためです。

多くのSQL数値ファンクション(『Oracle Database SQL言語リファレンス』を参照)は、BINARY_FLOATパラメータおよびBINARY_DOUBLEパラメータを受け入れるバージョンでオーバーロードされます。これらのデータ型の変数をこのようなファンクションに渡したり、このようなファンクションに式を渡すときに変換ファンクションのTO_BINARY_FLOAT(『Oracle Database SQL言語リファレンス』を参照)およびTO_BINARY_DOUBLE(『Oracle Database SQL言語リファレンス』を参照)を起動することによって、計算集中型コードをスピードアップできます。

12.3.5.2 パフォーマンスを重視するコードでの制約付きサブタイプの回避

パフォーマンスを重視するコードでは、制約付きサブタイプ(「制約付きサブタイプ」を参照)を使用することは避けてください。制約付きサブタイプの変数またはパラメータに代入を行うたびに、代入される値が制約に違反していないことを保証するために実行時に余分なチェックが必要になります。

関連項目:

「PL/SQLの事前定義のデータ型」(事前定義の制約付きサブタイプを含む)

12.3.5.3 暗黙的なデータ型変換の最少化

PL/SQLは実行時に、必要に応じて異なるデータ型を暗黙的(自動的)に変換します。たとえば、PLS_INTEGER変数をNUMBER変数に代入すると、PL/SQLによって、PLS_INTEGER値がNUMBER値に変換されます(各値の内部表現は異なるため)。

可能であれば、暗黙的な変換は常に最小限に抑えてください。たとえば:

  • 表の列に変数を挿入する場合、または表の列から変数に値を代入する場合、その変数に表の列と同じデータ型を指定します。

    ヒント:

    %TYPE属性を使用して変数を宣言します(「%TYPE属性」を参照)。

  • 各リテラルが、割当て先の変数または記述対象の式と同じデータ型になるようにします。

  • SQLデータ型からPL/SQLデータ型に値を変換し、変換した値を式で使用します。

    たとえば、NUMBER値をPLS_INTEGER値に変換し、そのPLS_INTEGER値を式で使用します。PLS_INTEGER演算はハードウェア算術計算を使用するため、ライブラリ算術計算を使用するNUMBER演算より処理速度が速くなります。PLS_INTEGERデータ型の詳細は、「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照してください。

  • あるSQLデータ型の値を別のSQLデータ型の変数に代入する前に、SQL変換ファンクションを使用してソース値をターゲット・データ型に明示的に変換します(SQL変換ファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください)。

  • 異なるデータ型のパラメータを受け入れるバージョンでサブプログラムをオーバーロードし、そのパラメータ型の各バージョンを最適化します。オーバーロードされたサブプログラムの詳細は、「オーバーロードされたサブプログラム」を参照してください。

関連項目:

12.3.6 SQL文字関数の使用

SQLには、PL/SQLコードよりも効率的な低レベルのコードを使用する高度に最適化された文字ファンクションが多くあります。PL/SQLコードを記述するかわりにこれらのファンクションを使用することで、同じ処理を実行できます。

参照:

12.3.7 最低コストの条件テストの先頭への配置

PL/SQLは、結果が判別できた時点でただちに論理式の評価を停止します。可能であれば常に、最低コストの条件を論理式の最初に配置して評価することで、この短絡評価を利用してください。たとえば、変数テストに失敗した場合、PL/SQLでファンクションを起動する必要がなくなるように、ファンクションの戻り値をテストする前に、PL/SQL変数の値をテストします。

IF boolean_variable OR (number > 10) OR boolean_function(parameter) THEN ...

関連項目:

「短絡評価」