TECH
QUESTION
자주하는 질문답변 입니다.
Oracle
작성자 | 유건데이타 | 등록일 | 2015-07-15 |
제목 | Parallel query option의 사용 | ||
---|---|---|---|
SCOPE
----- 8~10g Standard Edition 은 Parallel Query Option 이 지원하지 않는다. 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 (2) table level, create/alter table 문장에서 정의한다. (3) query level, PARALLEL hint에서 정의한다. Query level의 PARALLEL hint는 table 차원의 정의와 init PARALLEL parameter에 우선한다. 마찬가지로 table 차원에서 정의된 parallelism의 degree가 init 한다. 그러므로, 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 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 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의 사용법은 (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 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 등을 알 수 있다. from otn 데이타베이스 유지보수 전문기업 유건데이타 |
Comment | |||
---|---|---|---|
등록된 코멘트가 없습니다. |