session与process的设置关系
session:指定了一个实例中允许的会话数,即能同时登录到数据库的并发用户数。
process: 指定了一个实例在操作系统级别能同时运行的进程数,包括后台进程与服务器进程。
由上面的分析可知,一个后台进程可能同时对应对个会话,因此通常sessions的值是大于processes的值
从官方文档我们可以查询到以下的信息:
Property
Oracle 10g
Oracle 11g
Parameter type
Integer
Integer
Default value
Derived: (1.1 *PROCESSES) + 5
Derived: (1.5 *PROCESSES) +
22
Modifiable
No
No
Range of values
1 to 231
1 to 216 (which is 1 to 65536)
Basic
Yes
Yes
SESSIONS specifies the
maximum number of sessions that can be created in the system. Because every
login requires a session, this parameter effectively determines the maximum
number of concurrent users in the system. You should always set this parameter
explicitly to a value equivalent to your estimate of the maximum number of
concurrent users, plus the number of background processes, plus approximately
10% for recursive sessions.
Oracle uses the default
value of this parameter as its minimum. Values between 1 and the default do not
trigger errors, but Oracle ignores them and uses the default instead.
The default values of the
ENQUEUE_RESOURCES and TRANSACTIONS parameters are derived from SESSIONS.
Therefore, if you increase the value of SESSIONS, you should consider whether
to adjust the values of ENQUEUE_RESOURCES and TRANSACTIONS as well. (Note that
ENQUEUE_RESOURCES is obsolete as of Oracle Database 10g release 2 (10.2).)
In a shared server
environment, the value of PROCESSES can be quite small. Therefore, Oracle
recommends that you adjust the value of SESSIONS to approximately 1.1 * total
number of connections.
3.1结论
1. sessions的值是根据processes的值计算得到的,一般情况下只需要设置processes的值即可。
2. 在Oracle 10g中,sessions大小的的计算公式为:(1.1 * PROCESSES) + 5;在Oracle 11g中,sessions大小的的计算公式为:(1.5 * PROCESSES) + 22。
3. 若sessions的当前值比计算值大的话,则sessions的值可能保持不变;若sessions的当前值比计算值小的话,则sessions取计算值,即sessions的值总是取MAX(当前值,计算值),但是这个也不是绝对的。
3.2实验
SYS@lhrdb> COL NAME FORMAT A10
SYS@lhrdb> COL VALUE FORMAT A10
SYS@lhrdb> SELECT A.NAME, A.VALUE
2 FROM V$PARAMETER A
3 WHERE A.NAME IN ('processes',
'sessions');
NAME VALUE
---------- ----------
processes 100
sessions 176
SYS@lhrdb> alter system set processes=200
scope=spfile;
System altered.
SYS@lhrdb> STARTUP FORCE
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed
Size
2247072 bytes
Variable
Size
503318112 bytes
Database
Buffers 1207959552 bytes
Redo
Buffers
6803456 bytes
Database mounted.
Database opened.
SYS@lhrdb> SELECT A.NAME, A.VALUE
2 FROM V$PARAMETER A
3 WHERE A.NAME IN ('processes',
'sessions');
NAME VALUE
---------- ----------
processes 200
sessions 328
SYS@lhrdb>
SYS@lhrdb> alter system set processes=50
scope=spfile;
System altered.
SYS@lhrdb> STARTUP FORCE
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed
Size
2247072 bytes
Variable
Size
503318112 bytes
Database
Buffers 1207959552 bytes
Redo
Buffers
6803456 bytes
Database mounted.
Database opened.
SYS@lhrdb> SELECT A.NAME, A.VALUE
2 FROM V$PARAMETER A
3 WHERE A.NAME IN ('processes',
'sessions');
NAME VALUE
---------- ----------
processes 50
sessions 176
SYS@lhrdb>
SYS@lhrdb> alter system set
processes=60 scope=spfile;
System altered.
SYS@lhrdb> STARTUP FORCE
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed
Size
2247072 bytes
Variable
Size
452986464 bytes
Database Buffers
1258291200 bytes
Redo
Buffers
6803456 bytes
Database mounted.
Database opened.
SYS@lhrdb> SELECT A.NAME, A.VALUE
2 FROM V$PARAMETER A
3 WHERE A.NAME IN ('processes',
'sessions');
NAME VALUE
---------- ----------
processes 60
sessions 176
3.3报错信息
当数据库连接的并发用户已经达到这个值时,又有新session连进来,就会报错
ORA-00018,"maximum number of sessions
exceeded"
当Oracle需要启动新的process而又已经达到processes参数时,就会报错:
ORA-00020: maximum number of processes (2048)
exceeded
如果数据库上连接被占用完,新的连接过来时,会在客户端产生:"ORA-12519,
TNS:no appropriate service handler found "的报错信息.
[ZFLHRDB2:oracle]:/oracle>oerr ora 12519
12519, 00000, "TNS:no appropriate service
handler found"
// *Cause: The listener could not find any available
service handlers that
// are appropriate for the client connection.
// *Action: Run "lsnrctl services" to
ensure that the instance(s) have
// registered with the listener, and are accepting
connections.
[ZFLHRDB2:oracle]:/oracle>oerr ora 20
00020, 00000, "maximum number of processes (%s)
exceeded"
// *Cause: All process state objects are in
use.
// *Action: Increase the value of the PROCESSES
initialization parameter.
[ZFLHRDB2:oracle]:/oracle>oerr ora 18
00018, 00000, "maximum number of sessions
exceeded"
// *Cause: All session state objects are in
use.
// *Action: Increase the value of the SESSIONS
initialization parameter.