확장 가능한 애플리케이션 구축

사용자 수 및 데이터 볼륨에 관계없이 시스템 리소스를 과부하하지 않고 동일한 리소스를 사용하도록 애플리케이션을 설계합니다.

확장 가능 애플리케이션 정보

확장 가능 응용 프로그램은 시스템 리소스 사용량이 비례적으로 증가하여 더 큰 작업 로드를 처리할 수 있습니다.

확장 가능 응용 프로그램은 시스템 리소스 사용량이 비례적으로 증가하여 더 큰 작업 로드를 처리할 수 있습니다. 예를 들어 작업 로드를 두 배로 늘리면 확장 가능한 응용 프로그램이 시스템 리소스를 두 배로 사용합니다.

확장 불가능 응용 프로그램은 시스템 리소스를 소진하므로 응용 프로그램 작업 로드를 늘릴 경우 더 이상 처리량을 수행할 수 없습니다. 확장 불가능한 응용 프로그램으로 인해 처리량이 고정되고 응답 시간이 느려집니다.

리소스 소진의 예는 다음과 같습니다.

사용자 수 및 데이터 볼륨에 관계없이 시스템 리소스를 과부하하지 않고 동일한 리소스를 사용하도록 애플리케이션을 설계합니다.

바인드 변수를 사용하여 확장성 향상

올바르게 사용되는 바인드 변수를 사용하여 효율적이고 확장 가능한 응용 프로그램을 개발할 수 있습니다.

바인드 변수는 명령문이 성공적으로 실행되려면 적합한 값 또는 값 주소로 바꿔야 하는 SQL 문의 위치 표시자입니다. 바인드 변수를 사용하면 런타임 시 입력 또는 파라미터를 허용하는 SQL 문을 작성할 수 있습니다.

하위 프로그램에서는 값이 호출자에 의해 제공되는 매개변수를 포함할 수 있는 것처럼 SQL 문은 바인드 변수 위치 표시자를 포함할 수 있습니다. 바인드 변수라고 하는 값은 런타임 시 제공됩니다. 서브 프로그램이 한 번 컴파일된 다음 다른 파라미터를 사용하여 여러 번 실행되는 것과 마찬가지로 바인드 변수 위치 표시자가 있는 SQL 문은 한 번 하드 구문 분석된 다음 다른 바인드 변수를 사용하여 소프트 구문 분석됩니다.

최적화 및 행 소스 생성을 포함하는 하드 구문 분석은 CPU를 많이 사용하는 작업입니다. 소프트 구문 분석은 최적화 및 행 소스 생성을 건너뛰고 곧바로 실행되며 일반적으로 동일한 명령문의 하드 구문 분석보다 훨씬 빠릅니다. (하드 구문 분석과 소프트 구문 분석의 차이를 포함하는 SQL 처리에 대한 개요는 Oracle Database Concepts를 참조하십시오.)

CPU를 많이 사용하는 작업은 하드 구문 분석일 뿐만 아니라 다른 여러 작업과 동시에 수행될 수 없기 때문에 확장 불가능한 작업입니다. 동시성 및 확장성에 대한 자세한 내용은 "동시성 및 확장성 정보"를 참조하십시오.

예 8-1은 바인드 변수가 없는 질의와 바인드 변수가 있는 의미상 동등한 질의 간의 성능 차이를 보여줍니다. 전자는 속도가 느리고 래치가 더 많이 사용됩니다. (래치가 확장성에 미치는 영향에 대한 자세한 내용은 을 참조하십시오.) 이 예에서는 성능 통계를 수집 및 표시하기 위해 "Comparing Programming Techniques with Runstats"에 설명된 Runstats 도구를 사용합니다.

참고:

참고:

소프트 구문 분석은 하드 구문 분석보다 효율적이지만 명령문의 소프트 구문 분석 비용은 여전히 매우 높습니다. 응용 프로그램의 효율성 및 확장성을 최대화하려면 구문 분석을 최소화합니다. 구문 분석을 최소화하는 가장 쉬운 방법은 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 문을 캐시할 수 없습니다.

참조:

OPEN FOR 문 정보

OPEN FOR 문에는 다음과 같은 기본 구문이 있습니다.

OPEN FOR 문의 기본 구문은 다음과 같습니다.

OPEN cursor_variable FOR query

응용 프로그램은 cursor_variable을 닫기 전에 여러 다른 query에 대해 열 수 있습니다. PL/SQL은 런타임까지 다른 query의 수를 결정할 수 없으므로 PL/SQL은 OPEN FOR 문을 캐시할 수 없습니다.

커서 변수를 사용할 필요가 없는 경우 선언된 커서를 사용하면 성능과 프로그래밍 용이성이 향상됩니다. 자세한 내용은 Oracle Database Development Guide를 참조하십시오.

참조:

DBMS_SQL 패키지 정보

DBMS_SQL 패키지는 Dynamic SQL 문을 작성, 실행 및 설명하는 API입니다. 컴파일 시 PL/SQL 컴파일러가 출력 호스트 변수의 수 또는 유형(select list 항목)이나 입력 바인드 변수를 확인할 수 없는 경우 EXECUTE IMMEDIATE 문 대신 DBMS_SQL 패키지를 사용해야 합니다.

DBMS_SQL 패키지는 Dynamic SQL 문을 작성, 실행 및 설명하는 API입니다. DBMS_SQL 패키지를 사용하면 EXECUTE IMMEDIATE 문을 사용하는 것보다 많은 작업이 수행되지만 컴파일 시 PL/SQL 컴파일러에서 출력 호스트 변수 수나 유형(select list 항목) 또는 입력 바인드 변수를 확인할 수 없는 경우 DBMS_SQL 패키지를 사용해야 합니다.

참조:

대량 SQL 정보

대량 SQL은 PL/SQL과 SQL 간의 "왕복" 횟수를 줄여 더 적은 리소스를 사용합니다.

대량 SQL을 사용하지 않으면 데이터베이스(SQL)에서 한 번에 한 행을 검색하여 처리(PL/SQL)한 다음 데이터베이스(SQL)로 반환합니다. 대량 SQL을 사용하면 데이터베이스에서 행 집합을 검색하고, 행 집합을 처리한 다음, 전체 집합을 데이터베이스로 반환합니다.

Oracle recommends using Bulk SQL when you retrieve multiple rows from the database and return them to the database, as in Example 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은 대량 SQL 없이 예제 8-2와 동일한 작업을 수행합니다.

이러한 TKPROF는 Example 8-2 및 Example 8-3에 표시된 것처럼 이 작업에 대량 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;
/

대량 SQL이 없는 예제 8-3

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는 다중 버전 일관성 모델과 다양한 유형의 잠금 및 트랜잭션 격리 수준을 사용하여 데이터 일관성을 유지합니다. Oracle Database 잠금 방식에 대한 개요는 Oracle Database Concepts를 참조하십시오. Oracle Database 트랜잭션 격리 레벨에 대한 개요는 Oracle Database Concepts를 참조하십시오.

트랜잭션이 동시에 실행될 때 일관된 트랜잭션 동작을 설명하기 위해 데이터베이스 연구원은 직렬화 가능이라는 트랜잭션 격리 범주를 정의했습니다. 직렬화 가능한 트랜잭션은 단일 사용자 데이터베이스인 것처럼 보이는 환경에서 작동합니다. 직렬화 가능한 트랜잭션은 특정 경우에 적합하지만 작업 로드의 99%에 대해서는 읽기 커밋된 격리가 완벽합니다.

Oracle Database에는 시퀀스, 래치, 비블로킹 읽기 및 쓰기, 공유 SQL과 같은 동시성 및 확장성을 향상시키는 기능이 있습니다.

참조: 데이터 동시성 및 일관성에 대한 자세한 내용은 Oracle Database Concepts를 참조하십시오.

시퀀스 및 동시성 정보

시퀀스는 직렬화를 제거하므로 응용 프로그램의 동시성과 확장성이 향상됩니다.

시퀀스는 여러 사용자가 고유한 정수를 생성할 수 있는 스키마 객체로, 고유한 기본 키가 필요할 때 매우 유용합니다.

시퀀스가 없으면 고유한 Primary Key 값을 프로그래밍 방식으로 생성해야 합니다. 사용자는 가장 최근에 생성된 값을 선택하고 값을 증분하여 새 기본 키 값을 가져옵니다. 이 기법을 사용하려면 트랜잭션 중에 Lock이 필요하며 여러 유저가 차기 Primary Key 값, 즉 트랜잭션이 직렬화될 때까지 대기합니다. 시퀀스는 직렬화를 제거하므로 응용 프로그램의 동시성과 확장성이 향상됩니다.

참조:

래치 및 동시성 정보

래치가 증가하면 동시성 기반 대기가 늘어나므로 확장성이 떨어집니다.

래치는 공유 데이터 구조에 대한 다중 사용자 액세스를 조정하는 간단한 하위 레벨 직렬화 방식입니다. 래치는 여러 프로세스에서 액세스하는 경우 공유 메모리 리소스가 손상되지 않도록 보호합니다.

래치가 증가하면 동시성 기반 대기가 늘어나므로 확장성이 떨어집니다. 개발 중에 약간 더 빠르게 실행되는 접근 방식이나 더 적은 래치를 사용하는 접근 방식을 사용할 수 있는 경우 둘 중 하나를 사용합니다.

참조:

비블록화 읽기, 쓰기 및 동시성 정보

Oracle Database에서는 비차단 읽기 및 쓰기를 통해 쿼리를 차단하거나 중지하지 않고도 읽고 있는 데이터의 변경 사항을 동시에 실행할 수 있습니다. 비블록화 읽기 및 쓰기를 사용하면 한 세션이 데이터를 읽고 다른 세션이 해당 데이터를 변경할 수 있습니다.

공유 SQL 및 동시성 정보

Oracle Database는 SQL 문을 실행 가능한 객체로 한 번 컴파일하면 다른 세션이 객체를 존재하는 한 재사용할 수 있습니다. 공유 SQL이라고 하는 이 Oracle Database 기능을 통해 데이터베이스는 세션이 동일한 SQL 문을 사용할 때마다가 아니라 리소스를 많이 사용하는 작업(SQL 문 컴파일 및 최적화)을 한 번만 수행할 수 있습니다.

참조: 공유 SQL에 대한 자세한 내용은 Oracle Database Concepts를 참조하십시오.

동시 세션 수 제한

동시 세션이 많을수록 동시성 기반 대기 횟수가 많아지고 응답 시간이 느려집니다.

컴퓨터에 CPU 코어가 n개 있는 경우 실제로 최대 n개 세션이 동시에 활성화될 수 있습니다. 각 추가 "동시" 세션은 활성 상태가 되려면 CPU 코어를 사용할 수 있을 때까지 기다려야 합니다. 일부 대기 세션이 I/O만 대기하는 경우 동시 세션 수를 약간 n보다 크게 늘리면 런타임 성능이 약간 향상될 수 있습니다. 그러나 동시 세션 수를 너무 늘리면 런타임 성능이 크게 저하됩니다.

SESSIONS 초기화 매개변수는 시스템의 최대 동시 사용자 수를 결정합니다. 자세한 내용은 Oracle Database Reference를 참조하십시오.

참조: http://www.youtube.com/watch?v=xNDnVOCdvQ0 - CPU 코어가 12개인 컴퓨터에서 동시 세션 수를 수천 개에서 96개로 줄이는 효과를 보여주는 비디오의 경우

프로그래밍 기법과 Runstats 비교

Runstats 도구를 사용하면 두 프로그래밍 기법의 성능을 비교하여 어느 것이 더 좋은지 확인할 수 있습니다.

Runstats 정보

Runstats 도구를 사용하면 두 프로그래밍 기법의 성능을 비교하여 어느 것이 더 좋은지 확인할 수 있습니다.

Runstats는 다음 값을 측정합니다.

앞의 측정값 중 가장 중요한 것은 래치입니다("래치 및 동시성 정보 참조).

참조: Runstats를 사용하는 Example 8-1

Runstats 설정

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. 이 Package Spec을 생성합니다.

     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는 두 기법에 대한 차이가 p_difference_threshold보다 큰 경우에만 데이터를 표시합니다. 기본적으로 Runstats는 모든 데이터를 표시합니다.

  4. 다음 Package Body를 생성합니다.

     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;
 /

참조:

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를 사용하는 Example 8-1

실제 성능 및 데이터 처리 기법

데이터 웨어하우스 환경에서 데이터베이스 응용 프로그램의 일반적인 작업은 초대용량 데이터 집합을 query하거나 수정하는 것입니다. 응용 프로그램 개발자의 문제는 대용량 데이터 세트를 처리할 때 높은 성능을 달성하는 방법입니다.

처리 기술은 반복 및 셋 기반의 두 가지 범주로 나뉩니다. 수년간 테스트를 거친 Real-World Performance 그룹은 대규모 데이터 세트를 처리하는 데이터베이스 애플리케이션을 위한 세트 기반 프로세싱 기법의 성능이 훨씬 우수하다는 사실을 발견했습니다.

이 항목에는 다음과 같은 주요 하위 항목이 포함되어 있습니다.

반복 데이터 처리 정보

반복 프로세싱에서 응용 프로그램은 조건부 논리를 사용하여 행 집합을 반복합니다.

일반적으로 반복 프로세싱은 다음과 같이 클라이언트/서버 모델을 사용합니다.

  1. 데이터베이스 서버에서 클라이언트 응용 프로그램으로 행 그룹을 전송합니다.

  2. 클라이언트 응용 프로그램 내에서 그룹을 처리합니다.

  3. 처리된 그룹을 다시 데이터베이스 서버로 전송합니다.

행 단위 처리, 배열 처리 및 수동 병렬화의 세 가지 주요 기술을 사용하여 반복 알고리즘을 구현할 수 있습니다.

반복 처리: 행 단위

행 단위 처리에서 단일 프로세스는 데이터 집합을 반복하며 한 번에 한 행씩 실행됩니다. 일반적인 구현에서는 응용 프로그램이 데이터베이스에서 각 행을 검색하여 중간 계층에서 처리한 다음 DML을 실행하고 커밋하는 행을 다시 데이터베이스로 보냅니다.

기능 요구 사항은 ext_scan_events라는 외부 테이블을 query한 다음 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;

행 단위 기법은 다음과 같은 이점이 있습니다.

행 단위 기법에는 다음과 같은 단점이 있습니다.

참조 : RWP #7 세트 기반 처리

반복 처리: 배열

배열 처리는 행 단위 처리와 동일합니다. 단, 행 단위 처리는 행 단위가 아닌 반복 단위로 행 그룹을 처리합니다.

기능 요구 사항이 Example 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개 그룹으로 행을 인출합니다.

배열 기법은 행 단위 기법에 비해 다음과 같은 이점이 있습니다.

이 기법의 단점은 행 단위 처리의 단점과 동일합니다. 대용량 데이터 집합에 대해서는 처리 시간이 허용되지 않을 수 있습니다. 또한 응용 프로그램은 단일 CPU 코어에서 직렬로 실행되어야 하므로 Oracle Database의 고유 병렬화를 활용할 수 없습니다.

반복 처리: 수동 병렬화

수동 병렬화는 행 단위 및 배열 처리와 동일한 반복 알고리즘을 사용하지만 다중 서버 프로세스가 작업을 나누고 병렬로 실행할 수 있도록 합니다.

함수 요구사항이 행 단위 및 배열 예제에서와 동일하다고 가정합니다. 주요 차이점은 다음과 같습니다.

다음 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에 대한 파일 이름을 검색합니다. 버킷의 각 파일 이름에 대해 프로그램은 External Table의 위치를 이 파일 이름으로 설정합니다. 그런 다음 일괄 처리를 사용하여 External Table을 query하고 스테이지 테이블에 삽입한 다음 커밋합니다.

작업 1이 실행되는 동안 다른 31개의 Oracle Scheduler 작업은 병렬로 실행됩니다. 이러한 방식으로 각 작업은 동시에 스캔 이벤트 파일의 다른 하위 집합을 읽고 해당 하위 집합의 레코드를 동일한 스테이지 테이블에 삽입합니다.

수동 병렬화 기법은 대체 반복 기법에 비해 다음과 같은 이점이 있습니다.

수동 병렬화 기법에는 다음과 같은 단점이 있습니다.

참조: 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을 실행합니다.

셋 기반 기법은 반복 기법에 비해 크게 유용합니다.

셋 기반 처리에서는 다음과 같은 몇 가지 단점이 있습니다.

참고: RWP #7 Set-Based Processing, RWP #8: Set-Based Parallel Processing, RWP #9: Set-Based Processing–Data Deduplication, RWP #10: Set-Based Processing–Data Transformations, RWP #11: Set-Based Processing–Data Aggregation