Testing maximum number of Oracle sessions supported by shared servers

I’ve been testing a shared servers configuration and I was asked to push a test database to its limits to see how many sessions it would support.  It was really just a test of how many could login, not how many could actively use the database at the same time.

The first thing I found out that was on my platform, HP-UX Itanium 11.1.0.7, the maximum value that I could set the init parameter sessions to and have the database come up was 65535.  It appears that session id is a 16 bit unsigned number which results in this limit, at least on the tested platform.  I got this interesting message in the alert log when I set sessions to 100000:

ORA-41009: Session with session number 34463, serial number 1 does not exist
PMON (ospid: 20330): terminating the instance due to error 41009

I got this fun error when I set sessions to 65536:

ORA-00600: internal error code, arguments: [kews_init_ses - bad sesid], [], [],
[], [], [], [], [], [], [], [], []

Otherwise I tested the limits by running one or more Java programs that open up a bunch of sessions and give me a session count after each 100 sessions opened.  I couldn’t open more than around 3700 sessions from my laptop no matter how many Java processes I ran so I must have hit some client networking limit.  Then I was able to run about 10 Java processes on a test database server and maxed out at around 11,000 sessions open.  I couldn’t open  more than about 2000 per Java process on the db server.  Shared pool memory appeared to be my limiting factor.  I had 7 gig sga, 2 gig large pool 2 gig shared pool.  I had 32 dispatchers and 32 shared servers.  At around 11,000 connections I started getting shared pool memory errors in the alert log.

Here is the Java program:

/**

 Attempt to max out the session count on a database.

 */

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

public class MaxSessions
{
  public static void main (String args [])
    throws SQLException
  {
    // Create a OracleDataSource instance explicitly
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:oci:TEST/TEST@testdb");

    // Retrieve a connection
    Connection conn = ods.getConnection();

    // Array of connections
    int num_connections=1000;

    Connection carray[]=new Connection[num_connections];

    // fill array with connections

    for (int i=0; i<num_connections; i++)
      {
      carray[i] = ods.getConnection();
      if ((i % 100)==0)
        getSessionCount(conn);
      }

    getSessionCount(conn);

    // close connections

    for (int i=0; i<num_connections; i++)
      carray[i].close();

    // Close the connection
    conn.close();
    conn = null;
    System.out.println ("All sessions closed, MaxSessions complete");
  }
  static void getSessionCount(Connection conn)
       throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    ResultSet rset = stmt.executeQuery ("select count(*) from v$session");

    while (rset.next ())
      System.out.println ("Session count is " + rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset =  null;

    // Close the Statement
    stmt.close();
    stmt = null;
  }
}

Here is my Unix script to kick 10 of these off at once:

$ cat run10.sh
export JDK_HOME=$ORACLE_HOME/jdk
export JAVAC=$JDK_HOME/bin/javac
export JAVA=$JDK_HOME/bin/java
export CLASSPATH15=.:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/fscontext.jar:$ORACLE_HOME/jlib/orai18n.jar
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$LD_LIBRARY_PATH

$JAVAC -classpath $CLASSPATH15 -g  MaxSessions.java
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run1.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run2.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run3.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run4.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run5.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run6.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run7.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run8.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run9.out &
nohup $JAVA -classpath $CLASSPATH15 MaxSessions > run10.out &

Note:  I hacked the Oracle supplied Java demo program DataSource.java and the batch file rundemo.bat to build this test.  I don’t write much Java so I usually start with an Oracle demo.

Here is the output from out of the Java processes showing the sessions over 11,000:

$ cat run9.out
Session count is 46
Session count is 1065
Session count is 2081
Session count is 3095
Session count is 4060
Session count is 5036
Session count is 5995
Session count is 6979
Session count is 7996
Session count is 8974
Session count is 9988
Session count is 10874
Session count is 10685
Session count is 11565
Exception in thread "main" java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 248 bytes of shared memory ("shared pool","unknown object","kgsp-heap","kgllk")

– Bobby