sponsored links

Oracle sessions,processes 和 transactions 参数 关系 说明

 

一.官网说明

1.1 processes

11gR2 的文档:

 

Property

Description

Parameter type

Integer

Default value

100

Modifiable

No

Range of values

6 to operating system dependent

Basic

Yes

Oracle RAC

Multiple instances can have different values.

 

PROCESSES specifies the maximum numberof operating system user processes that can simultaneously connect to Oracle.Its value should allow for all background processes such as locks, job queueprocesses, and parallel execution processes.

 

The defaultvalues of the SESSIONS and TRANSACTIONS parameters arederived from this parameter. Therefore, if you change the valueof PROCESSES, you should evaluate whether to adjust the values of thosederived parameters.

 

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/initparams198.htm#REFRN10175

 

1.2 sessions

11gR1:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams220.htm#REFRN10197

 

Property

Description

Parameter type

Integer

Default value

Derived: (1.1 * PROCESSES) + 5

Modifiable

No

Range of values

1 to 231

Basic

Yes

 

11gR2

Property

Description

Parameter type

Integer

Default value

Derived: (1.5 * PROCESSES) + 22

Modifiable

No

Range of values

1 to 231

Basic

Yes

 

这里要注意的是到了11gR2里,sessions 的默认值计算方式变了。 该值的计算是针对 dedicate 模式的。

 

SESSIONS specifies the maximum number of sessions that can becreated in the system. Because every login requires a session, this parametereffectively determines the maximum number of concurrent users in the system.You should always set this parameter explicitly to a value equivalent to yourestimate of the maximum number of concurrent users, plus the number ofbackground processes, plus approximately 10% for recursive sessions.

Oracle uses thedefault value of this parameter as its minimum. Values between 1 and thedefault do not trigger errors, but Oracle ignores them and uses the defaultinstead.

The defaultvalues of the ENQUEUE_RESOURCES and TRANSACTIONS parametersare derived from SESSIONS. Therefore, if you increase the valueof SESSIONS, you should consider whether to adjust the valuesof ENQUEUE_RESOURCES and TRANSACTIONS as well. (Notethat ENQUEUE_RESOURCES is obsolete as of Oracle Database 10g release2 (10.2).)

In a shared server environment, the value of PROCESSES canbe quite small. Therefore, Oracle recommends that youadjust the value of SESSIONS to approximately 1.1 * total numberof connections.

 

 

1.3 transactions

11gR2

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/initparams258.htm#REFRN10222

 

 

Property

Description

Parameter type

Integer

Default value

Derived: (1.1 * SESSIONS)

Modifiable

No

Range of values

4 to 232

Oracle RAC

Multiple instances can have different values.

 

TRANSACTIONS specifieshow many rollback segments to onlinewhen UNDO_MANAGEMENT = MANUAL. The maximum number of concurrenttransactions is now restricted by undo tablespace size(UNDO_MANAGEMENT = AUTO) or the number of online rollback segments(UNDO_MANAGEMENT = MANUAL).

 

 

二. 测试

 

2.1 测试DB版本

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE   11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0- Production

NLSRTL Version 11.2.0.1.0 - Production

 

SQL> show parameter processes

 

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     0

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     1000

log_archive_max_processes            integer     4

processes                            integer     150

 

SQL> show parameter sessions

 

NAME                                 TYPE        VALUE

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

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

sessions                             integer     248

shared_server_sessions               integer

 

按照11gR2的中的计算方法:SESSIONS=(1.5* PROCESSES) + 22

                                                   SESSIONS=1.5*150+22=247

 

SQL> show parameter transaction

 

NAME                                 TYPE        VALUE

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

transactions                         integer    272

transactions_per_rollback_segment    integer    5

 

根据公式:transaction= (1.1 * SESSIONS)

                     1.1*248==272.8

 

 

2.2 只增加processes到1000,观察sessions 和 transactions 参数

 

SQL> create pfile from spfile;

File created.

 

然后修改init 文件,将processes 修改成1000。

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create spfile frompfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITnewccs.ORA';

 

File created.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             595591632 bytes

Database Buffers          469762048 bytes

Redo Buffers                4603904 bytes

Database mounted.

Database opened.

 

SQL> show parameter processes

 

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     0

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     1000

log_archive_max_processes            integer     4

processes                            integer     1000

 

SQL> show parameter sessions

 

NAME                                 TYPE        VALUE

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

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

sessions                             integer     1522

shared_server_sessions               integer

 

SQL> show parameter transaction

 

NAME                                 TYPE        VALUE

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

transactions                         integer     1674

transactions_per_rollback_segment    integer    5

SQL>

 

当我们修改processes 之后,如果sessions 和transactions 小于由公式计算出来的值,就会自动进行修改。为了验证这个观点,我们在做一个测试,修改processes 值,同时修改sessions和transactions的值,使其大于公式计算出来的值。

 

2.3 同时修改processes,sessions和transactions 值

Processes 设为500

根据公式:sessions=1.5*500+22=772

                     Transactions=1.1*sessions=850

 

我们将sessions 设置为900,transactions 设置为1000

 

*.processes=500

*.sessions=900

*.transactions=1000

 

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create spfile frompfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITnewccs.ORA';

 

File created.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             595591632 bytes

Database Buffers          469762048 bytes

Redo Buffers                4603904 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter processes

 

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     0

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     1000

log_archive_max_processes            integer     4

processes                            integer     500

 

SQL> show parameter sessions

 

NAME                                 TYPE        VALUE

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

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

sessions                             integer     900

shared_server_sessions               integer

 

SQL> show parameter transactions

 

NAME                                 TYPE        VALUE

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

transactions                         integer     1000

transactions_per_rollback_segment    integer    5

SQL>

 

 

 

 

 

 

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

版权所有. 文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

Blog: http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群:83829929(满) DBA5群: 142216823(满) 

DBA6 群:158654907(满)   DBA7 群:69087192(满)  DBA8 群:172855474

DBA 超级群2:151508914  DBA9群:102954821     聊天 群:40132017(满)

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

Tags:
  • oracle 修改processes参数
    oracle 修改processes参数 $ sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Thu Nov 4 10:43:29 2010 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: ...
  • oracle 连接(connects), 会话(sessions)和进程(pocesses)的关系
    连接connects,会话sessions和进程pocesses的关系 每个sql login称为一个连接(connection), 而每个连接,可以产生一个或多个会话,这个依据你的服务器是共享模式还是专用模式:   如果数据库运行在专用服务器方式,         则一个会话对应一个服务器进程(process), 如果数据库运行在共享服务器方式,      ...
  • Oracle OCP笔记(07)初始化参数文件
    Oracle OCP笔记(07)初始化参数文件 一.初始化参数文件     初始化参数文件是数据库实例的配置文件,数据库实例在加载进内存时,内存中的参数通过参数文件或默认值构建. 二.两种初始化参数文件(pfile,spfile) 1.pfile(parameter file,参数文件)     pfile是文本格式的参数文件,实例启动时只加载一次,通常认为 ...
  • Oracle Data Guard 重要配置参数
    Oracle Data Guard 重要配置参数
        Oracle Data Guard主要是通过为生产数据库提供一个或多个备用数据库(是产生数据库的一个副本),以保证在主库不可用或异常时数据不丢失并通过备用数据库继续提供服务.对于Oracle DG的配置,我们可以通过Grid Control来完成,也可以通过Data Guard Broker以及SQL*Plus来完成.对于前两者方式可以在图形界面上完 ...
  • Oracle Ebs R12 SLA与GL关系变化
    Oracle Ebs R12 SLA与GL关系变化
    Oracle Ebs R12 SLA与GL关系变化SLA概念:SLA(Subledger Accounting) 子帐是子分类帐会计的简称,字面上的含义就是子分类帐会计分录SLA&GL关系:   R12功能模块上面都启用了MOAC特性,新增了子模块和税模块,OPM和离散库存也集成了,所有的子模块会计分录都可以使用特定的公司配置出来;之间的关系分位三种 ...