并行查询执行计划:
SQL> drop table t purge;
Table dropped.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t as select * from dba_objects where rownum < 1000;
Table created.
SQL> create table t1 as select * from dba_objects where rownum < 1000;
Table created.
SQL> set autotrace traceonly;
SQL> set linesize 200
SQL> select /*+ parallel(t 2) parallel(t1 2) */ * from t,t1 where t.object_id=t1.object_id;
999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2519702473
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 403K| 7 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 999 | 403K| 7 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 999 | 403K| 7 (0)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 999 | 201K| 3 (0)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T | 999 | 201K| 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 999 | 201K| 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 999 | 201K| 3 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 999 | 201K| 3 (0)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T1 | 999 | 201K| 3 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
699 recursive calls
3 db block gets
218 consistent gets
27 physical reads
592 redo size
119976 bytes sent via SQL*Net to client
1076 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
999 rows processed
SQL>
并行数据流图:
--EOF--
2.就自己本机的硬件情况，通过SQL示例，来找到最优的并行度。
SQL> drop table t purge;
Table dropped.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 254');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> select /*+ parallel(t 2) */ * from t;
393337 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 393K| 36M| 861 (2)| 00:00:11 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 393K| 36M| 861 (2)| 00:00:11 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 393K| 36M| 861 (2)| 00:00:11 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 393K| 36M| 861 (2)| 00:00:11 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
31 recursive calls
3 db block gets
5926 consistent gets
5539 physical reads
632 redo size
23705110 bytes sent via SQL*Net to client
288792 bytes received via SQL*Net from client
26224 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
393337 rows processed
SQL> select /*+ parallel(t 4) */ * from t;
393337 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 393K| 36M| 431 (2)| 00:00:06 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 393K| 36M| 431 (2)| 00:00:06 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 393K| 36M| 431 (2)| 00:00:06 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 393K| 36M| 431 (2)| 00:00:06 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
37 recursive calls
3 db block gets
6046 consistent gets
5539 physical reads
676 redo size
23701333 bytes sent via SQL*Net to client
288792 bytes received via SQL*Net from client
26224 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
393337 rows processed
SQL> select /*+ parallel(t 8) */ * from t;
393337 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 393K| 36M| 215 (2)| 00:00:03 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 393K| 36M| 215 (2)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 393K| 36M| 215 (2)| 00:00:03 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 393K| 36M| 215 (2)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
38 recursive calls
3 db block gets
6166 consistent gets
5539 physical reads
676 redo size
23702674 bytes sent via SQL*Net to client
288792 bytes received via SQL*Net from client
26224 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
393337 rows processed
SQL> select /*+ parallel(t 16) */ * from t;
393337 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 393K| 36M| 108 (2)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 393K| 36M| 108 (2)| 00:00:02 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 393K| 36M| 108 (2)| 00:00:02 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 393K| 36M| 108 (2)| 00:00:02 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
51 recursive calls
3 db block gets
6556 consistent gets
5539 physical reads
676 redo size
23698067 bytes sent via SQL*Net to client
288792 bytes received via SQL*Net from client
26224 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
393337 rows processed
SQL> select /*+ parallel(t 32) */ * from t;
393337 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 393K| 36M| 54 (2)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 393K| 36M| 54 (2)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 393K| 36M| 54 (2)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 393K| 36M| 54 (2)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
45 recursive calls
3 db block gets
6556 consistent gets
5539 physical reads
676 redo size
23693854 bytes sent via SQL*Net to client
288792 bytes received via SQL*Net from client
26224 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
393337 rows processed
SQL> select /*+ parallel(t 64) */ * from t;
393337 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3050126167
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 393K| 36M| 27 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 393K| 36M| 27 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 393K| 36M| 27 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | 393K| 36M| 27 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
33 recursive calls
3 db block gets
6046 consistent gets
5539 physical reads
676 redo size
23701998 bytes sent via SQL*Net to client
288792 bytes received via SQL*Net from client
26224 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
393337 rows processed
SQL>
数据库运行在一台双核CPU的台式机上.
由于Oracle不知道系统的繁忙程度，对并行Cost的计算只是简单按照并行度增加一倍而减少一半，所以不是特别具有参考价值。单从Time来看, 当并行度达到32时, Time都不再减少, 所以在该机器上且只运行该单一SQL的情况下并行度设为32最优.
但是根据如下对并行的系统统计信息来看, 当并行度达到16时就并行操作已经降低了25%~50%(Parallel operations downgraded 25 to 50 pct), 个人理解是当并行度达到16时, 磁盘IO已经达到极限了, 再加上并行进程之间的开销所以反而出现了downgrade.
SQL> select name, value from v$sysstat where name like 'Parallel%';
NAME VALUE
---------------------------------------------------------------- ----------
Parallel operations not downgraded 3
Parallel operations downgraded to serial 0
Parallel operations downgraded 75 to 99 pct 6
Parallel operations downgraded 50 to 75 pct 1
Parallel operations downgraded 25 to 50 pct 2
Parallel operations downgraded 1 to 25 pct 0
6 rows selected.
SQL>
--EOF--
3.针对PARALLEL_DEGREE_POLICY的三个值，分别演示它们的效果。
SQL> create table t as select * from all_objects;
Table created.
SQL> alter table t parallel;
Table altered.
SQL> create table t8 as select * from all_objects;
Table created.
SQL> alter table t8 parallel 8;
Table altered.
SQL> select table_name,degree from user_tables where table_name in('T','T8');
TABLE_NAME DEGREE
------------------------------ ----------------------------------------
T DEFAULT
T8 8
SQL> alter session set parallel_degree_policy=manual;
Session altered.
SQL> select count(*)from t;
COUNT(*)
----------
55633
SQL> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 2 0
SQL> select count(*) from t8;
COUNT(*)
----------
55634
SQL> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 8 0
SQL> alter session set parallel_degree_policy=limited;
Session altered.
SQL> select count(*)from t;
COUNT(*)
----------
55633
SQL> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 0 0
SQL> select count(*) from t8;
COUNT(*)
----------
55634
SQL> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 8 0
SQL> alter session set parallel_degree_policy=auto;
Session altered.
SQL> select count(*)from t;
COUNT(*)
----------
55633
SQL> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 0 0
SQL> select count(*) from t8;
COUNT(*)
----------
55634
SQL> select * from v$pq_sesstat where statistic='Allocation Height';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Allocation Height 0 0
SQL>
--EOF--
4.用一个并行的SQL示例，比较10391事件和 V$PQ_TQSTAT结果的异同。
SQL> alter session set tracefile_identifier='pxtest1';
Session altered.
SQL> ALTER SESSION SET "_px_trace"="compilation","execution","messaging";
Session altered.
SQL> alter session set events '10391 trace name context forever,level 128';
Session altered.
SQL> select /*+ parallel(t 4) */ count(*) from t;
COUNT(*)
----------
1573348
SQL> alter session set events '10391 trace name context off';
Session altered.
SQL> select * from v$pq_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE
---------- ---------- ---------------------------------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------------------------------------- ----------
1 0 Producer 1 36 0 0 10 1 P003 1
1 0 Producer 1 36 0 0 11 0 P002 1
1 0 Producer 1 36 0 0 1 1 P001 1
1 0 Producer 1 36 0 0 8 0 P000 1
1 0 Consumer 4 144 0 0 57 6 QC 1
SQL>
[oracle@localhost trace]$ ls *pxtest1*
RCLSH_ora_8270_pxtest1.trc RCLSH_p000_7926_pxtest1.trm RCLSH_p002_7930_pxtest1.trc RCLSH_p003_8314_pxtest1.trm
RCLSH_ora_8270_pxtest1.trm RCLSH_p001_7928_pxtest1.trc RCLSH_p002_7930_pxtest1.trm
RCLSH_p000_7926_pxtest1.trc RCLSH_p001_7928_pxtest1.trm RCLSH_p003_8314_pxtest1.trc
[oracle@localhost trace]$ view RCLSH_ora_8270_pxtest1.trc
...
*** ACTION NAME:() 2013-11-27 19:09:31.478
kkfdapdml
pgadep:0 pdml mode:0 PQ allowed DML allowed => not allowed
select /*+ parallel(t 4) */ count(*) from t
kkfdmpdml oct=3, pdml_allowed=0
kkfdmpdml cursor pdml_mode=0
kkfdmpdml session txn mode 0
kkfdmpdml cursor txn mode 0
kkfdmpdml cursor and session envs match
kxfrSysInfo
DOP trace -- compute default DOP from system info
# instance alive = 1 (kxfrsnins)
kxfrDefaultDOP
DOP Trace -- compute default DOP
# CPU = 2
Threads/CPU = 2 ("parallel_threads_per_cpu")
default DOP = 4 (# CPU * Threads/CPU)
default DOP = 4 (DOP * # instance)
Default DOP = 4
kxfrSysInfo
system default DOP = 4 (from kxfrDefaultDOP())
kxfrDmpSys
dumping system information
arch:255 ((unknown)
sess:384 myiid:1 mynid:1 ninst:1 maxiid:1
Instances running on that system:
inum:0 iid:1
kxfralo
DOP trace -- requested thread from best ref obj = 4 (from kxfrIsBestRef(
))
kxfralo
threads requested = 4 (from kxfrComputeThread())
kxfralo
adjusted no. threads = 4 (from kxfrAdjustDOP())
kxfrialo
Start: allocating requested 4 slaves
kxfrAllocSlaves
DOP trace -- call kxfpgsg to get 4 slaves
kxfpgsg
num server requested = 4
kxfpiinfo
inst[cpus:mxslv]
1[2:3600]
kxfpclinfo
inst(load:user:pct:fact)aff
1(13:0:100:15)
kxfpAdaptDOP
Requested=4 Granted=4 Target=16 Load=13 Default=4 users=0 sets=1
kxfpgsg
load adapt num servers requested to = 4 (from kxfpAdaptDOP())
kxfpgsg
getting 1 sets of 4 threads, client parallel query execution flg=0x30
Height=4, Affinity List Size=0, inst_total=1, coord=1
Insts 1
Threads 4
kxfpg1srv
trying to get slave P000 on instance 1
kxfpg1sg
Got It. 1 so far.
kxfpg1srv
trying to get slave P001 on instance 1
kxfpg1sg
Got It. 2 so far.
kxfpg1srv
trying to get slave P002 on instance 1
kxfpg1sg
Got It. 3 so far.
kxfpg1srv
trying to get slave P003 on instance 1
kxfpg1sg
Got It. 4 so far.
kxfpg1sg
got 4 servers (sync), returning...
GROUP GET
Acquired 4 slaves in 1 set q serial 21505
P000 inst 1 spid 7926
P001 inst 1 spid 7928
P002 inst 1 spid 7930
P003 inst 1 spid 8314
kxfpgsg
got 4 threads on 1 instance(s), avg height=4
Insts 1
Svrs 4
kxfxpnd
i: 0, cnt: 4
kxfxpnd
calling kxfxmdmp
enqueueing:
MSG( -->, KXFXOparse, STREAM_HINT )
kxfxmh at addr: 0xa6ff7ef8
kxfxmh->kxfxmhh.kxfmhtyp: KXFMHFSX
kxfxmh->kxfxmhmsz: 1912
kxfxmh->kxfxmhflg: 0x5
kxfxmh->kxfxmhotyp: , KXFXOparse
kxfxmp->kxfxmppxid: 1
kxfxmp->kxfxmptqidb: 668003
kxfxmp->kxfxmpssno: 1
kxfxmp->kxfxmpsno: 0
kxfxmp->kxfxmpnslv: 4
kxfxmp->kxfxmpntqd: 1
kxfxmp->kxfxmpnpqd: 4
kxfxmp->kxfxmpnss: 0
kxfxmp->kxfxmpndfo: 1
kxfxmp->kxfxmpsqllen: 44
kxfxmp->kxfxmpgvsqllen: 0
kxfxmp->kxfxmpoct: 3
kxfxmp->kxfxmpflg: TOP/NO PEEK/NO TRANS/CVM ON/PDML OFF/KKOISRECUR
OFF
kxfxmp->kxfxmpqcxid: xid: 0x0000.000.00000000
kxfxmp->kxfxmpbuf: 0xa6ff8090
kxfxpnd
i: 1, cnt: 4
kxfxpnd
calling kxfxmdmp
enqueueing:
MSG( -->, KXFXOparse, STREAM_HINT )
kxfxmh at addr: 0xa6ff00e0
kxfxmh->kxfxmhh.kxfmhtyp: KXFMHFSX
kxfxmh->kxfxmhmsz: 1912
...
可以看出v$pq_tqstat和10391事件都可以显示出并行度，并行进程等信息。
不过，v$pq_tqstat更多地给出了与数据库相关的信息，如NUM_ROWS，WAITS 等；
而10391事件则更多地展现了与操作系统相关的信息，如CPU核数（# CPU = 2），并行进程的进程id（P000 inst 1 spid 7926）。
总的来说10391事件给出的信息更详细。
参考：
http://searchitchannel.techtarget.com/feature/Parallel-processing-Using-parallel-SQL-effectively
--EOF--
DDL的并行示例
SQL> select count(*) from t;
COUNT(*)
----------
1573348
SQL> select bytes/1024/1024 MB from user_segments where segment_name = 'T';
MB
----------
174.125
SQL> set timing on
SQL> create table t1 as select * from t;
Table created.
Elapsed: 00:00:05.86
SQL> create table t2 as select /*+ parallel(t 2) */ * from t;
Table created.
Elapsed: 00:00:07.31
SQL> create table t4 as select /*+ parallel(t 4) */ * from t;
Table created.
Elapsed: 00:00:08.79
SQL>
DML的并行示例
SQL> update t1 set object_name=object_name ;1573348 rows updated.
Elapsed: 00:02:03.44
SQL> rollback;
Rollback complete.
Elapsed: 00:00:45.20
SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.05
SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.05
SQL> update /*+ parallel(t 2) */ t2 set object_name=object_name ;
1573348 rows updated.
Elapsed: 00:01:55.58
SQL> rollback;
Rollback complete.
Elapsed: 00:00:51.89
SQL> update /*+ parallel(t 4) */ t4 set object_name=object_name ;
1573348 rows updated.
Elapsed: 00:03:22.03
SQL> rollback;
Rollback complete.
Elapsed: 00:01:50.10
SQL>
从上可以看出，无论是并行DDL还是DML都不一定比不并行优。
--EOF--
555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
