The parameter Parallel_max is the Oracle's largest open parallel service process, the concurrent sum of all sessions.
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
Scenario 1, the degree of parallelism is set to 2, the actual degree of parallelism is 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 rows have been selected.
Scenario 2, two session parallelism is set to 2, compared to scene 1, the two sessions will account for 8 parallel
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
Scenario 3, the degree of parallelism is set to 4, the actual degree of parallelism is 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 rows have been selected.
Scenario 4, the degree of parallelism is set to 10, the actual degree of parallelism is 8, this is the Parallel_max_servers control
Session1:
Create INDEX ind_brtt_trans_track_id on Bpms_ru_trans_track (trans_track_id) parallel 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 rows have been selected.
Scenario 5,parallel_max_servers is set to 4, you can see that the maximum degree of parallelism is only 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 Experiment
