標籤：
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
oracle parameter parallel_max實驗
