oracle parallel_max_servers,12c无法按照预期修改parallel_max_servers

客户有个12c的环境，正在做xtts的迁移，从10g迁移到12c。其中一项要求是12c的一些初始化参数和10g一致，如parallel_max_servers。发现设置后，重启了数据库，还是无法达到预期的修改值。

Oracle PL/SQL

SQL> show parameter parallel_max_servers

NAME TYPE VALUE

---------------------- ----- ---------

parallel_max_servers integer 474

SQL> alter system set parallel_max_servers=585 scope=spfile;

shutdown immediate;

startup;

SQL> show parameter parallel_max_servers

NAME TYPE VALUE

---------------------- ----- ---------

parallel_max_servers integer 474

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SQL>showparameterparallel_max_servers

NAMETYPEVALUE

----------------------  -----     ---------

parallel_max_serversinteger474

SQL>altersystemsetparallel_max_servers=585scope=spfile;

shutdownimmediate;

startup;

SQL>showparameterparallel_max_servers

NAMETYPEVALUE

----------------------  -----     ---------

parallel_max_serversinteger474

打开其alertlog中，我们可以看到类似如下的信息：

Adjusting the altered value of parameter parallel_max_servers

from 585 to 474 due to the value of parameter processes (600)

……

1

2

3

Adjustingthealteredvalueofparameterparallel_max_servers

from585to474duetothevalueofparameterprocesses(600)

……

我们在Doc ID 1968840.1处看到：

From 11gR2 onwards, there is a new method to compute the default for PARALLEL_MAX_SERVERS.

In 11gR2, the value of PARALLEL_MAX_SERVERS is capped by PROCESSES - 15.

In 12c, the value of PARALLEL_MAX_SERVERS is capped by PROCESSES - N, where N is the result of an internal calculation that estimates the maximum number of background processes that need to be reserved for the particular database.

1

2

3

From11gR2onwards,thereisanewmethodtocomputethedefaultforPARALLEL_MAX_SERVERS.

In11gR2,thevalueofPARALLEL_MAX_SERVERSiscappedbyPROCESSES-15.

In12c,thevalueofPARALLEL_MAX_SERVERSiscappedbyPROCESSES-N,whereNistheresultofaninternalcalculationthatestimatesthemaximumnumberofbackgroundprocessesthatneedtobereservedfortheparticulardatabase.

parallel_max_servers的最大值，受限制与process，在11gR2中，这个限制是parallel_max_servers=processes-15(另外，在11.2.0.4中变成了30).

而在12c中，是processes-N，这个N是一个内部算法。具体的值可以通过如下来得到：

SQL> select KSBNEEDED_VAL into v_process_reserved from sys.X$KSBDPNEEDED ;

SQL>

1

2

SQL>selectKSBNEEDED_VALintov_process_reservedfromsys.X$KSBDPNEEDED;

SQL>

所以在12c中的parallel_max_servers可以设置的最大值，为process-(KSBNEEDED_VAL.X$KSBDPNEEDED)。

参考：

How PARALLEL_MAX_SERVERS and PROCESSES Parameters are Related? (Doc ID 1968840.1)

New Default Value of PARALLEL_MAX_SERVERS in Release 11.2.0.2 (Doc ID 1377411.1)

Oracle Community:The value of parallel_max_server adjusted in alertlog of 12c(12.1.0.2) database is not as per the formula mentioned in the (Doc ID 1968840.1)