The memory consumption keeps increasing while continuously executing the storage procedure in "Thick" mode

[YJUN0121]

1. What versions are you using?

oracledb version: 6.7.0 oracledb thin mode: false oracle client version: 19.25.0.0.0

$node --version v20.15.1

2. Describe the problem

When a stored procedure containing a custom type is continuously invoked, the memory usage keeps increasing.

3. Include a runnable Node.js script that shows the problem.

/**
CREATE OR REPLACE TYPE my_custom_type AS OBJECT (
  id NUMBER,
  name VARCHAR2(100)
);

CREATE OR REPLACE PROCEDURE my_custom_proc (
  p_in IN my_custom_type,
  p_in_out IN OUT my_custom_type
) IS
BEGIN
  p_in_out.id := p_in_out.id + p_in.id;
  p_in_out.name := p_in_out.name || p_in.name;
END my_custom_proc;
*/

const oracledb = require("oracledb");
oracledb.initOracleClient();
let pool = {};

async function initPool() {
  try {
    pool = await oracledb.createPool({
      user: "",
      password: "",
      connectString: "",
      poolMin: 20,
      poolMax: 64,
      poolIncrement: 2,
      poolTimeout: 60
    });
    console.log("Connection pool initialized successfully.");
  } catch (err) {
    console.error("Connection pool initialization failed:", err);
  }
}

async function executeProcedure() {
  let connection;
  try {
      connection = await pool.getConnection();
      let sql = `
        declare
            P_IN MY_CUSTOM_TYPE;
            P_IN_OUT MY_CUSTOM_TYPE;
        begin
            MY_CUSTOM_PROC(P_IN => :P_IN, P_IN_OUT => :P_IN_OUT);
        end;`;
      let p = {"P_IN":{"type":"MY_CUSTOM_TYPE","val":{"ID":123,"NAME":"hello"},"dir":3001},"P_IN_OUT":{"type":"MY_CUSTOM_TYPE","val":{"ID":456,"NAME":"world"},"dir":3002}};
      let res = await connection.execute(sql, p);
      //console.log("Result: %s", res);
  } catch (err) {
      console.error("Failed to execute stored procedure:", err);
  } finally {
      if (connection) {
          await connection.close();
      }
  }
}

async function main() {
  await initPool();
  setInterval(() => {
    for (let i = 0; i < 50; i++) {
      executeProcedure();
    }
    console.log(`Memory Usage: ${process.memoryUsage().heapUsed / 1024 / 1024} MB`);
  }, 1000);
}

main();

Result: Connection pool initialized successfully. Memory Usage: 5.4888153076171875 MB Memory Usage: 5.7423858642578125 MB Memory Usage: 5.997673034667969 MB Memory Usage: 5.478019714355469 MB Memory Usage: 5.9424896240234375 MB Memory Usage: 6.422264099121094 MB Memory Usage: 6.283149719238281 MB Memory Usage: 6.881156921386719 MB Memory Usage: 6.675872802734375 MB Memory Usage: 7.139030456542969 MB Memory Usage: 7.642112731933594 MB Memory Usage: 8.131202697753906 MB Memory Usage: 7.844795227050781 MB Memory Usage: 8.704254150390625 MB Memory Usage: 7.896240234375 MB Memory Usage: 8.808212280273438 MB Memory Usage: 8.490554809570312 MB Memory Usage: 9.391311645507812 MB Memory Usage: 8.614830017089844 MB Memory Usage: 9.408500671386719 MB Memory Usage: 8.921623229980469 MB Memory Usage: 9.904556274414062 MB

Continuously calling stored procedures results in a continuous increase in memory usage. What is the reason for this?

Thank you for your help.

[sharadraju]

Thanks @YJUN0121 for using node-oracledb. A couple of questions while we look into this:

• Did you test this use case in previous node-oracledb versions? If so, which versions
• Have you tested this with Thin mode? Do you see the same scenario.

[sudarshan12s]

As per my testing, I could not see in thin mode. On thick mode, I do see an increase with node-oracledb 6.7. I will share an update on thick mode.

[cjbj]

Are you just seeing the effect of having an increasing number of connections? I recommend not testing via the loop. Instead use a web app and throw a much larger number of user requests at it. See if the memory stabilizes.

Also, as a best practice, use the schema name when binding. From the doc:

use fully qualified names like “MDSYS.SDO_GEOMETRY” instead of “SDO_GEOMETRY”.

[YJUN0121]

Thanks @YJUN0121 for using node-oracledb. A couple of questions while we look into this:

• Did you test this use case in previous node-oracledb versions? If so, which versions I tested the node-oracledb 6.0.2, 6.5.0, and 6.7.0 versions with this use case, all of which showed a growing trend of memory usage in Thick mode.

• Have you tested this with Thin mode? Do you see the same scenario. My Oracle database version is 11.2, and node-oracledb versions higher than 6.0.0 do not support connections in Thin mode. The previous version of node-oracledb I used was 5.5.0, and I didn't see a consistent increase in memory consumption using this use case.