首页 » ORACLE 9i-23ai » about parallel
about parallel
目前机器通常都是多core CPU,单个进程未开并行只会在一个core上运行，未完全发挥多core的特性，有时在统计查询,DML,DDL时需要开启并行提高效率
下面对并行数做个测试
db version 11203
sys@ANBOB> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 40
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 16
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
sys@ANBOB> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 1
anbob@ANBOB> select /*+ parallel 8*/count(*) from obj ;
sys@ANBOB> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P000 IN USE 29 23345 34 63
P001 IN USE 30 23347 40 211
sys@ANBOB> alter system set parallel_threads_per_cpu=5;
System altered.
sys@ANBOB> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1
parallel_threads_per_cpu integer 5
resource_manager_cpu_allocation integer 1
sys@ANBOB> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P002 IN USE 31 23400 24 75
P004 IN USE 33 23404 27 611
P003 IN USE 32 23402 35 51
P000 IN USE 29 23345 36 253
P001 IN USE 30 23347 42 187
anbob@ANBOB> alter table obj parallel 50;
Table altered.
anbob@ANBOB> select count(*) from obj ;
COUNT(*)
----------
145010
sys@ANBOB> show parameter parallel_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 40
sys@ANBOB> alter system set parallel_max_servers=50;
System altered.
sys@ANBOB> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P012 AVAILABLE 41 23721
P040 AVAILABLE 27 23849
P049 AVAILABLE 77 23868
P000 AVAILABLE 29 23697
P047 AVAILABLE 75 23863
P027 AVAILABLE 56 23751
P023 AVAILABLE 52 23743
P005 AVAILABLE 34 23707
P046 AVAILABLE 74 23861
P036 AVAILABLE 65 23770
P044 AVAILABLE 72 23857
P020 AVAILABLE 49 23737
P019 AVAILABLE 48 23735
P033 AVAILABLE 62 23763
P014 AVAILABLE 43 23725
P022 AVAILABLE 51 23741
P035 AVAILABLE 64 23768
P030 AVAILABLE 59 23757
P039 AVAILABLE 71 23813
P002 AVAILABLE 31 23701
P025 AVAILABLE 54 23747
P034 AVAILABLE 63 23766
P042 AVAILABLE 68 23853
P021 AVAILABLE 50 23739
P041 AVAILABLE 28 23851
P016 AVAILABLE 45 23729
P032 AVAILABLE 61 23761
P009 AVAILABLE 38 23715
P029 AVAILABLE 58 23755
P045 AVAILABLE 73 23859
P031 AVAILABLE 60 23759
P024 AVAILABLE 53 23745
P048 AVAILABLE 76 23866
P007 AVAILABLE 36 23711
P011 AVAILABLE 40 23719
P037 AVAILABLE 66 23773
P038 AVAILABLE 69 23811
P028 AVAILABLE 57 23753
P026 AVAILABLE 55 23749
P018 AVAILABLE 47 23733
P003 AVAILABLE 32 23703
P010 AVAILABLE 39 23717
P043 AVAILABLE 70 23855
P006 AVAILABLE 35 23709
P001 AVAILABLE 30 23699
P008 AVAILABLE 37 23713
P004 AVAILABLE 33 23705
P015 AVAILABLE 44 23727
P013 AVAILABLE 42 23723
P017 AVAILABLE 46 23731
50 rows selected.
sys@ANBOB> alter system set parallel_max_servers=40;
System altered.
sys@ANBOB> alter system set parallel_min_percent=80 scope=spfile;
System altered.
sys@ANBOB> startup force
anbob@ANBOB> select count(*) from obj ;
COUNT(*)
----------
145010
sys@ANBOB> select count(*) from v$px_process;
COUNT(*)
----------
40
sys@ANBOB> alter system set parallel_min_percent=81 scope=spfile;
System altered.
sys@ANBOB> startup force
anbob@ANBOB> select count(*) from obj ;
select count(*) from obj
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves (requested 50, available 40, parallel_min_percent 81)
anbob@ANBOB> alter table obj parallel 10;
anbob@ANBOB> alter session force parallel query parallel 15;
anbob@ANBOB> select count(*) from obj ;
COUNT(*)
----------
145010
sys@ANBOB> select count(*) from v$px_process;
COUNT(*)
----------
15
anbob@ANBOB> alter session disable parallel query;
Session altered.
anbob@ANBOB> select /*+ parallel (o 18)*/count(*) from obj o;
COUNT(*)
----------
145010
sys@ANBOB> select count(*) from v$px_process;
COUNT(*)
----------
18
1，指定了paraller但没有指定并行度时默认情况下cpu_count*parallel_threads_per_cpu，前提小于parallel_max_servers
2，当在TABLE或INDEX启用了并行度时，只是允许对象上的并行数并不是实际提供的并行进程的数量，并行大小min(table/index paraller,parallel_max_servers)
3，当parallel_max_servers（40）< table paraller（50）时，并且parallel_min_percent=0时，表示并行度会自动降级。
4，parallel_min_percent 取值范围0-100，是至少要提示百分比的进程数，如表上并行为50，当参数值设为80时，50*80%=40 小于等于parallel_max_servers允许最大的并行进程数才不会抛出ora-12827,所以table/index parallel <=parallel_max_servers*parallel_min_percent
5, parallel_adaptive_multi_user参数也会影响指定并发的数量从10G开始值默认为TRUE,当值是FALSE时如果多并发用户同时启用并行那还是会按装对象上的并行度当达到parallel_max_servers时停止，比如表并行5，15个用户并行执行，parallel_max_servers=50时，最大也是到50个子进程；当值是TRUE，会根据内部算法永远用不到50，并发子进程数会自动降级，建议把值设为FALSE;可以通过v$sysstat视图中name like Parallel operations%查看
6，alter session force parallel query 会覆盖table/index上的并行度
7，alter session disable parallel query,HINT也可以并行，禁用并行设parallel_max_servers=0
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。