IBM Support

High Number of Open Cursors from Oracle Pipelines

Troubleshooting


Problem

  1. There is a performance trade-off with the number of open cursors which are allowed on the DB

  2. There is an Oracle DB limit OPEN_CURSORS and if this limit is reached, the Oracle DB connection will fail with ORA-01000: maximum open cursors exceeded.

  3. Every pipeline’s JDBC connection is isolated, each stage instance will get it’s own cursor.

  4. For Multi-threaded Pipelines, the number of open cursors per JDBC stage will be equal to the number of pipeline threads.

  5. Each JDBC stage thread will keep its cursor open during the entire duration of the pipeline.

    1. Once the pipeline stops, all of the JDBC stages’s cursors will be closed.

Symptom

  1. A pipeline which connects to Oracle DB throws Stage Errors that the “Maximum open cursors exceeded”.

  2. An Oracle DBA sees that there is a large number of open cursors for the DB user which is configured in StreamSets pipelines.

Resolving The Problem

  1. If ORA-01000 errors are occurring in your Oracle pipelines, the following steps can be followed to determine what applications are maxing out the OPEN_CURSOR limit and whether you need to increase your OPEN_CURSORS limit to run your SDC pipelines:

    1. Find out the session that is causing the error by using the following SQL statement:

      select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null;

       The output displays the details of all sessions. You can see the maxed out session IDs and compare how many are from your SDC pipeline user and how many are from other applications.

    2. If needed, you can increase your OPEN_CURSOR limit with the query:

      alter system set open_cursors = 1000.

  1. Monitoring Open and Cached Cursors

  2. Troubleshooting Open Cursor Issues

  3. Oracle Database Reference: V$OPEN_CURSOR

Document Location

Worldwide

[{"Line of Business":{"code":"LOB76","label":"Data Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSM7CU","label":"IBM StreamSets Data Collector"},"ARM Category":[{"code":"","label":""}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
15 March 2025

UID

ibm17186188

Page Feedback