IBM Support

Large number of inactive Oracle processes created by one user performing a simple task (for example a logon) caused by defect APAR PI60308 in Controller

Troubleshooting


Problem

Oracle DBA has noticed a large number of inactive processes listed on the Oracle database server. Specifically, it appears that approximately 10 times more Oracle processes are created during a process (for example a single user logon) when using Controller 10.2 (compared with the older Controller 10.1 version). - In some environments, this can (eventually) cause an error message.

Symptom

Example #1:

In one real-life test (using Controller 10.2.2039 connected to Oracle 11G release 2):

One user launching Controller client and reaching logon screen...


...created 2 new 'INACTIVE' processes (associated with this database).

After typing in the username/password, and reaching the 'actuality' choice screen...

...there were a total of 20 INACTIVE processes (associated with this database).

After clicking OK (and the Controller client was finally open)...


...there were now 32 INACTIVE processes.

Then after closing Controller client, there were still 32 INACTIVE processes.

Example #2:

In a different real-life test (using Controller 10.2.5130.56 connected to Oracle 11G release 2):

  • One user launching Controller client and reaching logon screen created 2 new 'INACTIVE' processes (associated with this database).
  • After typing in the username/password, and reaching the 'actuality' choice screen, there were a total of 19 INACTIVE processes (associated with this database).
  • After clicking OK (and the Controller client was finally open) there were now 32 INACTIVE processes.
  • Then after closing Controller client, there were still 32 INACTIVE processes.
 

Potential Error message:

Eventually (after a while) the number of free processes may drop to zero. When this happens, the users will get the following error message:


    • ORA-12520: TNS:listener could not find available handler for requested type of server

Cause

There are several possible causes for the 'listener could not find available handler' error.
  • TIP: See separate IBM Technote #1603472 for more examples.

This Technote specifically relates to the scenario where the cause is a defect (reference APAR PI60308) in Controller 10.2.x & 10.3.x.
  • This defect causes a leak of Oracle cursors.

More Information:
Earlier versions (for example Controller 10.1) do not have this behaviour. Specifically:
  • Controller 10.1: 3 Oracle processes created (during a single user logon)
  • Controller 10.2: 30 Oracle processes created (during a single user logon)

Diagnosing The Problem

How to check the maximum number of Processes that your Oracle server can host:

To check the maximum value of processes (that your Oracle database server will allow), ask your Oracle administrator (DBA) to run the following Oracle script:

  • show parameters processes;
 

How to check the current number of Processes in use (on your Oracle server):

To check how many Oracle processes currently exist (active or inactive), ask your Oracle administrator (DBA) to run the following Oracle script:

  • SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
    FROM v$session sess,
    v$sql sql
    WHERE sql.sql_id(+) = sess.sql_id
    AND sess.type = 'USER'

You will see a screen similar to:

Resolving The Problem

Fix:
Upgrade to Controller 10.4 (or later).
         
Workaround:
Increase the value of PROCESSES to a sufficiently high number.
  • For most (smaller) customers, 1000 should be sufficient.
  • For large customers, 3000 should be sufficient.

Steps:
See separate IBM Technote #1603472.

Is it safe to increase PROCESSES ?
IBM believes that there is very little drawback to increasing PROCESSES.
  • Open_cursors will set a maximum number of cursors a process can open. If you set it to 1000 Oracle will actually allocate cursors 64 at a time, so setting this artificially high does not have a negative impact on resource usage.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3;10.2.1;10.2.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 March 2019

UID

swg21978834

Page Feedback