スケーラブルなアプリケーションの作成

ユーザー移入およびデータ量にかかわらず、同じリソースを使用し、システム・リソースに過負荷をかけないようにアプリケーションを設計してください。

スケーラブルなアプリケーションについて

スケーラブルなアプリケーションは、システム・リソースの使用量の増加に比例して、より多くのワークロードを処理できます。

スケーラブルなアプリケーションは、システム・リソースの使用量の増加に比例して、より多くのワークロードを処理できます。たとえば、ワークロードが倍増した場合、スケーラブルなアプリケーションでは、システム・リソースが2倍使用されます。

非スケーラブルなアプリケーションでは、システム・リソースが消耗されるため、アプリケーション・ワークロードを増やした場合、スループットはより向上しません。非スケーラブルなアプリケーションの場合、スループットが固定され、レスポンス時間が低下します。

リソースの消耗の例:

ハードウェアの消耗
ディスクの入力/出力(I/O)不足を必然的に引き起こす、大規模トランザクションでの表スキャン
ネットワークおよびスケジューリングのボトルネックを引き起こす過剰なネットワーク・リクエスト
ページングおよびスワッピングを引き起こすメモリー割当て
オペレーティング・システムのスラッシングを引き起こす、過剰なプロセスおよびスレッド割当て

バインド変数を使用したスケーラビリティの向上

バインド変数を正しく使用すると、効率的でスケーラブルなアプリケーションを開発できます。

バインド変数はSQL文のプレースホルダで、SQL文の実行を正常に行うには、有効な値または値のアドレスと置換される必要があります。バインド変数を使用すると、実行時に入力またはパラメータを受け取るSQL文を作成できます。

サブプログラムがパラメータを持ち、その値をインボーカが指定するのと同じように、SQL文はバインド変数のプレースホルダを持ち、その値(バインド変数と呼ばれる)は実行時に指定されます。サブプログラムがコンパイルされると、様々なパラメータで何度も実行されるのと同じように、バインド変数のプレースホルダを持つSQL文は、ハード解析されると、様々なバインド変数でソフト解析されます。

最適化および行ソース生成を含むハード解析は、CPUに大きく負荷をかける処理です。最適化および行ソース生成がスキップされ、ただちに実行されるソフト解析は、同じ文のハード解析より通常大幅に処理が速くなります。(ハード分析とソフト解析の違いを含む、SQL処理の概要は、『Oracle Database概要』を参照してください。)

ハード解析はCPUに負担をかける処理であるだけでなく、他の多くの処理と同時に実行できないため非スケーラブルな処理です。同時実行性およびスケーラビリティの詳細は、"同時実行性およびスケーラビリティについて"を参照してください。

例8-1は、バインド変数のない問合せとバインド変数のある意味的に同等の問合せのパフォーマンスの違いを示しています。前者は、速度が遅く、より多くのラッチが使用されます(ラッチはスケーラビリティに与えます影響の詳細は、参照)。パフォーマンス統計を収集し表示するために、この例では、Runstatsツール("「Runstatsによるプログラミング手法の比較」で説明)が使用されている。

ノート:

例8-1は、単一のユーザーのパフォーマンス・コストを示しています。ユーザーが追加されると、コストは急速に増大します。
例8-1の結果は、この設定で作成されました。
```
SET SERVEROUTPUT ON FORMAT TRUNCATED
```

ノート:

文字列リテラルではなくバインド変数を使用すると、SQLインジェクション攻撃に弱いコードを最も効率良く作成できます。詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
バインド変数は、データ・ウェアハウス・システムの効率性を損なう場合があります。ほとんどの問合せは非常に多くの時間を要するため、オプティマイザは、最適な汎用問合せではなく、問合せごとに最適なプランの作成を試みます。バインド変数を使用した場合、オプティマイザにより最適な汎用問合せが強制的に作成されることがあります。データ・ウェアハウス・システムのパフォーマンスの向上の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』に関する項を参照してください。

ソフト解析はハード解析よりも効率的ですが、文を何度もソフト解析するコストは依然として非常に高くなります。アプリケーションの効率性およびスケーラビリティを最大化するには、解析を最小化してください。解析を最小化する最も簡単な方法は、PL/SQLの使用です。

例8-1パフォーマンスを向上させるバインド変数

CREATE TABLE t ( x VARCHAR2(5) );

DECLARE
  TYPE rc IS REF CURSOR;
  l_cursor rc;
BEGIN
  runstats_pkg.rs_start;  -- Collect statistics for query without bind variable

  FOR i IN 1 .. 5000 LOOP
    OPEN l_cursor FOR 'SELECT x FROM t WHERE x = ' || TO_CHAR(i);
    CLOSE l_cursor;
  END LOOP;

  runstats_pkg.rs_middle;  -- Collect statistics for query with bind variable

  FOR i IN 1 .. 5000 LOOP
    OPEN l_cursor FOR 'SELECT x FROM t WHERE x = :x' USING i;
    CLOSE l_cursor;
  END LOOP;

  runstats_pkg.rs_stop(500);  -- Stop collecting statistics
end;
/

結果は、次のようなテキストになります。

Run 1 ran in 740 hsec
Run 2 ran in 30 hsec
Run 1 ran in 
2466.67% of the time of run 2

Name                                   Run 1         Run 2    Difference
STAT...recursive cpu usage               729            19          -710
STAT...CPU used by this sessio           742            30          -712
STAT...parse time elapsed              1,051             4        -1,047
STAT...parse time cpu                  1,066             2        -1,064
STAT...session cursor cache hi             1         4,998         4,997
STAT...table scans (short tabl         5,000             1        -4,999
STAT...parse count (total)            10,003         5,004        -4,999
LATCH.session idle bit                 5,003             3        -5,000
LATCH.session allocation               5,003             3        -5,000
STAT...execute count                  10,003         5,003        -5,000
STAT...opened cursors cumulati        10,003         5,003        -5,000
STAT...parse count (hard)             10,001             5        -9,996
STAT...CCursor + sql area evic        10,000             1        -9,999
STAT...enqueue releases               10,008             7       -10,001
STAT...enqueue requests               10,009             7       -10,002
STAT...calls to get snapshot s        20,005         5,006       -14,999
STAT...calls to kcmgcs                20,028            35       -19,993
STAT...consistent gets pin (fa        20,013            17       -19,996
LATCH.call allocation                 20,002             6       -19,996
STAT...consistent gets from ca        20,014            18       -19,996
STAT...consistent gets                20,014            18       -19,996
STAT...consistent gets pin            20,013            17       -19,996
LATCH.simulator hash latch            20,014            11       -20,003
STAT...session logical reads          20,080            75       -20,005
LATCH.shared pool simulator           20,046             5       -20,041
LATCH.enqueue hash chains             20,343            15       -20,328
STAT...recursive calls                40,015        15,018       -24,997
LATCH.cache buffers chains            40,480           294       -40,186
STAT...session pga memory max        131,072        65,536       -65,536
STAT...session pga memory            131,072        65,536       -65,536
LATCH.row cache objects              165,209           139      -165,070
STAT...session uga memory max        219,000             0      -219,000
LATCH.shared pool                    265,108           152      -264,956
STAT...logical read bytes from   164,495,360       614,400  -163,880,960

Run 1 latches total compared to run 2 -- difference and percentage
         Run 1         Run 2          Diff       Pct
       562,092           864      -561,228  2,466.67%

PL/SQL procedure successfully completed.

PL/SQLを使用したスケーラビリティの向上

特定のPL/SQL機能を使用して、アプリケーションのスケーラビリティを改善できます。

PL/SQLによる解析の最小化の方法

データベース・アクセスに最適化されたPL/SQLは、文を暗黙的にキャッシュします。PL/SQLでは、カーソルを閉じると、カーソルはパースペクティブから閉じられますが(つまり、開いているカーソルが必要な場合にそのカーソルを使用できない)、実際にはPL/SQLはカーソルを開いたままにし、その文をキャッシュする。

キャッシュされた文を再度使用する場合、PL/SQLは同じカーソルを使用することで、解析を回避します。(必要に応じてPL/SQLはキャッシュされた文を閉じます。たとえば、プログラムで他のカーソルをオープンする必要があるが、オープンとOPEN_CURSORSのinit.ora設定を超えるような場合です)。

PL/SQLは、実行時に変化しないSQL文のみをキャッシュできます。

EXECUTE IMMEDIATE文について

EXECUTE IMMEDIATE文は、動的SQL文を一度の操作で作成して実行します。

EXECUTE IMMEDIATE文の基本構文は次のとおりです。

EXECUTE IMMEDIATE sql_statement

sql_statementとは、SQL文を表す文字列のことです。EXECUTE IMMEDIATE文が実行されるたびにsql_statementの値が同じ場合、PL/SQLはEXECUTE IMMEDIATE文をキャッシュできます。EXECUTE IMMEDIATE文の実行ごとに異なる可能性がある場合、PL/SQLはEXECUTE IMMEDIATE文をキャッシュできません。

関連情報:

EXECUTE IMMEDIATEの詳細は、Oracle Database PL/SQL言語リファレンス
DBMS_SQLパッケージについて

OPEN FOR文について

OPEN FOR文の基本構文は、次のとおりです。

OPEN FOR文の基本構文は次のとおりです。

OPEN cursor_variable FOR query

アプリケーションは、cursor_variableを閉じる前に様々な問合せに対して開くことができます。PL/SQLは、実行時まで様々な問合せの数を特定できないため、PL/SQLはOPEN FOR文をキャッシュできません。

カーソル変数を使用する必要がない場合、パフォーマンスおよびプログラミングの容易性を向上するために、宣言されたカーソルを使用してください。詳細は、『Oracle Database開発ガイド』に関する項を参照してください。

関連情報:

Oracle Database PL/SQL Language Reference for information about OPEN FOR
「カーソル変数について」
「カーソルについて」

DBMS_SQLパッケージについて

DBMS_SQLパッケージは、動的SQL文を作成、実行および説明するためのAPIである。PL/SQLコンパイラがコンパイル時に出力ホスト変数(選択リスト項目)または入力バインド変数の数値またはタイプを決定できない場合に、EXECUTE IMMEDIATE文ではなくDBMS_SQLパッケージを使用する必要があります。

DBMS_SQLパッケージは、動的SQL文を作成、実行および説明するためのAPIである。DBMS_SQLパッケージの使用はEXECUTE IMMEDIATE文の使用よりも手間がかかりますが、PL/SQLコンパイラがコンパイル時に出力ホスト変数(選択リスト項目)または入力バインド変数の数値またはタイプを決定できない場合にDBMS_SQLパッケージを使用する必要があります。

関連情報:

DBMS_SQLパッケージを使用するタイミングの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
DBMS_SQLパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
EXECUTE IMMEDIATE文について

バルクSQLについて

バルクSQLは、PL/SQLとSQLの間のラウンド・トリップ数を削減し、これによりリソースの使用が減少する。

バルクSQLを使用しない場合、データベースから1度に1行取得し(SQL)、処理して(PL/SQL)、データベースに返します(SQL)。バルクSQLを使用する場合、データベースから行セットを取得し、行セットを処理して、データベースにセット全体を返します。

Oracleでは、例8-2のように、データベースから複数の行を取得してデータベースに戻す場合、バルクSQLを使用することをお薦めします。次の例に示すように、複数の行を取得するが、それらを返す必要がない場合は、バルクSQLは必要ありません。

FOR x IN (SELECT * FROM t WHERE ... )  -- Retrieve row set (implicit array fetch)
  LOOP
    DBMS_OUTPUT.PUT_LINE(t.x);          -- Process rows but do not return them
  END LOOP;

例8-2は、表object_nameをループして、100行のセットを取得し、処理して、データベースに返します。(バルクFETCH文を100行に制限する場合、明示的なカーソルが必要です。)

例8-3は、バルクSQLを使用しないで例8-2と同じジョブを実行します。

例8-2および例8-3のTKPROFレポートが示すとおり、このジョブではバルクSQLを使用すると、CPU時間が約50%減ります。

SELECT ROWID RID, OBJECT_NAME FROM T T_BULK

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      721      0.17       0.17          0      22582          0       71825
********************************************************************************
UPDATE T SET OBJECT_NAME = :B1 WHERE ROWID = :B2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    719     12.83      13.77          0      71853      74185       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      720     12.83      13.77          0      71853      74185       71825


SELECT ROWID RID, OBJECT_NAME FROM T T_SLOW_BY_SLOW

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      721      0.17       0.17          0      22582          0       71825
********************************************************************************
UPDATE T SET OBJECT_NAME = :B2 WHERE ROWID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  71824     21.25      22.25          0      71836      73950       71824
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    71825     21.25      22.25          0      71836      73950       71824

ただし、このジョブにバルクSQLを使用すると、TKPROFレポートに示すとおり、単一のSQL文の使用よりもCPU時間、およびコードが多く使用されます。

UPDATE T SET OBJECT_NAME = SUBSTR(OBJECT_NAME,2) || SUBSTR(OBJECT_NAME,1,1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.30       1.44          0       2166      75736       71825
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.30       1.44          0       2166      75736       71825

例8-2 バルクSQL

CREATE OR REPLACE PROCEDURE bulk AS
  TYPE ridArray IS TABLE OF ROWID;
  TYPE onameArray IS TABLE OF t.object_name%TYPE;

  CURSOR c is SELECT ROWID rid, object_name  -- explicit cursor
              FROM t t_bulk;

  l_rids    ridArray;
  l_onames  onameArray;
  N         NUMBER := 100;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT
    INTO l_rids, l_onames LIMIT N;   -- retrieve N rows from t

    FOR i in 1 .. l_rids.COUNT
      LOOP                           -- process N rows
        l_onames(i) := substr(l_onames(i),2) || substr(l_onames(i),1,1);
      END LOOP;

      FORALL i in 1 .. l_rids.count  -- return processed rows to t
        UPDATE t
        SET object_name = l_onames(i)
        WHERE ROWID = l_rids(i);
        EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/

例8-3 バルクSQLを使用しない場合

CREATE OR REPLACE PROCEDURE slow_by_slow AS
BEGIN
  FOR x IN (SELECT rowid rid, object_name FROM t t_slow_by_slow)
    LOOP
      x.object_name := substr(x.object_name,2) || substr(x.object_name,1,1);

      UPDATE t
      SET object_name = x.object_name
      WHERE rowid = x.rid;
    END LOOP;
END;

関連情報:

バルクSQLの概要は、Oracle Database開発ガイドを参照
バルクSQLをいつ使用するかについての詳細は、Oracle Database開発ガイドを参照
バルクSQLの詳細は『Oracle Database PL/SQL言語リファレンス』を参照してください。

同時実行性およびスケーラビリティについて

同時実行性とは、複数のトランザクションを同時に実行することです。アプリケーションの処理の同時実行性が高くなれば、拡張性も高くなります。スケーラブルなアプリケーションは、システム・リソースの使用量の増加に比例して、より多くのワークロードを処理できます。

同時実行性とは、複数のトランザクションを同時に実行することです。同時実行されるトランザクション内の文は、同じデータを更新できます。アプリケーションの処理の同時実行性が高くなれば、拡張性も高くなります。スケーラブルなアプリケーションは、システム・リソースの使用量の増加に比例して、より多くのワークロードを処理できます。たとえば、ワークロードが倍増した場合、スケーラブルなアプリケーションでは、システム・リソースが2倍使用されます。

同時実行されるトランザクションは、意味のある一貫した結果を作成する必要があります。したがって、マルチユーザーのデータベースは、次を提供する必要があります。

ユーザーの同時データ・アクセスを可能にするデータの同時実行性。
データ整合性: ユーザー自身のトランザクションおよび他のユーザーのコミット・トランザクションによる見える変更を含む、一貫したデータ・ビューを各ユーザーに表示します

Oracle Databaseは、複数バージョン一貫性モデル、様々なロック・タイプ、およびトランザクション分離レベルを使用して、データの一貫性を維持します。Oracle Databaseのロック・メカニズムの概要は、『Oracle Database概要』を参照してください。Oracle Databaseのトランザクション分離レベルの概要については、『Oracle Database概要』を参照してください。

トランザクションの同時実行時に一貫したトランザクション動作を表すために、データベース調査者は、シリアライズ可能と呼ばれるトランザクション分離カテゴリを定義しています。シリアライズ可能トランザクションは、単一ユーザー・データベースである環境で動作します。シリアライズ可能トランザクションは特定の場合に望ましいですが、作業負荷の99%の場合、読取りコミット済分離で十分です。

Oracle Databaseには、同時実行性およびスケーラビリティを向上させる機能(順序、ラッチ、非ブロック読取り/書込み、および共有SQLなど)が用意されています。

データの同時実行性および一貫性の詳細は、『Oracle Database概要』を参照してください。

順序およびスケーラビリティについて

順序を使用すると、シリアライズ化が不要になり、アプリケーションの同時実行性およびスケーラビリティが向上します。

順序は、複数のユーザーが一意の整数を生成する際に使用されるスキーマ・オブジェクトであり、一意の主キーが必要な場合に大変便利です。

順序を使用しない場合、一意の主キー値はプログラム的に作成する必要があります。ユーザーは、最近作成された値を選択し、増分することで、新しい主キー値を取得します。この手法では、トランザクション中にロックが必要であり、複数のユーザーが次の主キー値を待機します(つまり、トランザクションのシリアライズ)。順序を使用すると、シリアライズ化が不要になり、アプリケーションの同時実行性およびスケーラビリティが向上します。

関連情報:

順序への同時アクセスの詳細は、Oracle Database概要を参照してください。
順序の作成および管理

ラッチおよび同時実行性について

ラッチを増やすと、同時実行性に基づく待機が増加するため、スケーラビリティが低下します。

ラッチは、共有データ構造へのマルチユーザー・アクセスを調整する、単純な低レベルのシリアライズ化メカニズムである。ラッチは、共有メモリー・リソースが複数プロセスによってアクセスされるときに破損しないように保護します。

ラッチを増やすと、同時実行性に基づく待機が増加するため、スケーラビリティが低下します。開発中に少し速く実行するアプローチか、より少ないラッチを使用するアプローチのいずれかを使用できる場合は、後者を使用してください。

関連情報:

ラッチの詳細は、『Oracle Database概要』を参照してください。
単一オブジェクトに対するラッチに類似したmutexの詳細は、Oracle Database概要を参照してください。

非ブロック読取り/書込みおよび同時実行性について

Oracle Databaseでは、非ブロック読取り/書込みを使用すると、問合せを同時に実行し、ブロックまたは停止することなく読取り中のデータを変更できます。非ブロック読取り/書込みでは、あるセッションがデータの変更中に、別のセッションがそのデータを読み取ることができます。

共有SQLおよび同時実行性について

Oracle DatabaseがSQL文を実行可能なオブジェクトにコンパイルすると、そのオブジェクトが存在するかぎりそのオブジェクトを他のセッションが再利用できるようになります。共有SQLと呼ばれるこのOracle Database機能を使用すると、リソースに大きな負負担をかける操作であるSQLステートメントのコンパイルおよび最適化を、同じSQLステートメントがセッションで使用されるたびに行ったのではなく、1度実行するだけで済むみます。

参照:共有SQLの詳細は『Oracle Database概要』を参照してください。

同時セッション数の制限

同時セッション数が増えると、同時実行に基づく待機が増加し、レスポンス時間が遅くなります。

使用しているコンピュータにn CPUコアがある場合、実際には最大でnセッションが同時にアクティブになります。追加の同時セッションがそれぞれアクティブになるには、CPUコアが使用可能になるまで待機する必要があります。一部の待機セッションがI/Oのみを待機している場合、同時セッション数がnをわずかに超えて増加すると実行時のパフォーマンスがわずかに向上する場合があります。ただし、同時セッション数が大幅に増加すると、実行時パフォーマンスが著しく低下します。

SESSIONS初期化パラメータは、システム内の同時ユーザーの最大数を決定します。詳細は、『Oracle Databaseリファレンス』に関する項を参照してください。

関連項目: 12 CPUコアのコンピュータで同時セッション数を数千から96に減らす場合の効果のビデオについては、http://www.youtube.com/watch?v=xNDnVOCdvQ0を参照してください。

Runstatsによるプログラミング手法の比較

Runstatsツールを使用すると、2つのプログラミング手法のパフォーマンスを比較して、いずれの手法が優れているかを確認できます。

Runstatsについて

Runstatsツールを使用すると、2つのプログラミング手法のパフォーマンスを比較して、いずれの手法が優れているかを確認できます。

Runstatsは、次の値を測定します。

1/100秒(hsec)単位での各手法の経過時間
1つ目の手法の経過時間を2つ目の手法の経過時間に対するパーセントで表した値
2つの手法のシステム統計(解析コールなど)
2つの手法のラッチ

先行する計測の中で最も重要なことはラッチです(「ラッチおよび同時実行性について」を参照)。

関連項目: Runstatsの使用例を示します例8-1

Runstatsの設定

Runstatsツールは、ビューおよび一時表を使用するパッケージとして実装されます。

ノート:次の手順のステップ1では、動的パフォーマンス・ビューV$STATNAME、V$MYSTATおよびV$LATCHのSELECT権限が必要です。この特権を取得できない場合、この権限を持つユーザーにステップ1のビューの作成、およびその権限に対するSELECT権限の付与を要請してください。

Runstatsツールを設定する手順:

Runstatsで使用されるビューを作成します。

 CREATE OR REPLACE VIEW stats
 AS SELECT 'STAT...' || a.name name, b.value
 FROM V$STATNAME a, V$MYSTAT b
 WHERE a.statistic# = b.statistic#
 UNION ALL
 SELECT 'LATCH.' || name, gets
 FROM V$LATCH;

Runstatsで使用される一時表を作成します。

 DROP TABLE run_stats;

 CREATE GLOBAL TEMPORARY TABLE run_stats
 ( runid VARCHAR2(15),
   name VARCHAR2(80),
   value INT )
 ON COMMIT PRESERVE ROWS;

このパッケージ仕様を作成します。
```
 CREATE OR REPLACE PACKAGE runstats_pkg
 AS
   PROCEDURE rs_start;
   PROCEDURE rs_middle;
   PROCEDURE rs_stop( p_difference_threshold IN NUMBER DEFAULT 0 );
 end;
 /
```
パラメータp_difference_thresholdは、Runstatsにより表示される統計の量およびラッチ・データを管理します。Runstatsでは、2つの手法の差異がp_difference_thresholdよりも大きい場合のみデータが表示されます。デフォルトでは、Runstatsはすべてのデータを表示します。

次のパッケージ本体を作成します。

 CREATE OR REPLACE PACKAGE BODY runstats_pkg
 AS
   g_start NUMBER;
   g_run1 NUMBER;
   g_run2 NUMBER;

   PROCEDURE rs_start
   IS
   BEGIN
     DELETE FROM run_stats;

     INSERT INTO run_stats
     SELECT 'before', stats.* FROM stats;

     g_start := DBMS_UTILITY.GET_TIME;
   END rs_start;

   PROCEDURE rs_middle
   IS
   BEGIN
     g_run1 := (DBMS_UTILITY.GET_TIME - g_start);

     INSERT INTO run_stats
     SELECT 'after 1', stats.* FROM stats;

     g_start := DBMS_UTILITY.GET_TIME;
   END rs_middle;

   PROCEDURE rs_stop( p_difference_threshold IN NUMBER DEFAULT 0 )
   IS
   BEGIN
     g_run2 := (DBMS_UTILITY.GET_TIME - g_start);

     DBMS_OUTPUT.PUT_LINE
       ('Run 1 ran in ' || g_run1 || ' hsec');

     DBMS_OUTPUT.PUT_LINE
       ('Run 2 ran in ' || g_run2 || ' hsec');

     DBMS_OUTPUT.PUT_LINE
       ('Run 1 ran in ' || round(g_run1/g_run2*100, 2) || '% of the time of run 2');

     DBMS_OUTPUT.PUT_LINE( CHR(9) );

     INSERT INTO run_stats
     SELECT 'after 2', stats.* FROM stats;

     DBMS_OUTPUT.PUT_LINE
       ( RPAD( 'Name', 30 ) ||
         LPAD( 'Run 1', 14) ||

         LPAD( 'Run 2', 14) ||

         LPAD( 'Difference', 14)
       );

     FOR x IN
     ( SELECT RPAD( a.name, 30 ) ||
             TO_CHAR( b.value - a.value, '9,999,999,999' ) ||
             TO_CHAR( c.value - b.value, '9,999,999,999' ) ||
             TO_CHAR( ( (c.value - b.value) - (b.value - a.value)),
               '9,999,999,999' ) data
       FROM run_stats a, run_stats b, run_stats c
       WHERE a.name = b.name
         AND b.name = c.name
         AND a.runid = 'before'
         AND b.runid = 'after 1'
         AND c.runid = 'after 2'
         AND (c.value - a.value) > 0
         AND abs((c.value - b.value) - (b.value - a.value)) >
           p_difference_threshold
     ORDER BY ABS((c.value - b.value) - (b.value - a.value))
     ) LOOP
         DBMS_OUTPUT.PUT_LINE( x.data );
     END LOOP;

     DBMS_OUTPUT.PUT_LINE( CHR(9) );

     DBMS_OUTPUT.PUT_LINE(
       'Run 1 latches total compared to run 2 -- difference and percentage' );

     DBMS_OUTPUT.PUT_LINE
       ( LPAD( 'Run 1', 14) ||

         LPAD( 'Run 2', 14) ||

         LPAD( 'Diff', 14) ||

         LPAD( 'Pct', 10)
       );

     FOR x IN
     ( SELECT TO_CHAR( run1, '9,999,999,999' ) ||
             TO_CHAR( run2, '9,999,999,999' ) ||
             TO_CHAR( diff, '9,999,999,999' ) ||
             TO_CHAR( ROUND( g_run1/g_run2*100, 2), '99,999.99' ) || '%' data
       FROM ( SELECT SUM (b.value - a.value) run1,
                     SUM (c.value - b.value) run2,
                     SUM ( (c.value - b.value) - (b.value - a.value)) diff
             FROM run_stats a, run_stats b, run_stats c
             WHERE a.name = b.name
               AND b.name = c.name
               AND a.runid = 'before'
               AND b.runid = 'after 1'
               AND c.runid = 'after 2'
               AND a.name like 'LATCH%'
           )
     ) LOOP
         DBMS_OUTPUT.PUT_LINE( x.data );
     END LOOP;

   END rs_stop;

 END;
 /

関連情報:

「ビューを作成」
表の作成に関する項
チュートリアル: パッケージ仕様部の作成
チュートリアル: パッケージ本体の作成
動的パフォーマンス・ビューについては、『Oracle Databaseリファレンス』を参照してください。

Runstatsの使用

このトピックでは、Runstatsツールを使用するための構文を示します。

Runstatsを使用して2つのプログラミング手法を比較するには、次の構文を使用してrunstats_pkgプロシージャを匿名ブロックから起動します。

[ DECLARE local_declarations ]
BEGIN
  runstats_pkg.rs_start;
  code_for_first_technique
  runstats_pkg.rs_middle;
  code_for_second_technique
  runstats_pkg.rs_stop(n);
END;
/

関連項目: Runstatsの使用例を示します例8-1

Real-World Performanceおよびデータ処理手法

データ・ウェアハウス環境のデータベース・アプリケーションの一般的なタスクは巨大なデータ・セットを問い合せているか変更しています。アプリケーション開発者の課題は、大きなデータ・セットを処理する際に高パフォーマンスを実現する方法です。

処理方法は2つのカテゴリに分類されます: 繰返しおよびセット・ベースです。何年にも渡すテストによって、Real-World Performanceグループはセット・ベース処理の方法の方が、大きなデータ・セットを処理しますデータベース・アプリケーションにおいて、桁違いには優れていることがわかりました。

このトピックには、次の主要なサブトピックが含まれます。

繰返しデータ処理について

繰返し処理では、アプリケーションは条件ロジックを使用して行セット全体を繰り返します。

必須ではありませんが、通常、繰返し処理では次のようにクライアント/サーバー・モデルを使用します。

データベース・サーバーからクライアント・アプリケーションに行のグループを転送します。
クライアント・アプリケーション内でグループの処理。
処理されたグループをデータベース・サーバーに転送して戻します。

行ごとの処理、配列処理および手動並列度の3つの主要な方法を使用して繰返しアルゴリズムを実装できます。

繰返し処理: 行ごと

行ごとの処理で、単一処理はデータ・セット内をループし、一度に1つの行を処理します。典型的な実装では、アプリケーションはデータベースからそれぞれの行を取得し、中間層で処理してから行をデータベースに送って戻し、データベースでDMLが実行されコミットされます。

機能要件が、ext_scan_eventsという外部表を問い合せて、その行をstage1_scan_eventsという名前のヒープ構成表に挿入することだとします。次に示すPL/SQLブロックでは、行ごとの方法を使用して、この要件を満たしています。

declare
  cursor c is select s.* from ext_scan_events s;
  r c%rowtype;
begin
  open c;
  loop
    fetch c into r;
    exit when c%notfound;
    insert into stage1_scan_events d values r;
    commit;
  end loop;
  close c;
end;

行ごとの方法には次のメリットがあります。

小さなデータ・セットでは優れた性能を示します。
ループ・アルゴリズムは、プロの開発者は皆慣れているので、すぐに記述しやすく、理解しやすいものです。

行ごとの方法には次のデメリットがあります。

大きなデータ・セットの処理時間が許容できないほど長くなる場合があります。
アプリケーションはシリアルに実行されます。そのため、最新のハードウェアで稼働しているOracle Databaseのネイティブの並列処理機能を活用できません。

関連項目: RWP #7セット・ベース処理

繰返し処理: 配列

配列処理は、各繰返しに単一の行ではなく行のグループを処理すること以外は行ごとのプロセスと同じです。

機能要件は例X-X:と同じで、ext_scan_eventsという名前の外部表を問い合せて、その行をstage1_scan_eventsという名前のヒープ構成表に挿入することだとします。次のPL/SQLブロックでは、この要件を満たすために配列手法を使用しています。

declare
  cursor c is select s.* from ext_scan_events s;
  type t is table of c%rowtype index by binary_integer;
  a t;
  rows binary_integer := 0;
begin
  open c;
  loop
    fetch c bulk collect into a limit array_size;
    exit when a.count = 0;
    forall i in 1..a.count
      insert into stage1_scan_events d values a(i);
    commit;
  end loop;
  close c;
end;

前述のコードは、FETCH文でのBULK COLLECTオペレータの使用の点で、同等の行ごとのコードとは異なります。これは、PLS_INTEGER型のarray_size値によって制限されています。たとえば、array_sizeが100に設定されている場合、アプリケーションは100行ごとに行をフェッチします。

配列の方法は行ごとの方法に対して次のメリットがあります。

配列では、アプリケーションが一度に行をグループで処理でき、これはクライアントとサーバー間でネットワーク・ラウンドトリップ、COMMIT時間およびコード・パスを削減することを意味します。
サーバー・プロセスは挿入をバッチ処理し、挿入ごとではなく挿入のグループごとにコミットするため、データベースはさらに効率的です。

この方法のデメリットは行ごとの処理と同じです。大きなデータ・セットの処理時間が、許容できないほどになる場合があります。また、アプリケーションは単一CPUコアでシリアルに実行される必要があり、そのためOracle Databaseのネイティブの並列性は活用できません。

繰返し処理: 手動の並列度

手動の並列度は行ごとおよび配列処理と同じ繰返しアルゴリズムを使用しますが、複数のサーバー・プロセスが作業を分割し、並列に実行できるようにします。

行ごとおよび配列の例と同じ機能要件だとします。主な違いを次に示します。

スキャン・イベント・レコードは大量のフラット・ファイルに格納されます。
32サーバー・プロセスは並行して実行する必要があり、それぞれのサーバー・プロセスは別々の外部表を問い合せます。
同じPL/SQLプログラムの32個のスレッドを実行することで、PL/SQLを使用して並列度を実現します。それぞれのスレッドはOracle Schedulerによって管理される別々のジョブとして並行して実行されます。ジョブはスケジュールおよびプログラムの組合せです。

次のPL/SQLコードでは、手動の並列度を使用しています。

declare
  sqlstmt varchar2(1024) := q'[
-- BEGIN embedded anonymous block
  cursor c is select s.* from ext_scan_events_${thr} s;
  type t is table of c%rowtype index by binary_integer;
  a t;
  rows binary_integer := 0;
begin
  for r in (select ext_file_name from ext_scan_events_dets where ora_hash(file_seq_nbr,${thrs}) = ${thr})
  loop
    execute immediate
      'alter table ext_scan_events_${thr} location' || '(' || r.ext_file_name || ')';
    open c;
    loop
      fetch c bulk collect into a limit ${array_size};
      exit when a.count = 0;
      forall i in 1..a.count
        insert into stage1_scan_events d values a(i);
      commit;
--  demo instrumentation
      rows := rows + a.count; if rows > 1e3 then exit when not sd_control.p_progress('loading','userdefined',rows); rows := 0; end if;
    end loop;
    close c;
  end loop;
end;
-- END   embedded anonymous block
]';

begin
  sqlstmt := replace(sqlstmt, '${array_size}', to_char(array_size));
  sqlstmt := replace(sqlstmt, '${thr}', thr);
  sqlstmt := replace(sqlstmt, '${thrs}', thrs);
  execute immediate sqlstmt;
end;

ORA_HASHファンクションはext_scan_events_dets表を32の均等に分散したバケットに分割し、SELECT文はバケット0のファイル名を取得します。バケット内のファイル名ごとに、プログラムによって外部表の場所がこのファイル名に設定されます。次にプログラムではバッチ処理を使用して外部表を問合せ、ステージング表に挿入した後に、コミットします。

ジョブ1が実行されている間、他の31個のOracle Schedulerジョブは並列に実行されます。この方法により、各ジョブは同時にスキャン・イベント・ファイルの別々のサブセットから読取り、サブセットから同じステージング表にレコードを挿入します。

手動の並列度の方法は、他の繰返し方法よりも次の点においてメリットがあります。

サーバー・プロセスが並行して動作するため、大きなデータ・セットで優れたパフォーマンスを実現します。
アプリケーションがORA_HASHを使用してワークロードを分散すると、実行の各スレッドは同じ量のデータにアクセスできます。つまり、並列プロセスは同時に終了できます。

手動の並列度の方法には次のデメリットがあります。

コードが比較的長く、複雑でわかりにくくなります。
並列に行を処理するメインの作業をデータベースが開始する前に一定量の準備作業を実行する必要があります。
データベース・オブジェクトの共通セットで複数のスレッドが同じ操作を実行すると、ロック競合およびラッチ競合が発生する可能性があります。
並行処理は、その他の繰返しの方法と比べて甚大なCPUリソースを消費します。

関連項目: RWP #8: セット・ベース・パラレル処理

セットベース処理について

セット・ベース処理は、データベースの内部でデータ・セットを処理するSQL技術です。

セット・ベース・モデルでは、SQL文が結果を定義し、データベースが結果を取得するための最も効果的な方法を判断します。対照的に、繰返しのアルゴリズムでは、条件ロジックを使用して、データベースからクライアント・アプリケーションに各行または行の各グループをプルし、クライアントでデータを処理してから、データベースにデータを送信して戻します。セット・ベース処理では、データがデータベースから離れないため、ネットワークのランドトリップおよびデータベースAPIのオーバーヘッドがなくなります。

前述の例と同じ機能要件だとします。次のSQL文は、セット・ベース・アルゴリズムを使用してこの要件を満たします。

alter session enable parallel dml;
insert /*+ APPEND */ into stage1_scan_events d
  select s.* from ext_scan_events s;
commit;

INSERT文にext_scan_events表の副問合せがあるため、単一のSQL文ですべての行を読み書きします。また、アプリケーションはデータベースがすべての行を挿入した後、単一のCOMMITを実行します。対照的に、繰返しのアプリケーションでは各行または行の各グループの挿入の後にCOMMITを実行します。

セット・ベースの方法は、繰返しの方法に対して次に示す大きなメリットがあります。

Real-World Performanceデモおよびクラスで示しているように、大きなデータ・セットでのパフォーマンスは桁違いに高速です。プログラムの実行時間が数時間から数秒に縮まることは珍しいことではありません。
処理速度の大幅に向上する副次的な効果として、BAが長時間実行のバッチ・ジョブおよびエラー発生のエラーが起きることができ、ビジネス・プロセスをリアルタイムに刷ニューします。
アクセス・メソッドではなく、SQLが結果を定義するため、コードの長さは著しく短く(2、3行までに)なる。
手動の並列度と対照的に、アプリケーションではなくデータベースがプロセスを管理するため、並列DMLはパフォーマンスが最適化されます。
データ・セットを結合するときには、データベースは相対的に効率の悪いアプリケーション・レベルのループではなく、効率のよいハッシュ結合を自動的に使用します。
APPENDのヒントはダイレクト・パス・ロードを実行し、これはデータベースがREDOおよびUNDOを作成せず、そのため、I/OおよびCPUの無駄が減ることを意味します。

セット・ベース処理には、次のようないくつかの潜在的なデメリットがあります。

この方法はデータベース開発者に馴染みがなく、そのため難しいものとなる場合があります。
セット・ベース・モデルは繰返しモデルとはまったく異なるため、変更することはソース・コードを完全に書き直すこととなります。

関連アイテム: RWP #7セット・ベース処理、RWP #8: セット・ベース処理-- データ重複除外、RWP #9: セット・ベース処理-- データ変換、およびRWP #11: セット・ベース処理-- データ集計