- 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()使うことが推奨されております。;)"
って教えて頂きました。