ablog

不器用で落着きのない技術者のメモ

パラレルDMLが実行されているか確認する方法

  • OSプロセスで確認する。
$ ps -U oracle -f

...

oracle   10921     1  0 19:01 ?        00:00:00 ora_p000_orcl
oracle   10923     1  0 19:01 ?        00:00:00 ora_p001_orcl
oracle   10925     1  0 19:01 ?        00:00:00 ora_p002_orcl
oracle   10927     1  0 19:01 ?        00:00:00 ora_p003_orcl
  • V$PX_PROCESS 表で確認する。
SQL> select * from V$PX_PROCESS

SERV STATUS           PID SPID                SID    SERIAL#
---- --------- ---------- ------------ ---------- ----------
P002 AVAILABLE         33 13188
P000 AVAILABLE         30 13184
P003 AVAILABLE         34 13190
P001 AVAILABLE         31 13186
  • V$PQ_SLAVE 表で確認する。
SQL> select * from V$PQ_SLAVE;

SLAV STAT   SESSIONS IDLE_TIME_CUR BUSY_TIME_CUR CPU_SECS_CUR   ...
---- ---- ---------- ------------- ------------- ------------   ...
P000 IDLE        142             1             0            0   ...
P001 IDLE        142             1             0            0   ...
P002 IDLE        131             1             0            0   ...
P003 IDLE        124             1             0            0   ...
  • 実行計画で確認する。
$ sqlplus / as sysdba
SQL> alter system set sql_trace=true scope=both;
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
user_dump_dest                       string      /opt/app/oracle/admin/orcl/udump

SQL> exit
$ cd /opt/app/oracle/admin/orcl/udump
$ ls -l

...

-rw-r----- 1 oracle oinstall 26362491 Jun  5 18:49 orcl_ora_9127.trc

...

$ tkprof orcl_ora_9127.trc orcl_ora_9127.prf explain=scott/tiger aggregate=no sys=no sort=fchela

$ less orcl_ora_9127.prf

...

ALTER SESSION ENABLE PARALLEL DML


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: 172  (ORCL)
********************************************************************************

DELETE FROM     ...

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    185.25     270.75      89153     939031   10538362      623256
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    185.25     270.75      89153     939031   10538362      623256

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 172  (ORCL)

Rows     Row Source Operation
-------  ---------------------------------------------------
 623256  SEQUENCE  ****_**_******_SEQ (cr=829538 pr=85476 pw=42482 time=233083011 us)
 623256   VIEW  (cr=766327 pr=85476 pw=42482 time=148922778 us)
 623256    SORT GROUP BY (cr=766327 pr=85476 pw=42482 time=147053004 us)
1831088     FILTER  (cr=766327 pr=85476 pw=42482 time=142461453 us)
1888802      PX ***********  (cr=43052 pr=85474 pw=42482 time=145414117 us)
1888802       PX SEND QC (RANDOM) :TQ10003 (cr=43050 pr=85473 pw=42482 time=139747541 us)
1888802        HASH JOIN RIGHT OUTER (cr=43050 pr=85473 pw=42482 time=134081131 us)
     15         BUFFER SORT (cr=21 pr=19 pw=0 time=151225 us)
     15          PX RECEIVE  (cr=21 pr=19 pw=0 time=151215 us)
     15           PX SEND ********* :TQ10000 (cr=21 pr=19 pw=0 time=151179 us)
     15            TABLE ACCESS FULL *******_**** (cr=21 pr=19 pw=0 time=151130 us)
1888802         HASH JOIN RIGHT OUTER (cr=43029 pr=85454 pw=42482 time=124571014 us)
      0          PX RECEIVE  (cr=3 pr=0 pw=0 time=77 us)
      0           PX SEND HASH :TQ10002 (cr=3 pr=0 pw=0 time=72 us)
      0            PX BLOCK ITERATOR (cr=3 pr=0 pw=0 time=64 us)
      0             TABLE ACCESS FULL *******_******* (cr=3 pr=0 pw=0 time=55 us)

「:TQ...」となっているとパラレル実行されている。たぶん。

@yoheia DBMS_MONITOR.DATABASE_TRACE_ENABL()使うことが推奨されております。;)

Hiroshi Sekiguchi  on Twitter: "@yoheia DBMS_MONITOR.DATABASE_TRACE_ENABL()使うことが推奨されております。;)"

って教えて頂きました。