oracle参数-open_cursors

open_cursors

指定一个会话一次可以打开的游标 (环境区域) 的最大数量, 并且限制 PL/SQL 使用的 PL/SQL 游标高速缓存的大小, 以避免用户再次执行语句时重新进行语法分析。请将该值设置得足够高, 这样才能防止应用程序耗尽打开的游标。

 

值范围: 1 - 操作系统限制值。
默认值: 64

 

open_cursors & session_cached_cursors 关系

 

SQL> select distinct sid from v$mystat;

       SID
----------
        12


SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0

这是设置session_cached_cursors为0,禁止了cache cursor的功能

我们另开一个sqlplus来看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

能在v$open_cursor里看到这条sql

执行下一条语句
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

在来看v$open_cursor有什么变化

SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1

可以看到语句变了,来看看current open cursor和session cached cursor的数目
SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                0

我们来把session_cached_cursors修改成1
SQL> alter session set session_cached_cursors=1;

Session altered.

再执行上面的语句

SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

看看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1



此处执行3次 select * from v$open_cursor where sid=12,oracle进行了3次soft parse
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

然后再执行

SQL> select distinct sid from v$mystat;

       SID
----------
        12

再来看看v$open_cursor有什么变化
SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

v$open_cursor有了两条记录,再看一下current open cursor和session cached cursor的数目

SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                1
        
果然有一句sql进了session cursor cache,"select owner from test where rownum=1"这个sql相关的cursor就被cache起来了.

再执行一条新的语句
SQL> select owner from test where rownum<2;

OWNER
------------------------------
SYS

看一下v$open_cursor
SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1

可以看到上一次的"select distinct sid from v$mystat"已经被关闭,在v$open_cursor中不可见,但是cached cursor "select owner from test where rownum=1"还能看到

这时候运行3次select distinct sid from v$mystat
SQL> select distinct sid from v$mystat;

       SID
----------
        12
SQL> select distinct sid from v$mystat;

       SID
----------
        12
SQL> select distinct sid from v$mystat;

       SID
----------
        12
        
然后再运行select owner from test where rownum<2

SQL>  select owner from test where rownum<2;

OWNER
------------------------------
SYS


再去查看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C6C628 1755089113 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

发现原来cached cursor"select owner from test where rownum=1"已经被替换出了session cursor cache,而变成了"select distinct sid from v$mystat"

我们再修改session_cached_cursors

alter session set session_cached_cursors=2;

再重复一下上面的过程,再去看v$open_cursor

SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

可以看到现在有3个cursor,看一下current open cursor和session cached cursor的数目


SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                2
        
这下有2个cached cursor,一个current open cursor.

从这个实验我们可以清楚得看到v$open_cursor视图和session_cached_cursors参数的作用.