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 2³¹
Basic	Yes

11gR2

Property	Description
Parameter type	Integer
Default value	Derived: (1.5 * PROCESSES) + 22
Modifiable	No
Range of values	1 to 2³¹
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 2³²
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>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15880878/viewspace-720008/，如需轉載，請註明出處，否則將追究法律責任。

Oracle sessions,processes 和 transactions 引數 關係 說明

一.官網說明

1.1 processes

1.2 sessions

1.3 transactions

二. 測試

2.1 測試DB版本

2.2 只增加processes到1000，觀察sessions 和 transactions 引數

2.3 同時修改processes，sessions和transactions 值

相關文章

Oracle sessions,processes 和 transactions 引數關係說明