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

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

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

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

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