효과적인 병렬처리 방법

인덱스로 수행되는 쿼리는 일반적으로 병렬화할 수 없지만 파티션 테이블에 대한 쿼리가 로컬 파티션 인덱스를 기반으로 수행되는 경우 인덱스 스캔을 병렬로 수행할 수 있습니다.
제대로 튜닝되지 않은 SQL을 병렬화하면 실행 시간이 줄어들 수 있습니다. 그러나 데이터베이스 서버에 대한 해당 SQL의 영향을 확대하고 다른 세션에 미치는 영향도 증가하게 됩니다. 더 많은 데이터베이스 서버 리소스에 대한 액세스 권한을 부여하기 전에 SQL이 효율적인지 확인해야 합니다. SQL 병렬화는 SQL 튜닝의 대안이 아님.

2 병렬처리 정도 (DOP) 결정[편집]

우수한 병렬 성능을 위해서는 최적의 DOP가 중요
병렬 실행이 표시되거나 요청되었지만 DOP가 지정되지 않은 경우 기본 DOP는 시스템 CPU 코어 수의 두 배로 설정.
RAC 시스템의 경우 DOP는 전체 클러스터 코어 수의 두 배가 됩니다. 이 기본값은 구성 매개변수 PARALLEL_THREADS_PER_CPU에 의해 제어됩니다.
Oracle 11g 릴리스 2부터 PARALLEL_DEGREE_POLICY가 AUTO로 설정된 경우 Oracle은 수행할 작업의 특성과 관련된 개체의 크기에 따라 DOP를 조정합니다.
PARALLEL_ADAPTIVE_MULTI_USER가 TRUE로 설정된 경우 Oracle은 시스템의 전체 로드를 기반으로 DOP를 조정합니다. 시스템이 더 많이 로드되면 DOP가 감소합니다.
Oracle 11g 이상에서 PARALLEL_IO_CAP가 TRUE로 설정되면 Oracle은 IO 하위 시스템이 지원할 수 있는 DOP로 제한합니다. 이러한 IO 하위 시스템 제한은 DBMS_RESOURCE_MANAGER.CALIBRATE_IO 프로시저를 사용하여 계산할 수 있습니다.
CREATE TABLE, CREATE INDEX, ALTER TABLE 또는 ALTER INDEX의 PARALLEL 절을 사용하여 테이블 또는 인덱스 수준에서 DOP를 지정할 수 있습니다.
PARALLEL 힌트는 쿼리 내의 특정 테이블에 대한 DOP를 지정하는 데 사용할 수 있습니다.
다른 설정과 관계없이 DOP는 PARALLEL_MAX_SERVERS에서 지원할 수 있는 값을 초과할 수 없습니다. 대부분의 SQL 문에서 필요한 서버 수는 요청된 DOP의 두 배입니다.
DOP를 최적점 이상으로 높이면 더 이상 성능이 향상되지 않습니다. 그러나 최적 이상으로 DOP를 높이면 전체 시스템 성능에 상당한 부정적인 영향을 미칠 수 있습니다.
병렬화되는 SQL은 DOP가 증가함에 따라 크게 저하되지 않을 수 있지만 시스템의 부하가 계속 증가하고 동시에 실행되는 다른 SQL의 응답 시간이 감소할 수 있습니다.
DOP를 최적 수준 이상으로 높이면 호스트에 과부하가 걸리고 다른 SQL의 성능이 저하될 수 있습니다.

2.1 병렬 힌트[편집]

PARALLEL 힌트는 병렬 처리를 호출할 수 있습니다. 가장 간단한 형태의 힌트는 다음 예제와 같이 인수를 사용하지 않습니다.

-- 기본 
SELECT /*+ parallel */ * 
  FROM sh.sales s
-- 테이블 알리아스 지정 
SELECT /*+ parallel(s) */ * 
  FROM sh.sales s
-- 테이블에 병렬도 지정 
SELECT /*+ parallel(s,8) */ * 
  FROM sh.sales s;

NOPARALLEL 힌트를 사용하여 병렬 처리 억제

SELECT /*+ noparallel */ COUNT ( * )
  FROM sales;

11g r2에서 AUTO 옵션을 사용하면 PARALLEL_DEGREE_POLICY에 대한 AUTO 설정을 사용하여 DOP를 계산하도록 요청할 수 있습니다.

SELECT /*+ parallel(auto) */ COUNT ( * )     
  FROM sales;

임시 쿼리 실행의 경우 명시적 DOP를 설정할 수 있습니다. 그러나 애플리케이션 내에 포함된 SQL의 경우 SQL이 머신 구성(예: 더 많은 CPU), 워크로드(더 많은 동시 세션) 또는 구성(예: 병렬 슬레이브 수 또는 기본 DOP). Embedded SQL의 경우 명시적 DOP를 생략하거나 AUTO 키워드를 사용하는 것이 좋습니다(Oracle 11g 릴리스 2 이상).

2.2 병렬 구성 매개변수[편집]

특히 동시 시스템 활동을 고려할 때 최적의 DOP를 결정하는 것은 어려운 작업입니다.
처리를 수동으로 구성하기 전에 Oracle의 자동화를 시도해야 합니다.
그럼에도 불구하고 상당한 조정이 가능. 병렬 SQL을 최적화하기 위해 조정할 수 있는 중요한 구성 매개변수를 나열합니다.
1. parallel_adaptive_multi_user

TRUE로 설정하면 Oracle은 시스템의 로드를 고려하여 DOP를 조정합니다. 로드가 많은 시스템에서 Oracle은 요청된 수준 또는 기본 수준에서 DOP를 줄입니다.

1. parallel_degree_limit

Oracle11g Release 2 이상에서는 달성할 수 있는 DOP에 절대 제한을 둡니다. CPU 값은 DOP가 parallel_threads_per_cpu에서 지정한 값을 초과하지 않도록 합니다. IO 값은 최대값을 DBMS_RESOURCE_MANAGER.CALIBRATE_IO를 실행하여 결정된 IO 제한으로 설정합니다. AUTO를 사용하면 Oracle이 값을 선택할 수 있습니다. 특정 DOP에 해당하는 정수 값을 지정할 수도 있습니다.

1. parallel_degree_policy

11G 릴리스 2 이상에서 이 매개변수는 DOP가 계산되는 방법을 제어합니다. MANUAL은 11.1 및 이전 버전의 동작과 동일합니다. AUTO인 경우 SQL 문의 작업 유형과 테이블 크기에 따라 DOP가 계산됩니다. 또한 AUTO는 병렬 쿼리가 직접 경로 IO를 사용하지 않고 버퍼 캐시에서 데이터를 가져올 수 있도록 하며 요청된 DOP 실행을 즉시 사용할 수 없는 경우 병렬 프로세스를 대기열에 넣습니다.

1. parallel_execution_message_size

병렬 처리에 관련된 프로세스 간의 통신을 위한 버퍼 크기를 설정합니다.

1. parallel_force_local

Oracle 11g 릴리스 2부터 이 매개변수를 TRUE로 설정하면 RAC 클러스터에서 다중 인스턴스 병렬 처리를 억제합니다.

1. parallel_io_cap_enabled

매개변수를 TRUE로 설정하면 Oracle이 생각하는 IO 하위 시스템이 지원할 수 있는 DOP로 DOP가 제한됩니다. 매개변수를 사용하려면 먼저 DBMS_RESOURCE_MANAGER.CALIBRATE_IO를 사용하여 이러한 IO 제한을 결정해야 합니다.

1. parallel_max_servers

최대 병렬 서버 수입니다. 이는 실행할 수 있는 동시 병렬 작업의 양에 대한 절대적인 제한을 제공합니다.

1. parallel_min_percent

0이 아닌 값으로 설정하면 이 매개변수는 쿼리에 허용되는 최소 DOP를 결정합니다. 요청되거나 결정된 DOP가 시스템 부하 또는 병렬 서버 풀을 사용하는 다른 병렬 프로세스로 인해 제공될 수 없는 경우 DOP는 PARALLEL_MIN_PERCENT 값으로만 감소됩니다. 예를 들어 쿼리에서 8의 DOP를 요청했는데 5개만 사용할 수 있는 경우(5 / 8 = 62%) PARALLEL_ MIN_PERCENT가 62 미만이면 쿼리가 병렬로 실행됩니다. PARALLEL_MIN_ PERCENT가 62를 초과하면 명령문이 종료되거나 종료됩니다. 오류가 있거나 PARALLEL_DEGREE_ POLICY가 AUTO로 설정된 경우 나중에 실행하기 위해 대기합니다.

1. parallel_min_servers

최소 병렬 서버 수 - 데이터베이스가 처음 시작될 때 초기화되는 수입니다.

1. parallel_min_time_threshold

SQL 문이 자동으로 병렬화되는데 필요한 경과 시간(초)을 지정합니다. SQL 문의 예상 경과 시간이 임계값을 초과하면 Oracle은 자동으로 SQL을 병렬화합니다. AUTO의 기본값은 Oracle에서 자동으로 값을 계산합니다.

1. parallel_threads_per_cpu

CPU당 적용할 수 있는 병렬 스레드 수를 설정합니다. Oracle은 일반적으로 이 제한을 초과하지 않도록 DOP를 제한합니다.

2.3 병렬 SQL 모니터링[편집]

병렬 실행에는 여러 프로세스가 관련되기 때문에 병렬 실행을 설명, 추적 및 모니터링하는 것이 직렬 SQL보다 복잡할 수 있습니다.

2.3.1 병렬 설명 계획[편집]

EXPLAIN PLAN은 병렬 실행과 관련된 추가 병렬 작업을 반영하는 병렬화된 SQL 문에 대한 추가 단계를 반영.

SQL> EXPLAIN PLAN FOR
 SELECT * FROM customers 
 ORDER BY cust_last_name;

-------------------
Id	Operation	Name
0	SELECT STATEMENT	 
1	SORT ORDER BY	 
2	TABLE ACCESS FULL	CUSTOMERS

CUSTOMERS 테이블이 스캔되고 스캔된 행이 정렬됩니다. 명령문이 병렬화되면 실행 계획에 추가 작업이 추가됩니다.

SQL> EXPLAIN PLAN FOR
 SELECT /*+ parallel */ *
 FROM customers
 ORDER BY cust_last_name;

SELECT * FROM table (DBMS_XPLAN.display(null,null,'BASIC +PARALLEL'));

Id	Operation	Name	TQ	IN-OUT	PQ Distrib
0	SELECT STATEMENT	 	 	 	 
1	PX COORDINATOR	 	 	 	 
2	PX SEND QC (ORDER)	:TQ10001	Q1,01	P->S	QC (ORDER)
3	SORT ORDER BY	 	Q1,01	PCWP	 
4	PX RECEIVE	 	Q1,01	PCWP	 
5	PX SEND RANGE	:TQ10000	Q1,00	P->P	RANGE
6	PX BLOCK ITERATOR	 	Q1,00	PCWC	 
7	TABLE ACCESS FULL	CUSTOMERS	Q1,00	PCWP

PX BLOCK ITERATOR :
1. PX 블록 반복자 , 이 작업은 일반적으로 병렬 파이프라인의 첫 번째 단계입니다.
2. BLOCK ITERATOR는 관련된 각 병렬 서버에서 처리되는 청크로 테이블을 나눕니다.
PX SEND
1. PX 보내기
2. PX SEND 작업은 단순히 데이터가 한 병렬 프로세스에서 다른 프로세스로 전송되고 있음을 나타냅니다.
PX RECEIVE
1. PX 수신
2. PX RECEIVE 작업은 하나의 병렬 프로세스가 다른 프로세스로부터 수신하는 데이터를 나타냅니다.
PX SEND QC
1. PX 전송 QC
2. 병렬 쿼리 조정자 프로세스에 대한 전송 작업.
PX COORDINATOR
1. PX 코디네이터
2. 이 단계는 단순히 병렬 쿼리 조정자가 병렬 스트림에서 데이터를 수신하여 SQL 문으로 반환한다는 것을 나타냅니다.

그림 13-6은 이러한 단계가 DOP가 2인 병렬 처리와 어떻게 관련되는지 보여줍니다.

정렬 작업에서 일반적으로 PX SEND 및 PX RECEIVE 작업은 배포 옵션(DBMS_XPLAN의 "PQ Distrib" 열에 표시됨)과 연결되어 데이터가 한 슬레이브에서 다른 슬레이브로 전송되는 방식을 설명합니다.

RANGE 옵션은 정렬할 행이 정렬 열의 값을 기준으로 분산되기 때문.
예를 들어 이전 쿼리에서와 같이 CUST_FIRST_NAME별로 정렬할 때 Oracle은 A–K에서 한 슬레이브로 이름을 보내고 L–Z에서 다른 슬레이브로 이름을 보낼 수 있습니다.
일반적으로 발생하는 배포 옵션은 다음과 같습니다.
RANGE : 행은 값 범위에 따라 분산됩니다. 이는 정렬 작업이 병렬화될 때 일반적입니다.
HASH : 관련 값의 해시를 기반으로 병렬 쿼리 슬레이브에 행이 배포됩니다. 이는 조인 및 HASH GROUP BY 작업에 적합하며 일반적으로 RANGE 작업보다 더 균일한 행 분포를 보장합니다.
RANDOM : 무작위의 행은 병렬 쿼리 슬레이브에 무작위로 할당됩니다.
ROUND ROBIN : 라운드 로빈행은 포커 게임에서 카드를 처리하는 것처럼 원형 방식으로 한 번에 하나씩 배포됩니다.

DBMS_XPLAN에서 IN-OUT 열은 병렬 프로세스 간에 데이터가 흐르는 방식을 설명합니다. 열은 PLAN_TABLE 테이블의 OTHER_TAG 열에 해당합니다. 이러한 열은 아래 표시된 값 중 하나를 포함할 수 있습니다.
PLAN_TABLE 또는 DBMS_XPLAN 출력에 PARALLEL_FROM_SERIAL 또는 S->P 태그가 있으면 다른 병렬 실행 스트림에서 직렬 병목 현상을 나타낼 수 있습니다.

2.3.2 병렬 실행 추적[편집]

SQL 추적을 사용하여 쿼리를 조정하면 SQL이 병렬화될 때 다소 어려워집니다.
병렬 실행에 관련된 각 프로세스에는 고유한 추적 파일이 있기 때문입니다.
또한 이러한 프로세스는 병렬화된 모든 SQL 및 세션 간에 공유되기 때문에 추적 파일에는 우리가 관심 있는 것 외에도 다른 SQL 및 세션에 대한 추적 데이터가 포함됩니다.

1. [IN-OUT VALUE] [OTHER_TAG VALUE] [ 설명 ]

P->P PARALLEL_TO_PARALLEL 이 태그는 결과를 두 번째 병렬 프로세스 세트로 전달하는 병렬 처리를 나타냅니다. 예를 들어, 병렬 테이블 스캔은 결과를 병렬 정렬로 전달했을 수 있습니다.

P->S PARALLEL_TO_SERIAL 이것은 일반적으로 병렬 쿼리의 최상위 수준. 결과는 쿼리 코디네이터에 병렬로 제공.

P->S PCWC PARALLEL_COMBINED_PCWC WITH_PARENT

                 PARALLEL_COMBINED_WITH_CHILD	상위 단계 또는 하위 단계도 동일한 프로세스에 의해 병렬로 실행. 예를 들어, 병렬 중첩 루프 조인에서 병렬 쿼리 프로세스는 구동 테이블을 스캔하고 조인된 테이블에서 인덱스 조회도 실행.

S->P PARALLEL_FROM_SERIAL 일련의 병렬 프로세스에 결과를 전달하는 직렬 작업. 이 태그가 있으면 병렬 처리가 직렬 처리에서 대기할 수 있음을 나타내므로 병렬 문 내의 직렬 병목 현상을 나타낼 수 있습니다.

그러나 다소 복잡한 프로세스를 통해 병렬 실행을 추적하는 것은 가능하다 . 단계는 다음과 같습니다.

DBMS_SESSION.SET_ IDENTIFIER를 사용하여 세션에서 고유한 클라이언트 식별자를 설정.
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE을 사용하여 해당 클라이언트 식별자에 대한 추적을 활성화.
병렬 SQL을 실행.
trcsess 유틸리티를 사용 하여 클라이언트 ID에 대한 추적 항목만 포함하는 새 추적 파일 작성.
새 추적 파일을 분석.

1, 2, 3단계 수행

BEGIN
  DBMS_SESSION.set_identifier ('gh pqo test 27');
  DBMS_MONITOR.client_id_trace_enable
   (client_id => 'gh pqo test 27',
    waits => TRUE);
END;
/

4,5단계 수행

$ trcsess clientid='gh pqo test 27' output=pqo_test_27.trc *
$ tkprof pqo_test_27.trc pqo_test_27.prf sort='(prsela,fchela,exeela)'

병합된 추적 파일은 이제 호출 세션의 활동뿐만 아니라 쿼리 실행과 관련된 모든 병렬 서버 프로세스의 활동도 정확하게 반영.
병렬 실행을 추적하려면 클라이언트 식별자를 설정하고 trcsess 유틸리티를 사용하여 해당 클라이언트 식별자에 대한 추적 레코드를 단일 파일로 추출.
병렬 서버 활동의 상세한 추적은 "_px_trace" 기능을 사용하여 수행할 수도 있습니다. 예를 들어

ALTER SESSION SET "_px_trace"="compilation","execution","messaging";

10391 이벤트를 사용하여 병렬 서버 할당에 대한 정보를 덤프할 수도 있습니다.

ALTER SESSION SET EVENTS '10391 trace name context forever, level 128';

이 두 가지 이벤트는 다소 비밀스럽고 때로는 방대한 출력을 생성하며 다른 모든 기술이 병렬 실행을 밝히지 못하는 경우에만 사용해야 합니다.

2.4 V$PQ_TQSTAT 뷰[편집]

EXPLAIN PLAN 및 SQL 트레이스 출력을 사용하더라도 병렬 쿼리가 실행되는 방식을 정확하게 파악하기는 어렵습니다.
1. 예를 들어, 실제 DOP는 몇개 인가? 각 병렬 서버 프로세스가 수행한 작업의 양은 얼마입니까?
V$PQ_TQSTAT 뷰에는 전송 및 수신된 행 수를 포함하여 각 병렬 쿼리 서버 집합 간에 전송된 데이터에 대한 정보 포함.
1. 불행하게도 뷰는 병렬 쿼리를 실행한 세션 내에서만 볼 수 있으며 가장 최근에 실행된 쿼리에 대해서만 볼 수 있음.

SELECT dfo_number
          , tq_id
          , server_Type
          , MIN (num_rows)
          , MAX (num_rows),count(*) dop
       FROM v$pq_tqstat
      GROUP BY dfo_number, tq_id, server_Type
      ORDER BY dfo_number, tq_id, server_type DESC;

select tq_id, server_type
     , process, num_rows
     , bytes, waits 
  from v$pg_tqstat
 order by dfo_number
     , tq_id
     , decode (substr (server_type, 1, 4), 'Rang', 1, 'Prod', 2, 'Cons', 3)
     , process;

복잡한 병렬 SQL의 경우 DFO_NUMBER 열에 대해 서로 다른 값으로 표시되는 여러 병렬 파이프라인이 있을 수 있습니다.
V$PQ_TQSTAT 뷰를 사용하여 병렬 서버 간에 전송되는 실제 DOP 및 데이터 양을 측정.

2.5 기타 통계[편집]

V$PX_SESSION 뷰로 현재 어떤 병렬 슬레이브 프로세스가 SQL을 실행하고 있는지 실시간으로 볼 수 있습니다.
V$PX_SESSION을 V$SESSION 및 V$SQL에 조인하여 현재 병렬 처리를 사용하는 세션 및 SQL을 식별하여 원하는 실제 DOP를 볼 수 있음.

SQL> WITH px_session AS (SELECT qcsid, qcserial#, MAX (degree) degree,
2    MAX (req_degree) req_degree,
3    COUNT ( * ) no_of_processes
4   FROM v$px_session p
5  GROUP BY qcsid, qcserial#)
6 SELECT s.sid, s.username, degree, req_degree, no_of_processes,
7  sql_text
8 FROM v$session s JOIN px_session p
9   ON (s.sid = p.qcsid AND s.serial# = p.qcserial#)
10  JOIN v$sql sql
11   ON (sql.sql_id = s.sql_id
12   AND sql.child_number = s.sql_child_number)
13  /

SID USERNAME DEGREE REQ_DEGREE NO_OF_PROCESSES
---------- -------- ---------- ---------- ---------------
SQL_TEXT
---------------------------------------------------------
144 OPSG 18 18 36
select /*+ parallel(sa,18) */ prod_id,sum(quantity_sold)
, sum(amount_sold) from sales_archive sa group by prod
_id order by 3 desc

V$SYSSTAT에는 요청된 DOP에서 병렬 쿼리가 다운그레이드되는 빈도를 확인하는 데 도움이 되는 병렬 쿼리 다운그레이드와 관련된 일부 통계가 포함되어 있습니다.

SQL> SELECT name,value, round(value*100/sum(value) over(),2) pct
2 FROM v$sysstat
3 WHERE name LIKE 'Parallel operations%downgraded%';

NAME	VALUE	PCT
Parallel operations not downgraded	109	93.97
Parallel operations downgraded to serial	0	0
Parallel operations downgraded 75 to 99 pct	0	0
Parallel operations downgraded 50 to 75 pct	3	2.59
Parallel operations downgraded 25 to 50 pct	2	1.72
Parallel operations downgraded 1 to 25 pct	2	1.72