Tuesday, July 26, 2016

Increasing Processes, Sessions and Transactions in Oracle XE

Increasing Processes, Sessions and Transactions in Oracle XE

Out of the box, Oracle Database 10g Express Edition RDBMS is fast and powerful. The stated limitations of 2GB of maximum RAM usage and 2GB of total datafile management are plentiful for it to easily run as the back-end for a small to medium-sized office application.
However, we soon hit a connection limit as characterised by the following Oracle Errors:
ORA-12516: TNS:listener could not find available handler with matching protocol stack

ORA-00020: maximum number of processes (%s) exceeded
ORA-00020: maximum number of processes (%s) exceeded
We can get this second message because Oracle creates Operating System processes to handle Connections (or Sessions) - which means Processes, Sessions (and as we'll soon see..) Transactions are all related.
The default values in Oracle XE for these parameters are:
  • Processes = 40
  • Sessions = 49
  • Transactions = 53
I was able to generate the above error message (ORA-00020) from about ~30 connections on a vanilla Oracle XE installation (on Windows 7).
So, let's increase these limits to allow more connections to our Oracle Server..

1. Log in as SYSDBA

From the menu 'Oracle Database 10g Express Edition', find and select 'Run SQL Command Line', then type:
connect sys as sysdba
and enter your SYS, or SYSTEM password at the prompt
Oracle XE - Connected as SYSDBA, showing default values for processes, sessions and transactions

2. ALTER SYSTEM commands

Update: The Oracle XE 10g documentation links below were broken, so they now point to the Oracle 11g R2 Standard Edition documentation instead - note that the default values are greater in 11g R2 Standard than the Express Edition (XE).
The Oracle Documentation states that TRANSACTIONS is derived from SESSIONS, which in turn is derived fromPROCESSES, thus:
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS
So, what value to start with for PROCESSES?  Trebling it is as good a start as any, then I'd add a few more for good measure..  Here are the values I recommend:
  • PROCESSES = 150
  • SESSIONS = 300
  • TRANSACTIONS = 330
type the following commands:
alter system set processes = 150 scope = spfile;

alter system set sessions = 300 scope = spfile;

alter system set transactions = 330 scope = spfile;
then to make the settings take effect, we need to bounce the database..
shutdown immediate;

startup;
OracleXE - alter system commands and restarting the database

3. Verify the new parameters

with this simple select statement..
select name, value  
from v$parameter  
where name in ('processes', 'sessions', 'transactions');
OracleXE - showing updated processes, sessions and transactions

No comments:

Post a Comment

  How to Change Instance Type & Security Group of EC2 in AWS By David Taylor Updated April 29, 2023 EC2 stands for Elastic Compute Cloud...