Oracle Cursor

Q：Oracle cursor与statement是什么关系？cursor如何关闭？

A：一个SQL语句，无论是Insert,Update,Delete还是Select都是游标操作，只不过Select游标指向查询结果，而其余的指向修改的目标。

除了连接可以缓存，游标也是可以缓存的，主要是避免游标的反复创建。虽然Oracle对完全相同的SQL可以共享执行计划，但是也需要去共享池查询这个SQL的信息(该SQL的Hash值是否在共享池内)。缓存游标，则进一步优化，避免了反复查询共享池的操作(soft parse)，使用的是Softer soft parse。

若使用PreparedStatements：
Oracle游标对应的是PreparedStatement，而不是ResultSet。
并且MaxOpenPreparedStatements的设置应该小于Oracle的Open_Cursor的数值。
如果PreparedStatement没有关闭，则Oracle那端的游标就没有释放。
参考：http://blog.itpub.net/29254281/viewspace-1064007/ 

http://blog.csdn.net/tianlesoftware/article/details/6638062

http://blog.csdn.net/qfs_v/article/details/2404794

https://www.2cto.com/database/201402/282081.html

http://www.linuxidc.com/Linux/2011-04/34738.htm

http://blog.csdn.net/crave_shy/article/details/20038421

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865497961356 

http://www.orafaq.com/node/758

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#LNOCI091

https://m.baidu.com/from=844b/bd_page_type=1/ssid=0/uid=0/pu=usm%402%2Csz%401320_2001%2Cta%40iphone_1_11.0_3_604/baiduid=F584A35ACA9260303DC1F27EA50003B3/w=0_10_/t=iphone/l=3/tc?ref=www_iphone&lid=8709951824705805360&order=1&fm=alop&tj=www_normal_1_0_10_title&vit=osres&m=8&srd=1&cltj=cloud_title&asres=1&title=SESSION_CACHED_CURSORS%E7%9C%9F%E7%9A%84%E8%83%BD%E9%81%BF%E5%85%8Dsoftparse%E5%90%97%3F-...&dict=32&w_qd=IlPT2AEptyoA_yiGI5WtWzA8uR_XQaC5FR4VVjlGATWg8UW7hPTjJNBsZzKvL8iP&sec=25911&di=7d45a1979fc7efe2&bdenc=1&tch=124.0.205.311.0.0&nsrc=IlPT2AEptyoA_yixCFOxXnANedT62v3IEdiURiNN1zmymEytxP4kHREsRCP8QnqXJ9ibczDXvB9QwWbzKzlq&eqid=78dff7098a5adc00100000015a256950&wd=&clk_info=%7B%22srcid%22%3A1599%2C%22tplname%22%3A%22www_normal%22%2C%22t%22%3A1512401248447%2C%22xpath%22%3A%22div-div-div-a-p%22%7D&sfOpen=1

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#LNOCI16658

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. 

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.) In the session cursor cache, Oracle manages the cached cursors using a LRU list.


I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session.

To monitor open cursors, query v$sesstat where name='opened cursors current'. 

--total cursors open, by session
select a.value, s.username, s.machine, 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'
order by a.value desc;

查询游标的使用状态详情：
select  c.user_name, c.sid, C.*, sql.sql_text  
from v$open_cursor c, v$sql sql  
where c.sql_id=sql.sql_id    -- for 9i and earlier use: c.address=sql.address  
and c.sid=967;