-
Notifications
You must be signed in to change notification settings - Fork 1.1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SQL Bind/Cursor Issue #1694
Comments
Just wanted to add an update to this issue. After further testing, I can see that the cursor is never released/reused on this query. Every time the query runs, a new row is created in v$open_cursor. All my other queries are parsed and loaded 1X and I do not see any additions to v$open_cursor. I think this issue should be flagged as a bug at this time. |
Thanks @slewis30328 for the details, we will try to reproduce it . Can you give more details :
|
It's just run as a sql query and we are using thin mode. Here is some DDL examples if needed: CREATE OR REPLACE type SMSYS.NumberArray as table of number CREATE TABLE SMSYS.ITEMFAV NOTE: I didn't add index DDL - let me know if you need that. |
I am trying to add a test as below to reproduce the issue seen , can you check and let me know if it represents the issue at high level.
|
@slewis30328 , I could not reproduce the issue with 19.24 DB version. Can you let us know if above program reproduces the issue in your environment or if the above test needs modifications to reproduce. |
When I get a chance, I will see if I can reproduce issue with your test case above. Not sure if this makes a difference, but we are using connection pooling - example above is not using that. When I run my tests, this is the SQL statement that gets repeated and causes the open cursor count to increase for each execution. I guess this statement is run from your driver layer? SELECT /*+ NOPARALLEL */ |
Thanks. I tried with pool. I modified my test using pool. I am able to see the issue with connection been used to create a dbobject, release to pool in loop , I will share more details.
|
I am able to reproduce the issue. The cursor close in this case was missing. This is not a complete fix, but only for above specific case. If its possible to test this patch in the test env, please confirm. We will share the complete fix.
|
Can I apply this to 6.6.0 or is this just for 6.5.1? My dev env is 6.6.0 and we have 6.5.1 in TEST and PROD. |
I tested with 6.5.1 and that fix above worked. Do not see the cursor count increase. |
I know you said this a temp fix, but could I apply this in our PROD env until the patch comes out? When the count is exceeded, this causes major issues at application level. If you don't think that's a good idea, I could always go back to a standard IN clause. Not crazy about the use of that based on potential param size - much cleaner approach binding to the collection. |
I have done more testing. I am sharing the patch for 6.5.1 . The issue was happening when the diff --git a/lib/thin/connection.js b/lib/thin/connection.js
index 7a8a90548..db9ca7904 100644
--- a/lib/thin/connection.js
+++ b/lib/thin/connection.js
@@ -513,31 +513,37 @@ class ThinConnectionImpl extends ConnectionImpl {
// check cache; if already present, nothing more to do!
const info = this._getDbObjectType(result.outBinds.schema,
result.outBinds.name, result.outBinds.package_name, result.outBinds.oid);
- if (!info.partial)
+ if (!info.partial) {
+ result.outBinds.attrs_rc.close();
return info;
+ }
+ try {
// process TDS and attributes cursor
- info.version = result.outBinds.version;
- const attrRows = await result.outBinds.attrs_rc.getRows(1000, {});
- if (attrRows.length > 0) {
+ info.version = result.outBinds.version;
+ const attrRows = await result.outBinds.attrs_rc.getRows(1000, {});
+ if (attrRows.length > 0) {
// Its an object not a collection.
- info.attributes = [];
- for (const row of attrRows) {
- const attr = { name: row[1] };
- if (row[4]) {
- attr.type = types.DB_TYPE_OBJECT;
- attr.typeClass = this._getDbObjectType(row[4], row[3], row[5], row[6]);
- if (attr.typeClass.partial) {
- this._partialDbObjectTypes.push(attr.typeClass);
+ info.attributes = [];
+ for (const row of attrRows) {
+ const attr = { name: row[1] };
+ if (row[4]) {
+ attr.type = types.DB_TYPE_OBJECT;
+ attr.typeClass = this._getDbObjectType(row[4], row[3], row[5], row[6]);
+ if (attr.typeClass.partial) {
+ this._partialDbObjectTypes.push(attr.typeClass);
+ }
+ } else {
+ attr.type = types.getTypeByColumnTypeName(row[3]);
}
- } else {
- attr.type = types.getTypeByColumnTypeName(row[3]);
+ info.attributes.push(attr);
}
- info.attributes.push(attr);
}
- }
- await this._parseTDS(result.outBinds.tds, info);
+ await this._parseTDS(result.outBinds.tds, info);
+ } finally {
+ result.outBinds.attrs_rc.close();
+ }
info.partial = false;
return info; Note: I have added finally block to close in all cases. Without tabs ( |
Is it possible to post the updated connection.js file from your patch? Thank you. |
Please rename to connection.js and place it in lib/thin/ folder |
We have encountered an issue related to max open cursors. We have mapped the error back to a specific query. The query is a little different - binds to a collection type. Unfortunately, we are unable to replicate this. The problem has only surfaced 2X in the last month - obviously volume/load related. The internal references to Oracle packages is interesting. Not familiar with that package. We recently upgraded from Oracle 19.12 to 19.24. I'm not 100% sure of this, but I don't think we ran into this issue prior to the upgrade. Any ideas would be much appreciated.
NOTE: :p_catMasterIDArr would never have more than 100 elements
Query:
SELECT CATMASTERID
FROM SMSYS.ITEMFAV
WHERE LOCATIONID = :p_locID
AND CATMASTERID IN (SELECT * FROM TABLE(:p_catMasterIDArr))
Oracle Error:
09/19/2024 12:03:29 ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_PICKLER", line 144
ORA-06512: at "SYS.DBMS_PICKLER", line 189
ORA-06512: at "SYS.DBMS_PICKLER", line 405
ORA-06512: at "SYS.DBMS_PICKLER", line 144
Application Env:
oracledb version: 6.5.1
Node version: node-v20.11.0-win-x64
Oracle DB version: 19.24.0.0.0
OS: Windows Server 2016
The text was updated successfully, but these errors were encountered: