Oracle - 병렬 DML 모니터링 방법

Posted 08 21, 2012 16:14, Filed under: DataBase/Oracle


# 오라클은 병렬 DML을 모니터링할 수 있는 다양한 방법들을 제공하고 있습니다.
  어떤 정보를 확인하려고 하는가에 따라 적절한 방법을 선택할 수 있습니다.
  몇 가지 예를 들어보면 다음과 같습니다.

  * 실행 계획이 정상적으로 병렬 DML 로 나타나는지 확인하려면 DBMS_XPLAN 패키지를 이용하거나 V$SQL_PLAN 뷰를 조회하면 됩니다.
  * 현재 수행 중인 병렬 DML의 상태를 확인하려면 V$PX_SESSION 뷰와 V$PX_PROCESS 뷰를 조회하면 됩니다.
  * 병렬 DML 의 일량이나 대기 이벤트 정보를 확인하려면 SQL*Trace 나 AWR Report 를 확인하면 됩니다.
     또는 V$SESSION_EVENT, V$SESSTAT 뷰 등을 통해서도 실시간으로 확인이 가능합니다.
  * 병렬 DML 수행 후 병렬 수행의 통계 정보를 확인하고 싶으면 V$PQ_TQSTAT 뷰와 V$PQ_SESSTAT 뷰를 조회하면 됩니다.


# 테스트 환경
SQL > create table t1(c1 int, c2 varchar2(100));

Table created.

SQL > insert into t1
            select level, rpad('x',100,'x') from dual
            connect by level <= 10000
        ;

10000 rows created.

SQL > create table t2(c1 int, c2 varchar2(100));

Table created.

SQL > insert into t2
            select level, rpad('x',100,'x') from dual
            connect by level <= 10000
        ;

10000 rows created.

SQL > exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

SQL > exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.


# 병렬 DML과 실행 계획
병렬 DML 은 기본적으로 비활성화(Disabled)되어 있습니다.
아래 실행 계획을 보시면 SELECT 부분은 병렬화되어 있지만, INSERT 부분은 직렬(Serial) 수행하게 됩니다.
LOAD AS SELECT 오퍼레이션이 직렬로 수행된다는 것이 그 증거입니다.
SQL > explain plan for
  2  insert /*+ append parallel(t1 4) */ into t1
  3  select /*+ parallel(t2 4) */ * from t2;

Explained.

SQL > select * from table(dbms_xplan.display);

-- 10g
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |          | 10000 |  1015K|    11   (0)|        |      |            |
|   1 |  LOAD AS SELECT       | T1       |       |       |            |        |      |            |
|   2 |   PX COORDINATOR      |          |       |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 | 10000 |  1015K|    11   (0)|  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          | 10000 |  1015K|    11   (0)|  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T2       | 10000 |  1015K|    11   (0)|  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

-- 11g
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |          | 10000 |  1015K|    11   (0)|        |      |            |
|   1 |  LOAD AS SELECT       | T1       |       |       |            |        |      |            |
|   2 |   PX COORDINATOR      |          |       |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 | 10000 |  1015K|    11   (0)|  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          | 10000 |  1015K|    11   (0)|  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T2       | 10000 |  1015K|    11   (0)|  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------

SQL > commit;

Commit complete.


PARALLEL DML 이 활성화된 후 동일한 방법으로 실행 계획을 조회해보면 INSERT 부분(LOAD AS SELECT)이 병렬로 수행된다는 것을 알 수 있습니다.
-- DML 을 병렬로 실행하려면 PARALLEL DML 속성을 활성화(Enabled) 시켜야 합니다.
SQL > alter session enable parallel dml;

Session altered.

SQL > explain plan for
    insert /*+ append parallel(t1 4) */ into t1
    select /*+ parallel(t2 4) */ * from t2;

Explained.

SQL > select * from table(dbms_xplan.display);

-- 10g
------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          | 10000 |  1015K|    11   (0)|        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 10000 |  1015K|    11   (0)|  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       | T1       |       |       |            |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          | 10000 |  1015K|    11   (0)|  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 | 10000 |  1015K|    11   (0)|  Q1,00 | P->P | RND-ROBIN  |
|   6 |       PX BLOCK ITERATOR |          | 10000 |  1015K|    11   (0)|  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T2       | 10000 |  1015K|    11   (0)|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------

-- 11g
---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |          | 10000 |  1025K|    19   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT     | T1       |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T2       | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

위의 결과를 유심히 보면 한가지 재미있는 점을 발견할 수 있습니다.
Oracle 10g의 경우 두개의 테이블 큐(TQ10000, TQ1001)를 사용하는 반면 Oracle 11g의 경우에는 하나의 테이블 큐(TQ10000)만이 생긴다는 것입니다.
이것은 Oracle 11g에서 병렬 실행 알고리즘이 개선된 것으로 해석할 수 있습니다.
테이블 큐는 병렬 프로세스간의 데이터 통신을 위해 사용되는 구조체인데, 이것의 수가 작다면 그만큼 프로세스간의 통신량이 줄어들 것으로 기대할 수 있기 때문입니다.

위의 예제를 보면 병렬 DML이 정말 병렬로 실행될 것인지의 여부를 반드시 실행 계획으로 확인해봐야 한다는 것을 알 수 있습니다.

또다른 예로, 아래와 같이 테이블 t1 에 Non Unique 인덱스로 구성된 Primary Key 제약 조건을 생성합니다.

SQL > alter table t1
    add constraint t1_pk primary key (c1)
    using index(create index t1_n1 on t1(c1));

Table altered.

/*
    이 상태에서 동일한 병렬 DML(INSERT ...SELECT)의 실행 계획을 보겠습니다.
    우선 Oracle 10g 에서는 INSERT 부분이 병렬이 아닌 직렬로 수행되는 것을 알 수 있습니다.
*/

SQL > explain plan for
    insert /*+ append parallel(t1 4) */ into t1
    select /*+ parallel(t2 4) */ * from t2;

Explained.

SQL > select * from table(dbms_xplan.display);

-- 10g
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |          | 10000 |  1015K|    11   (0)|        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  1015K|    11   (0)|  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  1015K|    11   (0)|  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T2       | 10000 |  1015K|    11   (0)|  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------

/*
    반면에 Oracle 11g에서는 INDEX MAINTENANCE 라는 오퍼레이션이 추가되면서 INSERT 부분도 병렬로 수행됨을 알 수 있습니다.
    단, 이로 인해 테이블 큐의 개수가 늘어났다는 것도 알 수 있습니다.
*/

-- 11g
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          | 10000 |  1025K|    19   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | T1       |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       LOAD AS SELECT     | T1       |       |       |            |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T2       | 10000 |  1025K|    19   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

SCOTT@ORACLE11> alter table t1 drop constraint t1_pk;

Table altered.

SCOTT@ORACLE11> drop index t1_n1;

Index dropped.

-- 이 외에도  병렬 수행에는 많은 제약들이 있습니다.
-- 쿼리 작성시 의도한 대로 병렬 수행이 되는지 반드시 확인해봐야 합니다.


# 병렬 DML과 V$PX_SESSION, V$PX_PROCESS
  병렬 DML 이 어떤 서버 프로세스들에 의해 수행되고 있는지를 간편하게 조회하려면 V$PX_SESSION 뷰와 V$PX_PROCESS 뷰를 이용하면 됩니다.
SQL > alter session enable parallel dml;

SQL > insert /*+ append parallel(t1 4) */ into t1
    select /*+ parallel(t2 4) */ * from t2;

10000 rows created.

SQL > select userenv('sid') from dual;

USERENV('SID')
--------------
           213

SQL > select
  2     c.sid as qcsid,
  3     s.sid as slasid,
  4     p.server_name   
  5  from
  6     v$session c,
  7     v$px_session s,
  8     v$px_process p
  9  where
 10     c.sid = s.qcsid
 11     and c.sid = &SID_OF_SESSION_1
 12     and p.sid = s.sid
 13  ;
Enter value for sid_of_session_1: 213
old  11:        and c.sid = &SID_OF_SESSION_1
new  11:        and c.sid = 213

     QCSID     SLASID SERVER_N
---------- ---------- --------
       213        204 P000
       213        202 P001
       213        206 P002
       213        200 P003
       213        195 P004
       213        205 P005
       213        199 P006
       213        194 P007



# 병렬 DML과 SQL*Trace, AWR Report
   병렬 작업에 대해 SQL*Trace 를 수행하면 그 내용이 프로세스 별로 기록됩니다.
   즉 코디네이트 프로세스(현재 쿼리를 수행하는 세션)와 슬레이브 프로세스들의 트레이스 파일에 각각 정보가 기록됩니다.
   따라서 SQL*Trace 의 결과를 보려면 각각의 트레이스 파일을 따로 조회해야 합니다.

   아래와 같이 SQL*Trace(10046 진단 이벤트)를 레벨 8(대기 이벤트 기록)로 수행하고 TKPROF 리포트를 추출해보면 일량(Logical Reads)이나 대기 이벤트(direct path write 등)가 정확하게 나타나지 않는다는 것을 알 수 있습니다.
   ...... tpack 테스트 필요

   AWR Repoert를 통해서 위의 작업을 모니터링 해보면 direct path write 이벤트나 db file scattered read 이벤트 같은 I/O관련 대기이벤트가 포함된 정보를 볼 수 있습니다.
-- snap_begin.sql 파일의 내용
/*
col begin_snap new_value begin_snap;
col db_id new_value db_id;
col inst_num new_value inst_num;

select dbid as db_id from v$database;
select instance_number as inst_num from v$instance;

select dbms_workload_repository.create_snapshot as begin_snap from dual;
*/

-- snap_end.sql 파일의 내용
/*
col end_snap new_value end_snap;
select dbms_workload_repository.create_snapshot as end_snap from dual;
*/

-- snap_report.sql 파일의 내용
/*
select * from table(
    dbms_workload_repository.awr_report_text(
        &db_id,
        &inst_num,
        &begin_snap,
        &end_snap)
);
*/

-- AWR
SQL > @snap_begin

SQL > insert /*+ append parallel(scott.t1 4) */ into scott.t1
            select /*+ parallel(scott.t2 4) */ * from scott.t2;

SQL > commit;

SQL > @snap_end
SQL > @snap_report


Wait Events                       DB/Inst: ORACLE3/oraSub3  Snaps: 17675-17676
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
control file parallel write              19     .0           0      26       4.8
log file parallel write                  11     .0           0      18       2.8
log file sync                             3     .0           0      26       0.8
db file parallel write                    5     .0           0      12       1.3
control file sequential read            686     .0           0       0     171.5
db file sequential read                  14     .0           0       0       3.5
SQL*Net break/reset to clien              2     .0           0       0       0.5
SQL*Net message to client                 7     .0           0       0       1.8
direct path write                         8     .0           0       0       2.0
virtual circuit status                    2  100.0          59   29297       0.5
jobq slave wait                          19   94.7          56    2929       4.8
SQL*Net message from client               7     .0          55    7918       1.8
Streams AQ: qmn slave idle w              2     .0          55   27344       0.5
Streams AQ: qmn coordinator               4   50.0          55   13672       1.0
          -------------------------------------------------------------

Background Wait Events    DB/Inst: ORACLE3/oraSub3  Snaps: 17675-17676
-> ordered by wait time desc, waits desc (idle events last)

                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
control file parallel write              19     .0           0      26       4.8
log file parallel write                  11     .0           0      18       2.8
db file parallel write                    5     .0           0      12       1.3
rdbms ipc message                       197   95.4       2,212   11231      49.3
pmon timer                               20  100.0          59    2930       5.0
Streams AQ: qmn slave idle w              2     .0          55   27344       0.5
Streams AQ: qmn coordinator               4   50.0          55   13672       1.0
          -------------------------------------------------------------

Operating System Statistics       DB/Inst: ORACLE3/oraSub3  Snaps: 17675-17676

Statistic                                       Total
-------------------------------- --------------------
AVG_BUSY_TIME                                   3,080
AVG_IDLE_TIME                                   2,792
AVG_IOWAIT_TIME                                     0
AVG_SYS_TIME                                    2,499
AVG_USER_TIME                                     580
BUSY_TIME                                       6,163
IDLE_TIME                                       5,586
IOWAIT_TIME                                         0
SYS_TIME                                        5,001
USER_TIME                                       1,162
LOAD                                                1
OS_CPU_WAIT_TIME                                  200
RSRC_MGR_CPU_WAIT_TIME                              0
VM_IN_BYTES                                         0
VM_OUT_BYTES                                        0
PHYSICAL_MEMORY_BYTES                   1,868,386,304
NUM_CPUS                                            2
-------------------------------------------------------------



# 병렬 DML과 V$PQ_TQSTAT
   병렬 DML이 종료된 후 병렬 작업의 실행 상황을 파악하는 좋은 방법 중 하나가 V$PQ_TQSTAT 뷰와 V$PQ_SESSTAT 뷰를 조회하는 것입니다.
   병렬 DML을 수행한 해당 세션에서만 조회할 수 있습니다.

   309페이지 참조.



출처 : 오라클 성능 Q&A 시즌1




※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
08 21, 2012 16:14 08 21, 2012 16:14


Trackback URL : http://develop.sunshiny.co.kr/trackback/811

Leave a comment

« Previous : 1 : 2 : 3 : 4 : 5 : 6 : ... 9 : Next »

Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. Generally I do not read post on bl... 레기읏룸 셔츠룸 차이. 레깅스룸 부엉이 01 24,
  2. Wonderful site. A lot of useful in... /427 01 23,
  3. 안녕하세요^^ 배그핵
  4. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  5. 정말 큰 도움이 되었습니다.. 감사합... 사랑은

Recent Trackbacks

  1. important link important link %M
  2. invoice printing and mailing services invoice printing and mailing services %M
  3. my review here my review here %M
  4. relocation services london relocation services london %M
  5. get redirected here get redirected here 24 01

Calendar

«   01 2020   »
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2823605 HIT
TODAY 470 HIT
YESTERDAY 543 HIT