Description of the relationship between Oracle sessions, processes, and transactions Parameters

 

I. Official Website description

1.1 processes

11gR2 documentation:

 

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 shocould 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 shocould 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

 

Note that in 11gR2, the default computing method of sessions has changed. This value is calculated in dedicate mode.

 

SESSIONS specifies the maximum number of sessions that can be becreated in the system. because every login requires a session, this parametertransfertively determines the maximum number of concurrent users in the system. you shoshould 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 shoshould 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 implements 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 ).

 

 

Ii. Test

 

2.1 test the database version

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

 

According to the calculation method in 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

 

According to the formula: transaction = (1.1 * SESSIONS)

1.1*248 = 272.8

 

 

2.2 add processes to 1000 and observe the parameters of sessions and transactions.

 

SQL> create pfile from spfile;

File created.

 

Modify the init file to 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>

 

After processes is modified, if sessions and transactions are smaller than the value calculated by the formula, they are automatically modified. To verify this idea, we are doing a test to modify the processes value and modify the sessions and transactions values to make them greater than the value calculated by the formula.

 

2.3 modify the values of processes, sessions, and transactions simultaneously.

Set Processes to 500

According to the formula: sessions = 1.5*500 + 22 = 772

Transactions = 1.1 * sessions = 850

 

Set sessions to 900 and transactions to 1000.

 

*. Processses = 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>

Bytes -------------------------------------------------------------------------------------------------------

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com