首页 » ORACLE » 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

打赏

对不起,这篇文章暂时关闭评论。