hiswapna
asked on
Too many cursors opened on using sys_ref cursor as out parameter in a procedure
All Configurable parameters for the application are stored in a table, various modules of the application read these parameter values through one single interface.
The interface module runs in a separate background thread, which reads the values of table periodically ( every 3 min) through a Stored Procedure, The stored Procedure uses a ref cursor to return data back to the application.
It was noticed that insetad of using the cusror cache, a new cursor is opened everytime a refresh happens, and these cursors stay open for long time. The Stored proceure itself uses simple select statement without any where clauses to read values from table.(select a,b,c from table abc;)
How do we avoid opening cursor every time the stored proc is executed by the background job. How do we improve the performance.
Regards
Swapna
The interface module runs in a separate background thread, which reads the values of table periodically ( every 3 min) through a Stored Procedure, The stored Procedure uses a ref cursor to return data back to the application.
It was noticed that insetad of using the cusror cache, a new cursor is opened everytime a refresh happens, and these cursors stay open for long time. The Stored proceure itself uses simple select statement without any where clauses to read values from table.(select a,b,c from table abc;)
How do we avoid opening cursor every time the stored proc is executed by the background job. How do we improve the performance.
Regards
Swapna
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.