No. 10801
Parallel query option의 사용
----------------------------
parallel query option을 사용하면, 여러 개의 process가 하나의 SQL 문을
나누어 동시에 작업하여 성능 향상에 도움을 줄 수 있다.
여기에서는 parallel query에 대한 다음과 같은 사항을 정리하였다.
(1) parallel query option이 사용되기에 적당한 환경
(2) parallel query processing이 사용되어질 수 있는 SQL 문장의 종류
(3) parallel query 수행을 위한 정의 방법
(4) degree의 의미 및 결정
(5) parallel query processing의 tuning 방법
1. PARALLEL QUERY OPTION이 사용되기에 적당한 환경
(1) 의사 결정 애플리케이션(decision support application)이나 대용량
데이타베이스 환경 등 이러한 환경에서의 data-intensive한 연산의 성능을
향상시킨다.
(2) symmetric multi processor (SMP), clustered system, massively
parallel system, 하나의 시스템에 여러개의 CPU가 있는 경우 query
processing이 효과적으로 각 CPU에 할당되어 처리될 수 있다.
(3) datafile이 여러 개의 disk drive에 나누어져 있는 경우 query의 대상이
되는 데이타들이 하나의 disk에 모여 있다면, 하나의 query를 여러 개의
process로 나누어 동시에 작업한다 하더라도 I/O 측면에서는 효과가 없다.
주의) 현재 사용 중인 시스템의 CPU와 disk controller 사용율이 이미 100%에
가깝다면, parallel query option이 오히려 부하를 증가시킬 뿐이므로 system
resource를 증가시킨 후 parallel query option을 고려하여야 한다.
2. PARALLEL QUERY PROCESSING이 사용되어질 수 있는 SQL 문장의 종류
(1) SELECT 문장
(2) UPDATE, INSERT, DELETE 문 내의 subquery
(3) CREATE TABLE ... AS SELECT 문
(4) CREATE INDEX 문
위와 같은 문장 내에 최소한 하나의 full table scan operation이 포함되어야 query가 parallelize된다.
3. PARALLEL QUERY 수행을 위한 정의 방법
parallel query가 수행되도록 하려면 다음과 같이 3가지 방법으로 정의할 수 있다.
(1) instance level, init<SID>.ora file 내에 정의한다.
(2) table level, create/alter table 문장에서 정의한다.
(3) query level, PARALLEL hint에서 정의한다.
Query level의 PARALLEL hint는 table 차원의 정의와 init<SID>.ora file 내의
PARALLEL parameter에 우선한다. 마찬가지로 table 차원에서 정의된
parallelism의 degree가 init<SID>.ora 화일 내의 PARALLEL parameter에 우선
한다. 그러므로, parallel mode로 수행되는지는 instance나 table level의
check만으로는 부족하다. 또한, query 내에서 PARALLEL hint를 바르게 사용하지
못한 경우 comment로 인식되므로 주의하여야 한다.
주의) instance level이나 table level에 parallel query option을 거는 경우,
table의 정보가 바르게 analyze되지 않았거나, 그 외의 이유로 인하여 예상치 못한
문제를 접하는 경우가 많으므로 가능한 instance나 table level로는 parallel
query option을 setting하지 말고 필요한 query마다 hint를 이용하여 parallel을
지정하는 것이 바람직하다.
(1) instance level 정의
다음과 같은 init<ORACLE_SID>.ora parameter에 의해 정의된다.
parallel_max_servers
- instance 당 max slave
(0는 parallel query option을 사용하지 않음을 의미)
parallel_min_servers
- instance가 startup 당시 최소한 뜨게 되는 slave의 수.
만약 parallel_min_servers를 설정하지 않으면, default는 0이며,
이 파라미터는 parallel query server가 자주 start되고 shutdown되는 경우
일정한 갯수의 query server는 항상 떠있게 한다.
a. 7.3 미만
parallel_default_max_scans
- 하나의 query 수행을 위해 default로 사용되어지는 query server 갯수의
최대값. 이 값은 PARALLEL hint나 table 정의 시 PARALLEL 절이 없는 경우
영향을 미친다.
parallel_default_scansize
- table의 size가 analyze 문으로 인해 추측이 되는 경우 특정 table을 위해
필요한 query server의 갯수를 결정하기 위해 필요한 parameter이다.
table의 블럭 수를 이 값으로 나누면, 그것이 query를 위해 필요한 query
server의 갯수이다. 이렇게 계산된 query server의 갯수는
PARALLEL_DEFAULT_MAXSCANS paramter를 초과할 수 있다.
b. 7.3 이후
parallel_min_percent
- parallel query 수행을 위해 필요한 query slave의 최소 percentage.
parallel_min_percent / 100 개 이상의 query slave가 사용 가능하지 않으면
query는 오류를 발생시키며, 이 값이 설정되어 있지 않으면, 사용 가능한
qeury slave만을 이용하여 parallel query를 수행한다.
이 외에도 parallel_server_idl_time, parallel_default_max_instances,
recovery_parallelism 등의 parameter가 있으나, 이러한 parallel query와
관련한 어떠한 parameter도 parallel query option을 위해 반드시 필요한
것은 아니다.
(2) table level 정의
table은 n degree의 parallelism을 가지도록 create 혹은 alter될 수 있다.
이것은 init<SID>.ora files 내의 PARALLEL_DEFAULT_MAX_SCANS나
PARALLEL_DEFAULT_SCANSIZE가 정의되어 있더라도 이러한 값에 우선한다.
다음과 같이 지정할 수 있다.
CREATE TABLE DEPT
PARALLEL (DEGREE 5)
(DEPTNO NUMBER(2),
DNAME VARCHAR2(20));
(3) Query statement
PARALLEL hint를 사용하면 query에 포함된 table의 degree를 설정할 수 있다.
예를 들어, 다음과 같이 hint를 적어 degree 4로 parallel query option을
실행하도록 할 수 있다.
이 때 parallel이란 글자와 괄호( '(' )사이에 blank를 넣지 않도록 한다.
select /*+ PARALLEL(emp, 4) */ * from emp;
다음과 같이 NOPARALLEL hint를 사용하면, parallel query option을 사용하지
않도록 할 수 있다.
select /*+ NOPARALLEL(emp) */ * from emp;
4. DEGREE의 의미 및 결정
parallel query에서 degree란 하나의 operation 수행에 대한 server
process의 갯수이다. 이러한 degree 결정에 영향을 주는 요인들에는 다음과
같은 것들이 있다.
(1) system의 CPU 갯수
(2) system의 maximum process 갯수
(3) table이 striping되어 있는 경우, 그 table이 걸쳐있는 disk의 갯수
(4) data의 위치 (즉, memory에 cache되어 있는지, disk에 있는지)
(5) query의 형태 (예를 들어 sorts 혹은 full table scan)
한 사용자만이 parallel query를 사용하는 경우, sorting이 많이 필요한
작업과 같은 CPU-bound 작업의 경우는 CPU 갯수의 1 ~ 2배의 degree가 적당하며,
sorting보다는 table scan과 같은 I/O bound 작업의 경우는 disk drive 갯수의
1 ~ 2배가 적당하다.
동시에 수행되는 parallel query가 많은 경우에는 위의 각 사용자의 degree를
줄이거나 동시에 사용하는 사용자 수를 줄여야 한다.
5. PARALLEL QUERY PROCESSING의 TUNING 방법
(1) explain plan
explain plan을 이용하면, object_node와 other column을 이용하여 query가
어떻게 병렬로 처리되는지를 알 수 있다.
분산 환경이 아닌 경우 OBJECT_NODE 컬럼은 operation 수행 결과값이 다음 어느
operation에 사용되어지는지를 나타내며, OTHER 컬럼은 각 query server를
이용하여 어떻게 query가 진행되는지가 기술된다. OTHER 컬럼의 type이 LONG인
관계로 전체 text가 나오지 않을 경우,
$ORACLE_HOME/rdbms/admin/utlxplan.sql file 내에서 OTHER 컬럼의 type을
VARCHAR2로 바꾼 후 plan_table을 다시 만들어 수행하면, 전체 text를 확인할
수 있다.
참고) explain plan의 사용법은 <Oracle7 Server Application Developer's
Guide>나 한국오라클의 bulletin <Explain Plan 사용법>을 참조하도록 한다.
(2) parallel query 문장과 explain plan
SQL 문: SELECT /*+ parallel(dept, 4) */
dname, MAX(sal), AVG(SAL)
FROM dept, emp
WHERE dept.deptno = emp.deptno
GROUP BY dname;
위 문장 수행에 대한 explain plan의 결과는 다음과 같으며, parallel hint에
dept만 parallel query 처리하도록 지정하여 EMP를 full scan하는 부분은
OTHER 컬럼이 null인 것을 알 수 있다. 만약, dept와 emp를 모두 parallel
processing하고자 한다면 hint 지정 시 /*+ parallel(dept, 4, emp, 4) */
와 같이 지정하면 된다.
Query Plan OBJECT_NODE
------------------------------- --------------------------
OTHER
------------------------------------------------------------------
SELECT STATEMENT Cost =6
SORT GROUP BY :Q30003
SELECT /*+ CIV_GB */ A1.C0, AVG(A1.C1), MAX(A1.C2)
FROM :Q30002 A1
GROUP BY A1.C0
HASH JOIN :Q30002
SELECT /*+ PIV_GB */ A1.C0 C0, AVG(A1.C1) C1, MAX(A1.C1) C2
FROM (SELECT /*+ ORDERED NO_EXPAND USE_HASH(A3) */ A2.C1 C0, A3.C1 C1,
A3.C1 C2
FROM :Q30001 A2, :Q30000 A3
WHERE A3.C0=A2.C0) A1
GROUP BY A1.C0
TABLE ACCESS FULL DEPT 2 :Q30001
SELECT /*+ ROWID(A1) */ A1."DEPTNO" C0, A1."DNAME" C1
FROM "DEPT" A1
WHERE ROWID BETWEEN :B1 AND :B2
TABLE ACCESS FULL EMP 1 :Q30000
(3) parallel processing을 이용하지 않은 SQL 문장과 explain plan
SQL 문: SELECT /*+ NOPARALLEL */ dname, MAX(sal), AVG(SAL)
FROM dept, emp
WHERE dept.deptno = emp.deptno
GROUP BY dname;
위의 문장에 대한 explain plan은 다음과 같이 OBJECT_NODE와 OTHER 컬럼에
어떤 값도 들어 있지 않다.
Query Plan OBJECT_NODE OTHER
----------------------------- ---------------------- -----------
SELECT STATEMENT Cost =6
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL DEPT 2
TABLE ACCESS FULL EMP 1
(4) dynamic view
query가 parallel mode로 실행되고 있는 중에 v$pq_sysstat를 조회하면,
parallel query server의 busy, idle 상태 및 server started, server
shtudown된 상태를 알 수 있다.
다음 예와 같이 query하면, 현재 수행되는 parallel query를 위해 사용되어지는
query server의 갯수를 알 수 있다. 이 값을 통해
parallel_min_servers의 값이나 degree의 설정에 참조할 수 있다.
os> sqlplus system/manager
sql> select * from v$pq_sysstat where statistics = 'Servers Busy';
STATISTICS VALUE
--------------- ----------
Servers Busy 3
(5) OS process
OS 상태에서 다음과 같이 수행하면 현재 OS에서 구동되어 있는 parallel query
process를 확인할 수 있다. init<ORACLE_SID>.ora file 내에 parallel_min_servers의 값이 n으로 지정되어 있다면, 다음 명령을 수행하는 순간에 parallel processing에 참여하고 있는 process가 n보다 작더라도, 최소한 n개의 process가 구동되어 있게 된다.
ps -ef | grep p00
oracle 2512 1 0.0 16:03:11 ?? 0:12.09 ora_p001_ORA7
oracle 29162 1 0.0 16:03:11 ?? 0:30.16 ora_p000_ORA7
이 명령을 통하여 현재 구동되어 있는 parallel query process의 갯수와 현재까지 사용한 CPU time 등을 알 수 있다.
출처 : http://database.sarang.net/?inc=read&aid=17307&criteria=oracle&subcrit=&id=&limit=20&keyword=&page=2
