Oracle的打开游标(OPEN_CURSORS)

一、OPEN_CURSORS 概述

OPEN_CURSORS 指定会话一次可以拥有的打开游标（私有 SQL 区域的句柄）的最大数量。可以使用此参数来防止会话打开过多的游标。

OPEN_CURSORS参数说明

特性	描述
参数类型	Integer
默认值	50
修改方式	ALTER SYSTEM
PDB级别修改	是
参数范围	0 到 65535
初始化参数	是

将 OPEN_CURSORS 的值设置得足够高非常重要，以防止应用程序耗尽打开的游标。不同的应用程序的数量会有所不同。假设会话未打开由 OPEN_CURSORS 指定的游标数量，则将此值设置为高于实际需要的值不会增加任何开销。

二、什么是OPEN_CURSORS 

对oracle来说，什么是打开的游标

查看v$open_cursor定义

 select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$OPEN_CURSOR';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,kgllkuse, kgllksnm, user_name, kglhdpar, kglnahsh,kgllksqlid, kglnaobj, kgllkest,   decode(kgllkexc, 0, to_number(NULL), kgllkexc), kgllkctp,kgllkhdl, con_id    from x$kgllk where kglhdnsp = 0 and kglhdpar != kgllkhdl

可以看到v$open_cursor的基础是x$kgllk，这个一个关于Libraray Cache Lock的视图。也就是说，只要SQL上持有了Libraray Cache Lock，它就是一个打开的游标。

三、打开游标的内存释放规则

当某个SQL被缓存时，会持有Libraray Cache Lock，其相关的内存不会被释放或覆盖。

通过实验证明

3.1 将目标SQL游标缓存

会话1

 sqlplus sys/oracle@pdb1 as sysdbaselect p.spid,s.sid,s.serial# from v$process p,v$session s where p.addr=s.paddr and  sid=(select distinct sid from v$mystat);SPID                            SID    SERIAL#
------------------------ ---------- ----------
35997                            22       3850--执行3次
select object_name from test1 where object_id=9;
select object_name from test1 where object_id=9;
select object_name from test1 where object_id=9;
select object_name from test1 where object_id=9;

3.2 查看SQL对应的游标状态

会话2

select sid,CURSOR_TYPE from v$open_cursor where SQL_TEXT='select object_name from test1 where object_id=9';SID CURSOR_TYPE
---------- ----------------------------------------------------------------22 SESSION CURSOR CACHED

3.3 查看SQL对应的libraray cache lock

会话2

conn / as sysdbaselect kglhdadr,kglhdlmd,kglhdpmd,kglobhs0,kglobhs6 from x$kglob where kglnaobj like 'select object_name from test1 where object_id=9';KGLHDADR           KGLHDLMD   KGLHDPMD   KGLOBHS0   KGLOBHS6
---------------- ---------- ---------- ---------- ----------
00000000693483A8          1          0       4072      12144
000000006936D740          1          0       4072          0SQL> 

可以看到，父游标句柄和子游标句柄上持有NULL模式的libraray cache lock

3.4 刷新共享池

刷新之后查看该sql内存占用情况

conn / as sysdbaalter system flush shared_pool;select kglhdadr,kglhdlmd,kglhdpmd,kglobhs0,kglobhs6 from x$kglob where kglnaobj like 'select object_name from test1 where object_id=9';KGLHDADR           KGLHDLMD   KGLHDPMD   KGLOBHS0   KGLOBHS6
---------------- ---------- ---------- ---------- ----------
00000000693483A8          1          0          0          0
000000006936D740          1          0       4072          0

可以看到，当某个游标会缓存的情况下，父游标句柄和子游标的空间未被释放，但子游标的heap 0和heap 6空间被释放。

四、如何释放未被关闭的游标内存

从上面实验可以看出，刷新共享池，并不能有效释放游标所占用的空间。方法只有一个，那就是将游标关闭。我们无法直接关闭应用程序中已经打开的游标，那只能选择kill掉某个游标所对应的进程或会话。

kill进程

kill -9 35997

以下操作均在cdb下

 查看游标缓存情况

select sid,CURSOR_TYPE from v$open_cursor where SQL_TEXT='select object_name from test1 where object_id=9';
no rows selected

查看游标内存使用情况

select kglhdadr,kglhdlmd,kglhdpmd,kglobhs0,kglobhs6 from x$kglob where kglnaobj like 'select object_name from test1 where object_id=9';KGLHDADR           KGLHDLMD   KGLHDPMD   KGLOBHS0   KGLOBHS6
---------------- ---------- ---------- ---------- ----------
0000000077E2C910          0          0          0          0
0000000077EFE3F8          0          0       4072          0

再次刷新共享池，游标对应的所有内存被释放。

alter system flush shared_pool;select kglhdadr,kglhdlmd,kglhdpmd,kglobhs0,kglobhs6 from x$kglob where kglnaobj like 'select object_name from test1 where object_id=9';no rows selected

五、总结

由此可见，被缓存的游标并不是越多越好，过多缓存游标会占用大量的共享池空间，严重时会导致ORA-4031问题，关于ORA-4031报错，我们会在下一章节剖析。