oracleparameterparallel_max实验

oracleparameterparallel_max实验

parameter parallel_max是oracle最大可开启的并行服务进程,所有会话的并行总和。

SQL> show parameter parallel_max_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 135

SQL> alter system set parallel_max_servers=8;

SQL> show parameter parallel_max_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 8

场景1，并行度设置为2，实际的并行度为4

session1:
create index ind_brtt_TRANS_TRACK_ID on BPMS_RU_TRANS_TRACK(TRANS_TRACK_ID) parallel 2 nologging;

session2:
SQL> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P002 IN USE 28 26104 10 3155
P000 IN USE 26 26096 143 4585
P001 IN USE 27 26100 191 12111
P003 IN USE 31 26108 192 8579
P006 AVAILABLE 35 26120
P007 AVAILABLE 36 26124
P005 AVAILABLE 34 26116
P004 AVAILABLE 32 26112

已选择8行。

场景2，两个会话并行度设置为2，跟场景1比较，两个会话会占8个并行

session1:

create index ind_brtt_TRANS_ACTIVE_INS_ID on BPMS_RU_TRANS_TRACK(ACTIVE_INS_ID) parallel 2 nologging;

session2:

create index ind_brtt_TRANS_ACTIVE_INS_ID on BPMS_RU_TRANS_TRACK(ACTIVE_INS_ID) parallel 2 nologging;

session3:

SQL> select * from v$px_process;

SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P006 IN USE 36 26306 11 4569
P002 IN USE 28 26273 17 10857
P004 IN USE 34 26298 131 7743
P007 IN USE 38 26310 135 3217
P000 IN USE 26 26265 139 4749
P005 IN USE 35 26302 191 12117
P003 IN USE 31 26277 207 9757
P001 IN USE 27 26269 209 1257

场景3，并行度设置为4，实际的并行度为8

session1:
create index ind_brtt_TRANS_TRACK_ID on BPMS_RU_TRANS_TRACK(TRANS_TRACK_ID) parallel 4 nologging;

session2:
SQL> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P007 IN USE 40 26194 17 10843
P002 IN USE 28 26104 21 3693
P006 IN USE 36 26190 22 7611
P004 IN USE 34 26182 136 5095
P000 IN USE 26 26096 143 4589
P005 IN USE 35 26186 204 7967
P003 IN USE 31 26108 207 9749
P001 IN USE 27 26100 209 1249

已选择8行。

场景4，并行度设置为10，实际的并行度为8，这个就是parallel_max_servers控制

session1:
create index ind_brtt_TRANS_TRACK_ID on BPMS_RU_TRANS_TRACK(TRANS_TRACK_ID) parallel 10 nologging;

session2:
SQL> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P006 IN USE 36 26190 17 10845
P007 IN USE 40 26194 21 3695
P002 IN USE 28 26104 22 7613
P000 IN USE 26 26096 136 5097
P004 IN USE 34 26182 143 4591
P005 IN USE 35 26186 204 7969
P001 IN USE 27 26100 207 9751
P003 IN USE 31 26108 209 1251

已选择8行。

场景5，parallel_max_servers设置为4，可以看到最大并行度只能为4

SQL> alter system set parallel_max_servers=4;
System altered
SQL> show parameter parallel_max_servers
NAME TYPE VALUE
------------------------------------ ----------- ------
parallel_ma.x_servers integer 4

session1:
create index ind_brtt_TRANS_TRACK_ID on BPMS_RU_TRANS_TRACK(TRANS_TRACK_ID) parallel 8 nologging;

session2:
SQL> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P002 IN USE 28 26273 17 10855
P000 IN USE 26 26265 135 3215
P001 IN USE 27 26269 191 12115
P003 IN USE 31 26277 192 8583