DB

Oracle Parallel SQL Tuning 정리

Lawmin 2011. 12. 8. 14:05
1. 관련 파라미터 확인 (특히 Max DOP(degree of parallelism))

  • For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT

  • For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

Oracle RAC 환경의 경우, Oracle RAC services를 사용해서 특별히 node 개수를 한정하지 않는한 위의 공식이 적용된다고 한다.

* 자동 여부
PARALLEL_DEGREE_POLICY : manual, auto, limited 확인
PARALLEL_MIN_TIME_THRESHOLD : plan 상 예측시간 이상시 parallel 수행
PARALLEL_ADAPTIVE_MULTI_USER : Automatic DOP에서 상황에 따라 자동으로 degree 설정

참고: http://docs.oracle.com/cd/E11882_01/server.112/e10837/parallel002.htm 
 
관련 SQL
select * from v$parameter where name like '%cpu%'
or name like '%parallel%';

select (select count(*) from v$px_session) px_cnt,
       (select value from v$parameter where name like 'parallel_max_servers' and rownum = 1) px_max_cnt
from dual;
       

select count(*) from v$session;

select * from v$parameter where name like '%process%';

select dfo_number, tq_id, server_type,  process,
      num_rows, bytes, avg_latency, waits, timeouts
from v$pq_tqstat
order by 1, 2, 3, 4, 5;
  
2. Parallel Degree 지정
 * 테이블에 기본적으로 지정
ALTER TABLE emp parallel (degree default);
* 또는 아래와 같이 SQL에 힌트 적용 (일반적)
/*+ parallel */ /*+ parallel(4) */ /*+ no_parallel */ /*+ parallel(auto) */
 /*+ parallel(manual) */ 

3. Multiblock Read / Partition Wise Range-scan
Multiblock 처리를 위해 full, index_ffs가 기본이나, 파티션 되어있다면 partition-wise parallel range scan (max dop: partition 개수 -> plan 상 PX PARTITION RANGE ALL로 확인 가능) 도 가능하다.
/*+ full(table) */
/*+ index_ffs(table 4) */

4. USE_HASH ? NESTED LOOP ?
일반적으로 nested loop이 아닌 hash join 방식 처리
/*+ use_hash(b) */
partition-wise parallel range scan 인경우
/*+ use_nl(b) */ 

5. 분배방식 지정 (자주 쓰는 힌트 강조)
일반적으로 parallel process간 잘 분배가 되어야 하나 안되는 경우 명시적으로 아래와 같이 지정한다.
/*+ pq_distribute(inner table, outer method, inner method) */
pq_distribute(inner table, none, none) : full partition wise join (join 참여하는 테이블 모두 join column 에 대해 같은 기준 partitioned 된 경우)
pq_distribute(inner table, partition, none) : partial partition wise join (inner쪽 partition key기준으로 outer를 partitioning 하여 join)
pq_distribute(inner table, none, partition) : partial partition wise join (outer쪽 partition key기준으로 inner를 partitioning 하여 join)
pq_distribute(inner table, hash, hash) : join column 에 hash 함수 적용하여 outer build 및 inner probe 하여 join (둘다 대용량인 경우 적용)
pq_distribute(inner table, broadcast, none) : outer table을 broad cast 함 (outer table이 상당히 작은 경우, inner 쪽에 작은 크기의 outer table를 다 뿌려서 join 하는 방식임, 만약 반대로 동작하는 경우 큰 비효율 발생하므로 주의)
pq_distribute(inner table, none, broadcast) : inner table을 broad cast 함 (inner table이 상당히 작은 경우, outer 쪽에 작은 크기의 inner table를 다 뿌려서 join 하는 방식임, 만약 반대로 동작하는 경우 큰 비효율 발생하므로 주의) 

6. 분배방식 힌트를 주었으나 outer join 때문에 driving이 고정되는 경우에 순서 변경이 필요하다면 추가 힌트 적용
/*+ swap_join_inputs(inner table) */ : inner가 outer로 동작함

결국 outer 쪽을 broadcast 하고자 하는 아래 두 힌트는 같다. (가끔 헷갈려서 정리...)
/*+ pq_distribute(inner table, broadcast, none) */ 
/*+ pq_distribute(inner table, none, broadcast) swap_join_inputs(inner table) */
(outer table쪽을 broadcast 해야 하지만, outer join, semi join 등의 이유로 inner table쪽이 선행테이블로 고정이 되어 버리는 경우 swap_join_inputs 까지 사용)

아래 outline 쪽을 유심히 보면 내부적으로 힌트가 추가됨을 알수 있다.
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ leading(t3 t4 t2) use_hash(t1 t2 t4) */ t1.*, t2.*, t3.*, t4.*
  3  FROM t1, t2, t3, t4
  4  WHERE t1.id = t2.t1_id AND t2.id = t3.t2_id AND t3.id = t4.t3_id and t1.n = 19;
 
SQL> SELECT *
  2  FROM table(dbms_xplan.display(format=>'basic parallel outline'));
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 480577400
 
---------------------------------------------------------------------------
| Id  | Operation                 | Name     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |        |      |            |
|   1 |  PX COORDINATOR           |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)     | :TQ10003 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH JOIN              |          |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE            |          |  Q1,03 | PCWP |            |
|   5 |      PX SEND BROADCAST    | :TQ10000 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR   |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL  | T1       |  Q1,00 | PCWP |            |
|   8 |     HASH JOIN             |          |  Q1,03 | PCWP |            |
|   9 |      PX RECEIVE           |          |  Q1,03 | PCWP |            |
|  10 |       PX SEND BROADCAST   | :TQ10001 |  Q1,01 | P->P | BROADCAST  |
|  11 |        PX BLOCK ITERATOR  |          |  Q1,01 | PCWC |            |
|  12 |         TABLE ACCESS FULL | T2       |  Q1,01 | PCWP |            |
|  13 |      HASH JOIN            |          |  Q1,03 | PCWP |            |
|  14 |       PX RECEIVE          |          |  Q1,03 | PCWP |            |
|  15 |        PX SEND BROADCAST  | :TQ10002 |  Q1,02 | P->P | BROADCAST  |
|  16 |         PX BLOCK ITERATOR |          |  Q1,02 | PCWC |            |
|  17 |          TABLE ACCESS FULL| T3       |  Q1,02 | PCWP |            |
|  18 |       PX BLOCK ITERATOR   |          |  Q1,03 | PCWC |            |
|  19 |        TABLE ACCESS FULL  | T4       |  Q1,03 | PCWP |            |
---------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      SWAP_JOIN_INPUTS(@"SEL$1" "T1"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$1" "T2"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "T1"@"SEL$1" NONE BROADCAST)
      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE BROADCAST)
      PQ_DISTRIBUTE(@"SEL$1" "T4"@"SEL$1" BROADCAST NONE)
      USE_HASH(@"SEL$1" "T1"@"SEL$1")
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$1" "T4"@"SEL$1")
      LEADING(@"SEL$1" "T3"@"SEL$1" "T4"@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T4"@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

 
7. Monitoring (parallel 로 어디까지, 잘 돌고 있는지 확인!)
 select decode(px.qcinst_id, NULL, s.username, ' - '||lower(substr(s.program, length(s.program)-4, 4) ) ) "Username",
       decode(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave" ,
       to_char( px.server_set) "Slave Set",
       to_char(s.sid) "SID",
       decode(px.qcinst_id, NULL , to_char(s.sid) , px.qcsid) "QC SID",
       px.req_degree "Requested DOP",
       px.degree "Actual DOP",
       s.status,
       s.sql_id,
       s.row_wait_obj#,
       wo.object_name wait_object_name,
       s.row_wait_file#,
       s.row_wait_block#,
       s.event,
       s.p1,
       s.p2,
       s.p3,
       s.wait_class,
       s.wait_time,
       s.seconds_in_wait,
       s.state,
       slo.target,
       slo.sofar,
       slo.totalwork,
       ROUND(slo.sofar / slo.totalwork * 100, 2) fin_ratio,
       slo.opname,
       slo.units,
       slo.time_remaining,
       slo.elapsed_seconds
from   v$px_session px,
       v$session s,
       V$SESSION_LONGOPS slo,
       dba_objects wo
where  px.sid(+)=s.sid
and    px.serial#(+)=s.serial#
and    s.machine = :machine
and    wo.object_id(+) = s.row_wait_obj#
and    slo.sid(+) = s.sid
and    slo.serial#(+) = s.serial#
--and    px.sid = :sid
order by 5 , 1 desc;

병렬 실행과 관련된 대기 이벤트

  • PX Deq: Parse Reply
    : PEC 가 PES 에게 파싱 요청을 한 후 응답이 올 때까지 대기하는 이벤트 
    10G 에서 도입된 PSC(Parallel Single Cursor) 모델에서는 PEC가 생성한 커서를 공유하기 때문에 이러한 과정은 생략된다. 
    단. RAC 에서는 여전히 PEC 와 다른 노드에 존재하는 PES는 PEC가 생성한 SQL문을 파싱하는 역할을 수행
  • PX Deq: Execute Reply
    : PEC가 가장 보편적으로 대기하는 이벤트, PES의 실제 작업이 끝나기를 기다리는 대기이벤트이다. 
    즉 PEC가 PES가 작업을 끝낸 후 데이터를 보내주기를 기다리는 동안 이 이벤트를 대기 한다.
  • PX Deq Credit : need buffer
    : PEC / PES 간, PES / PES 간의 통신은, 프로세스 간 존재하는 테이블 큐(Table Q)를 통해 이루어진다. 
    가령 PES 가 테이블 큐에 데이터를 집어넣으면, PEC 가 테이블 큐에서 그 데이터를 빼가는 형식이다. 
    오라클은 두 프로세스 중 한 순간에 오직 하나의 프로세스만이 테이블 큐에 데이터를 집어넣을 수 있도록 보장한다. 
    테이블 큐에 데이터를 집어넣을 수 있는 자격을 확보할 때까지 기다리는 이벤트다.
  • PX Deq: Execution Msg
    : PES 에게 가장 보편적인 대기 이벤트, PES 가 어떤 작업을 수행하기 위한 메시지를 기다리는 이벤트 
    병렬 실행에 관계 하는 각 PES들은 특정 작업이 자신에게 할당될 때까지 기다려야 하며, 
    그 동안 PX Deq: Execution Msg 이벤트를 대기한다.
  • PX Deq: Table Q Normal
    : PES 가 테이블 큐에 데이터가 들어오기를 기다리는 이벤트 
    PES 가 다른 PES 로부터 데이터를 받아서 작업을 수행해야 하는 경우에 보편적으로 발생하는 이벤트 
    생산자/소비자(Producer/Consumer) 
    SELECT /*+ PARALLEL ... */ FROM TABLE A ORDER BY NAME 과 같은 형태(정렬작업 필요한)의 
    병렬작업을 수행하면 테이블로부터 데이터를 페치 하는 생산자 PES 와 
    페치된 데이터를 받아서 소비(ORDER BY) 하는 소비자 PES 가 협력하는 방식으로 작동
  • direct path read
    : 버퍼 캐시를 경유하지 않고 데이터 파일로부터 직접 데이터를 읽는 과정에서 발생하는 이벤트 
    PES 가 테이블로부터 데이터를 페치하는 작업은 대부분 데이터 파일에서 직접 데이터를 읽는 방식을 사용한다.
  • enq: TC Contention
    : PES 가 Direct Path I/O를 수행하려면, 해당 테이블에 대한 체크 포인트(Checkpoint)작업이 선행 되어야 한다. 
    버퍼 캐시의 더티 버퍼가 모두 데이타 파일에 기록되어야 버퍼 캐시를 경유하지 않고 데이터 파일에서 
    직접 데이터를 읽을 수 있기 때문이다. 
    PEC는 PES 에게 작업을 지시하기 전에 체크포인트 요청을 하고 작업이 끝날 때 까지 기다려야 하며 
    그 동안 enq: TC Contention 이벤트 대기

'DB'의 다른글

  • 현재글Oracle Parallel SQL Tuning 정리

관련글

티스토리툴바