There are three parameters that affect handling cursors at the application level: RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS. You should set these parameters at the precompiler level.
HOLD_CURSOR by default is NO. This means that after Oracle executes a SQL statement the links to the cursor cache, memory, and parse locks are released and marked for reuse. For more details refer to Programmer's Guide to Precompilers.
RELEASE_CURSOR by default is NO. This means that after Oracle executes a SQL statement, the links to the cursor cache is maintained and not released. For more information, refer to Programmer's Guide to Precompilers.
The maximum number of open cursors per user session is set by the Oracle initialization parameter OPEN_CURSORS.
MAXOPENCURSORS specifies the initial size of the cursor cache. If a new cursor is needed and there are no free cache entries, Oracle tries to reuse an entry. Its success depends on the values of HOLD_CURSOR and RELEASE_CURSOR and, for explicit cursors, on the status of the cursor itself.
If the value of MAXOPENCURSORS is less than the number of cache entries actually needed, Oracle uses the first cache entry marked as reusable. For example, suppose the cache entry E(1) for an INSERT statement is marked as reusable, and the number of cache entries already equals MAXOPENCURSORS. If the program executes a new statement, cache entry E(1) and its private SQL area might be reassigned to the new statement. To re-execute the INSERT statement, Oracle would have to reparse it and reassign another cache entry.
Oracle allocates an additional cache entry if it cannot find one to reuse. For example, if MAXOPENCURSORS=8 and all eight entries are active, a ninth is created. If necessary, Oracle keeps allocating additional cache entries until it runs out of memory or reaches the limit set by OPEN_CURSORS. This dynamic allocation adds to processing overhead.
Thus, specifying a low value for MAXOPENCURSORS saves memory but causes potentially expensive dynamic allocations and deallocations of new cache entries. Specifying a high value for MAXOPENCURSORS assures speedy execution but uses more memory.
To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and
RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle will free up the memory for other cursors.
Consequences of setting these parameters HOLD_CURSOR=NO and RELEASE_CURSOR=YES:
This will cause Oracle to release the links and locks for each cursor after the SQL statement is executed. This means that the next time Oracle needs to issue the same SQL statement, Oracle will have to reparse the statement, and rebuild the execution plan. This will cause some performance overhead.
MAXOPENCURSORS by default is 10. This number indicates the concurrent number of open cursors that the precompiler tries to keep cached. It specifies the initial size of the cursor cache. The limit of this parameter is determined by what you set OPEN_CURSORS to. Here is the formula:
MAXOPENCURSORS + 6 <= OPEN_CURSORS
6 is the overhead cursors Oracle needs.
相关推荐
在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码
java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK ……
NULL 博文链接:https://listen-raining.iteye.com/blog/1962065
NULL 博文链接:https://zhang-zling.iteye.com/blog/980488
oracle ora-各种常见java.sql.SQLException归纳
服务器出现java.sql.SQLException No suitable driver found for 的
Q: I am working with ... I am using updateBinaryStream method of resultset to update the BLOB field but it is failing after giving following exception java.sql.SQLException: Internal Error: Unable to
ORA-28001 the password has expired密码过期
本文主要介绍了关于MySQL存储表情报错:java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\xA9\x0D\x0A…’的相关解决方法,分享出供大家参考学习,下面话不多说了,来一起看看详细的介绍: ...
有关表死锁的详细图片 博文链接:https://meteor-1988.iteye.com/blog/1568695
java.sql.SQLException: null, message from server: “Host ‘223.72.41.7’ is not allowed to connect to this MySQL server” 客户端访问时报错: 解决方法: 1,登陆服务器 mysql> use mysql; //用mysql ...
非常方便的一个oracle管理工具,数据库开发和管理员都适用,可以进行数据库的日常开发,管理,优化工作,功能很全,对于dba来说,可以进行一些高级功能比如,awr, ash, 10046等功能。
主要给大家介绍了关于MySQL存储表情时报错:java.sql.SQLException: Incorrect string value: 'xF0x9Fx92xA9x0Dx0A...'的解决方法,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面来一起看看吧。
2008/06/25 13:30:57 - oracl - ERROR : java.sql.SQLException: ORA-01722: 无效数字 2008/06/25 13:30:57 - oracl - ERROR : at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
利用时执行如 tasklist /svc 、net user 等命令时出现 ORA-24345: 出现截断或空读取错误,且文件管理功能出现问题,无法上传webshell,因此萌生了重写利用工具的想法。
mysql5.6连接驱动jaf,可处理报错java.sql.SQLException: No suitable driver
ORA-01157解决方案
今天在项目中,使用Mybatis对oracle数据库进行操作的时候,报出ORA-00911: invalid character的错误,检查了一下SQL,发现都书写正确啊,复制到plsql上执行也都没问题,这什么原因呢? 注意:这里说的是用navicat...
oracle 10g 数据库驱动下载:oracle jdbc dirver
oracle实例的内存(SGA和PGA)进行调整,优化数据库性