建立可擴展的應用程式

設計應用程式使用相同的資源，無論使用者群體和資料量為何，而不是超載系統資源。

關於可擴展的應用程式

可擴展應用程式可處理較大的工作負載，並按比例增加系統資源使用量。

可擴展應用程式可處理較大的工作負載，並按比例增加系統資源使用量。例如，如果您將工作負載翻倍，可擴展的應用程式就會使用兩倍以上的系統資源。

無法擴展應用程式會耗盡系統資源；因此，如果您增加應用程式工作負載，則無法再使用其他處理量。不可擴展的應用程式會導致固定的輸送量和回應時間不佳。

資源耗盡範例包括：

• 硬體耗盡

• 表格掃描大量交易造成不可避免的磁碟輸入 / 輸出 (I/O) 短缺

• 造成網路及排程瓶頸的過度網路請求

• 記憶體配置造成分頁和交換

• 過多的處理作業與繫線配置導致作業系統超載

設計應用程式使用相同的資源，無論使用者群體和資料量為何，而不是超載系統資源。

使用連結變數來改善擴展性

連結變數，正確使用，讓您開發高效、可擴展的應用程式。

連結變數是 SQL 敘述句中的預留位置，必須以有效值或值位址取代，敘述句才能順利執行。透過使用連結變數，您可以撰寫一個 SQL 敘述句，以在程式實際執行時接受輸入或參數。

就像子程式可以有參數，其值由呼叫者提供一樣，SQL 陳述式也可以有繫結變數預留位置，其值 (稱為連結變數) 會在執行時期提供。就像編譯一次子程式，然後使用不同的參數執行多次一樣，具有連結變數預留位置的 SQL 敘述句會硬剖析一次，然後以不同的連結變數進行軟剖析。

硬剖析 (包括最佳化和產生資料列來源) 是非常耗用大量 CPU 的作業。軟剖析會略過最佳化和資料列來源產生並直接執行，通常比相同敘述句的硬剖析快得多。(如需包含硬剖析與軟剖析之間差異的 SQL 處理總覽，請參閱 Oracle Database Concepts。)

不僅是耗用大量 CPU 的作業，而且是無法擴展的作業，因為與其他許多作業無法並行完成。如需並行性和擴展性的詳細資訊，請參閱關於並行性和擴展性。

範例 8-1 顯示不含連結變數的查詢與含連結變數的語意等效查詢之間的效能差異。前者較慢，並使用更多閂鎖 (如需閂鎖如何影響擴展性的資訊，請參閱〈 〉)。若要收集和顯示效能統計資料，此範例會使用 Runstats 工具 (如 Comparing Programming Techniques with Runstats 中所述)。

注意：

• 範例 8-1 顯示單一使用者的效能成本。隨著使用者數量增加，成本也會快速上升。

• Example 8-1 的結果是以下列設定值產生：

  SET SERVEROUTPUT ON FORMAT TRUNCATED
  

注意：

• 使用連結變數而非字串文字，是讓您的程式碼難以攻擊 SQL 資料隱碼的最有效方式。如需詳細資訊，請參閱 Oracle Database PL/SQL Language Reference。

• 連結變數有時可以降低資料倉儲系統的效率。因為大多數查詢會花費很長的時間，所以最佳化處理程式會嘗試為各個查詢產生最佳計畫，而不是最佳的一般查詢。使用連結變數有時會強制最佳化處理程式產生最佳的一般查詢。如需有關提升資料倉儲系統效能的資訊，請參閱 Oracle Database Data Warehousing Guide。

雖然軟剖析比硬剖析更有效率，但軟剖析陳述式多次的成本仍然非常高。為了最大化應用程式的效率和可擴展性，請盡量減少剖析。將剖析最小化最簡單的方式是使用 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 敘述句時 sql_statement 都不同，PL/SQL 便無法快取 EXECUTE IMMEDIATE 敘述句。

另請參閱：

• Oracle Database PL/SQL Language Reference，瞭解 EXECUTE IMMEDIATE 的相關資訊

• 關於 DBMS_SQL 套裝程式

關於 OPEN FOR 陳述式

OPEN FOR 陳述式具有下列基本語法。

OPEN FOR 陳述式的基本語法為：

OPEN cursor_variable FOR query

您的應用程式可以在關閉前，針對數個不同的查詢開啟 cursor_variable。因為在程式實際執行之前，PL/SQL 無法判斷不同查詢的數目，所以 PL/SQL 無法快取 OPEN FOR 敘述句。

如果您不需要使用游標變數，請使用宣告游標，以取得較佳的效能與程式設計簡易性。如需詳細資訊，請參閱 Oracle Database Development Guide。

另請參閱：

• Oracle Database PL/SQL Language Reference，瞭解 OPEN FOR 的相關資訊

• 「關於游標變數」

• 「關於游標」

關於 DBMS_SQL 套裝程式

DBMS_SQL 套裝程式是用來建立、執行以及描述動態 SQL 敘述句的 API。如果 PL/SQL 編譯器無法在編譯時判斷輸出主機變數 (選取清單項目) 或輸入連結變數的數目或類型，則必須使用 DBMS_SQL 套裝程式而非 EXECUTE IMMEDIATE 敘述句。

DBMS_SQL 套裝程式是用來建立、執行以及描述動態 SQL 敘述句的 API。使用 DBMS_SQL 套裝程式比使用 EXECUTE IMMEDIATE 敘述句來得多，但是如果 PL/SQL 編譯器無法在編譯時判斷輸出主機變數 (選取清單項目) 或輸入連結變數的數目或類型，就必須使用 DBMS_SQL 套裝程式。

另請參閱：

• Oracle Database PL/SQL Language Reference，瞭解何時使用 DBMS_SQL 套裝程式的詳細資訊

• Oracle Database PL/SQL Packages and Types Reference，瞭解 DBMS_SQL 套裝程式的完整資訊

• 關於 EXECUTE IMMEDIATE 敘述句

關於大量 SQL

大量 SQL 可減少 PL/SQL 與 SQL 之間的「往返」次數，因此使用較少的資源。

如果沒有大量 SQL，您可以從資料庫 (SQL) 一次擷取一列、處理它 (PL/SQL)，然後將它傳回資料庫 (SQL)。使用大量 SQL 時，您可以從資料庫擷取一組列，處理列集合，然後將整個集合傳回資料庫。

Oracle 建議您在從資料庫擷取多個資料列時使用「大量 SQL」，並將它們傳回資料庫，如範例 8-2 所示。如果您擷取多個資料列，但未傳回它們，就不需要大量 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 會執行與範例 8-2 相同的工作，而不需要大量 SQL。

顯示範例 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 時，會使用比使用單一 SQL 敘述句更多的 CPU 時間和程式碼，因為此 TKPROF 報表顯示：

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;

另請參閱：

• Oracle Database Development Guide，瞭解大量 SQL 的簡介

• Oracle Database Development Guide，瞭解何時使用大量 SQL 的詳細資訊

• Oracle Database PL/SQL Language Reference，瞭解有關大量 SQL 的詳細資訊

關於並行性與擴展性

並行是多個交易的同時執行。您的應用程式處理並行越好，擴展性就越高。可擴展應用程式可處理較大的工作負載，並按比例增加系統資源使用量。

並行是多個交易的同時執行。並行交易中的對帳單可以更新相同的資料。您的應用程式處理並行越好，擴展性就越高。可擴展應用程式可處理較大的工作負載，並按比例增加系統資源使用量。例如，如果您將工作負載翻倍，可擴展的應用程式就會使用兩倍以上的系統資源。

並行交易必須產生有意義且一致的結果。因此，多重使用者資料庫必須提供下列項目：

• 資料並行可確保使用者可以同時存取資料。

• 資料一致性：確保每位使用者都能看到一致的資料檢視，包括其自身交易的可見變更，以及其他使用者的已確認交易

Oracle Database 透過使用多轉移一致性模型和各種類型的鎖定和交易隔離層級來維持資料一致性。如需 Oracle Database 鎖定機制的簡介，請參閱 Oracle Database Concepts。如需 Oracle Database 交易隔離層次的簡介，請參閱 Oracle Database Concepts。

為了描述並行執行交易時的一致交易行為，資料庫研究人員定義了稱為可序列化的交易隔離類別。可序列化的交易會在看來是單一使用者資料庫的環境中作業。在特定情況下，序列化交易是理想的，但對於 99% 的工作負載，讀取確認的隔離效果十分完美。

Oracle Database 的功能可以改善並行性和擴展性，例如序列、閂鎖、非阻隔讀取和寫入，以及共用 SQL。

另請參閱：Oracle Database Concepts，瞭解有關資料並行和一致性的詳細資訊

關於序列和並行

序列可消除序列化，從而提高應用程式的並行性和可擴展性。

順序是一個綱要物件，可供多位使用者產生唯一的整數，這在您需要唯一的主索引鍵時非常有用。

若無順序，則必須以程式設計方式產生唯一主索引鍵值。使用者可透過選取最近產生的值並遞增該值來取得新的主索引鍵值。此技術在交易期間需要鎖定，而且會讓多位使用者等待下一個主索引鍵值，亦即交易會序列化。序列可消除序列化，從而提高應用程式的並行性和可擴展性。

另請參閱：

• Oracle Database Concepts，瞭解序列並行存取的相關資訊

• 「建立及管理序列」

關於閂鎖和並行

閂鎖增加表示有更多的並行等待，因此可擴展性減少。

閂鎖是一種簡單、低層次的序列化機制，可以協調共用資料結構的多使用者存取。閂鎖可以保護共用記憶體資源，使其在多個處理作業進行存取時免於損毀。

閂鎖增加表示有更多的並行等待，因此可擴展性減少。如果您可以使用在開發期間稍微快執行的方法，或是使用較少閂鎖的方法，請使用後者。

另請參閱：

• Oracle Database Concepts，瞭解閂鎖的相關資訊

• Oracle Database Concepts，瞭解關於多邊形的資訊，這些資訊就像是單一物件的閂鎖

關於非封鎖抄表值與寫入並行

在 Oracle Database 中，非封鎖讀取和寫入可讓查詢對讀取的資料進行變更，而不會封鎖或停止。非阻隔讀取和寫入可以在另一個階段作業變更該資料時，讓一個階段作業讀取資料。

關於共用 SQL 和並行

Oracle Database 會將 SQL 敘述句編譯為可執行物件一次，然後其他階段作業就可以重複使用該物件，只要該物件存在即可。此 Oracle Database 功能稱為共用 SQL ，可讓資料庫只執行一次極耗資源的作業編譯和最佳化 SQL 敘述句，而不必每次階段作業使用相同的 SQL 敘述句。

另請參閱：Oracle Database Concepts，瞭解共用 SQL 的詳細資訊

限制並行階段作業數目

您的並行階段作業越多、您的等待時間越多、回應時間越慢。

如果您的電腦擁有 n 個 CPU 核心，則最多 n 個階段作業實際上可以並行使用。每個額外的「並行」階段作業都必須等待一個 CPU 核心可使用，才能變成作用中。如果某些等待中的階段作業只等待 I/O，則將並行階段作業的數目增加到稍微超過 n ，可能會稍微改善程式實際執行效能。不過，增加並行階段作業的數目過多將會大幅降低執行階段效能。

SESSIONS 初始化參數決定系統中的並行使用者數目上限。如需詳細資訊，請參閱 Oracle Database Reference。

另請參閱：http://www.youtube.com/watch?v=xNDnVOCdvQ0 影片顯示將 12 個 CPU 核心 (從數千個核心減少至 96 個) 之電腦上的並行階段作業數目所造成的影響

比較程式設計技術與 Runstats

[Runstats] (執行狀態) 工具可讓您比較兩種程式設計技術的效能，以查看哪一種比較好。

關於 Runstats

[Runstats] (執行狀態) 工具可讓您比較兩種程式設計技術的效能，以查看哪一種比較好。

Runstats 會測量下列值：

• 每種技術的經歷時間 (以百秒為單位)

• 第一個技術的經歷時間佔第二個技術的百分比

• 兩種技術的系統統計資料 (例如剖析呼叫)

• 為兩種技術閂鎖

在先前的測量中，最重要的是閂鎖 (請參閱關於閂鎖和並行 )。

另請參閱：使用 Runstats 的範例 8-1

設定執行狀態

Runstats 工具會以使用檢視和暫時表格的套裝軟體方式實作。

注意：對於下列程序的步驟 1 ，您需要動態效能視觀表 V$STATNAME、V$MYSTAT 以及 V$LATCH 的 SELECT 權限。如果您無法取得此權限，請讓具備權限的人員在步驟 1 中建立檢視，然後將該檢視的 SELECT 權限授與您。

設定 Runstats 工具的步驟：

1. 建立 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;
   

2. 建立 Runstats 使用的暫存表：

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

3. 建立此套裝程式規格：

    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 顯示的統計資料與閂鎖資料量。只有在兩個技術的差異大於 p_difference_threshold 時，Runstats 才會顯示資料。依預設，「執行狀態」會顯示所有資料。

4. 建立下列套裝程式主體：

    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 Reference，瞭解動態效能視觀表的相關資訊

使用 Runstats

本主題提供使用 Runstats 工具的語法。

若要使用 Runstats 來比較兩種程式設計技術，請使用下列語法從匿名區塊呼叫 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 團隊發現，針對處理大型資料集的資料庫應用程式，設定式處理技術表現出優越的幅度。

本主題包含下列主要子主題：

關於反覆資料處理

在反覆處理中，應用程式會使用條件邏輯，對一組列執行迴圈。

一般而言，反覆處理會使用用戶端 / 伺服器模型，如下所示：

1. 將一組資料列從資料庫伺服器傳輸至從屬端應用程式。

2. 處理用戶端應用程式內的群組。

3. 將已處理的群組傳輸回資料庫伺服器。

您可以使用三種主要技術來實作反覆演算法：逐列處理、陣列處理，以及手動平行處理。

反覆處理：逐列

在逐列處理中，單一處理會循環整個資料集，並一次在單一資料列上作業。在典型的實行中，應用程式會從資料庫擷取每一列，在中間層處理該列，然後將該列傳回資料庫，然後執行 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 STATEMENT 中 BULK COLLECT 運算子的對等列代碼不同，其受限於類型為 PLS_INTEGER 的 array_size 值。例如，如果 array_size 設為 100，則應用程式會擷取 100 群組中的資料列。

陣列技術對於逐列技術具有下列優點：

• 陣列可以讓應用程式同時處理一組資料列，亦即可以減少網路往返時間、COMMIT 時間，以及用戶端與伺服器中的程式碼路徑。

• 資料庫更有效率，因為伺服器處理作業會批次進行插入，並在每組插入之後 (而非每次插入之後) 確認。

此技術的缺點與逐列處理的缺點相同。無法接受大型資料集的處理時間。此外，應用程式必須以序列方式在單一 CPU 核心上執行，因此也無法利用 Oracle Database 的原生平行程度。

反覆處理：手動平行

手動平行處理使用與逐列和陣列處理相同的反覆演算法，但可讓多個伺服器處理分割工作並平行執行。

假設功能需求與資料列個別資料列和陣列範例中的功能需求相同。主要差異如下：

• 掃瞄事件記錄會儲存在整批的純文字檔中。

• 32 個伺服器處理作業必須以平行方式執行，而每個伺服器處理作業都查詢不同的外部表格。

• 您可以使用 PL/SQL 來執行相同 PL/SQL 程式的 32 個繫線，每個繫線都會以 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 示範和課程所示，大型資料集的效能提高了幾個數量。程式執行時間從數小時退到數秒鐘不尋常。

• 處理速度大幅提升訂單的副作用是，DBA 可以消除長時間執行且容易出錯的批次工作，並即時避免業務流程。

• 程式碼的長度明顯縮短 (短為兩行或三行程式碼)，因為 SQL 定義了結果而非存取方法。

• 與手動平行程度相比，平行 DML 已經針對效能最佳化，因為資料庫 (而非應用程式) 負責管理處理作業。

• 結合資料集時，資料庫會自動使用高效率雜湊結合，而不是相對效率不佳的應用程式層次迴圈。

• APPEND 提示會強制執行直接路徑載入，這表示資料庫不會建立重做和還原，因此可避免浪費 I/O 和 CPU。

設定式處理有一些潛在的缺點：

• 這些技術不熟悉許多資料庫開發人員，因此可能較為困難。

• 由於以集合為基礎的模型與反覆模型完全不同，因此變更它需要完全重寫原始程式碼。

另請參閱：RWP #7 設定式處理、RWP #8：設定式平行處理、RWP #9：設定式處理–資料去除重複、RWP #10：設定式處理–資料轉換，以及 RWP #11：設定式處理–資料聚總