Bernie's Blog A confusing concoction of Java, mobile devices, technology and photography

Configuring Oracle XE For Performance Testing

If you put any serious load on Oracle XE, connections tend to fail almost randomly and you'll see this:

ORA-12516 TNS:listener could not find available handler with matching protocol stack
ORA-12520: TNS:listener could not find available handler for requested type of server

Initially I thought it was a limitation of the product as it is supposed to be a lightweight version of Oracle. This is certainly not the case though. XE is installed to take up little resources and and this is one of the side effects. To allow it to take heavier load, do the following:

1. login as oracle: 'su - oracle'
2. connect to sqlplus: 'sqlplus / as sysdba'
3. run 'show parameter processes'
4. you should be able to see that the default value for processes is '40'
5. run 'alter system set processes=100 scope=spfile;'
6. restart XE by running 'shutdown immediate' and then 'startup'
7. confirm the new setting by running (3) again

This will increase the number of processes to XE, allowing it to take a higher load.

Cheers.