ORACLE 判断OPEN_CURSOR参数设置合理性

理解

V$OPEN_CURSOR, V$SESSION_CACHED_CURSOR 

 

概念：

 

OPEN_CURSOR

，

定义每个

Session

最大能够打开的游标数量。

在

init.ora

文件中定义，

可以通过

select * 

from v$parameter where name = 'open_cursors'

查询。

 

V$OPEN_CURSOR

，当前

Session

缓存的游标，而不是曾经打开的游标。

 

V$SESSION_CACHED_CURSOR

，当前

Session

已经关闭并被缓存的游标。

 

V$OPEN_CURSOR

中显示的当前

Session

游标缓存中游标，如果要精确查询当前

Session

打开的游标总

数，需要从

V$ SESSTAT

中查询。

 

select a.value, 

 

s.username, 

 

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'; 

 

 

Session Cache

的原理：

 

当设定

SESSION_CACHED_CURSOR

的值之后，

当有

parse

请求的时候，

Oracle

会从

library cache

中查

询。

如果有超过

3

次同样的

parse

请求，

这个游标将会存入

Session

的游标缓存中。

对于将来同样的查询，

就甚至不要

soft parse

，直接从

Session

的游标缓存中取。

 

 

验证：

 

登录两个

SQL*PLUS

客户端，分别为

Session test

和

Session monitor

。

 

 

1. 

检查是否参数设置，以及执行的

SQL

语句是否在

V$OPEN_CURSOR

找到。

 

Session Test:

 

SQL> show parameter session_cached_cursors; 

 

 

NAME TYPE VALUE 

 

------------------------------ 

 

session_cached_cursors integer 0 

 

SQL> select sid from v$mystat where rownum=1; 

 

SID 

 

---------- 

 

9 

 

SQL> select sid from v$mystat where rownum=1; 

 

SID 

 

---------- 

 

9 

 

 

通过如上的执行结果可以知道，当前参数设置

session_cached_cursors

的值为

0

，不缓存当前

Session

关

闭的游标。当前

Session

的

ID

为

9

。

 

 

Session Monitor: 

SQL> SELECT SID, n.NAME para_name, s.VALUE used 

 

2 FROM SYS.v_$statname n, SYS.v_$sesstat s 

 

3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count') 

 

4 AND s.statistic# = n.statistic# 5 AND SID = 9; 

 

SID PARA_NAME USED 

 

--- ------------------------------ ---------- 

 

9 opened cursors current 1 

9 session cursor cache count 0 

 

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9; 

 

SID USER_NAME SQL_TEXT 

 

--- ---------- ----------------------------------------------------------- 

9 SCOTT select sid from v$mystat where rownum=1 

 

 

通过如上的执行结果可以知道，当前在

V$OPEN_CURSOR

存储一个游标，对应

SQL

为

Session Test

执

行的最后一条语句。

V$SESSION_CACHED_CURSOR

没有存储游标。

 

 

2. 

更改参数

V$SESSION_CACHED_CURSOR

值。

 

 

Session Test:

 

SQL> alter session set session_cached_cursors = 1; 

 

Session altered. 

 

SQL> show parameter session_cached_cursors; 

 

NAME TYPE VALUE 

 

------------------------------------ ----------- ------------------------- 

session_cached_cursors integer 1 

 

 

3. 

验证如下结论。如果游标被存入

SESSION_CACHED_CURSOR

，前提是游标已经关闭，游标对应的

SQL

被执行

3

次以上。

OPEN_CURSOR

中会存储保存在

SESSION_CACHED_CURSOR

以及打开的游标

（不是精确值）。

 

 

Session Test:

 

SQL> select sid from v$mystat where rownum =1; 

 

SID 

 

---------- 

 

9 

 

SQL> select sid from v$mystat where rownum =1; 

 

SID 

 

---------- 

 

9 

 

SQL> select sid from v$mystat where rownum =1; 

 

SID 

 

---------- 

 

9 

 

 

Session Monitor:

 

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9; 

 

SID USER_NAME SQL_TEXT 

 

--- ---------- ----------------------------------------------------------- 

9 SCOTT select sid from v$mystat where rownum =1 

 

SQL> SELECT SID, n.NAME para_name, s.VALUE used 

 

2 FROM SYS.v_$statname n, SYS.v_$sesstat s 

 

3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count') 

 

4 AND s.statistic# = n.statistic# 

 

5 AND SID = 9; 

 

SID PARA_NAME USED 

 

--- ------------------------------ ---------- 

 

9 opened cursors current 1 

 

9 session cursor cache count 0 

 

 

v$open_cursor dooes not show all open cursors. it shows more than that, the best option to find the 

number of open cursors is from v$sysstat. 

通过如上的执行结果可以知道，即使同一个游标被打开

3

次，

在

SESSION_CACHED_CURSOR

的数量仍然为

0

。

 

 

下面，将会在

Session Test

中关闭游标（通过执行一条其他的语句）。

 

 

Session Test: 

SQL> select * from t where rownum!=7; 

 

no rows selected 

 

 

Session Monitor:

 

SQL> SELECT SID, n.NAME para_name, s.VALUE used 

 

2 FROM SYS.v_$statname n, SYS.v_$sesstat s 

 

3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count') 

 

4 AND s.statistic# = n.statistic# 

 

5 AND SID = 9; 

 

SID PARA_NAME USED 

 

--- ------------------------------ ---------- 

 

9 opened cursors current 1 

 

9 session cursor cache count 1 

 

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9; 

 

SID USER_NAME SQL_TEXT 

 

--- ---------- ----------------------------------------------------------- 

9 SCOTT select sid from v$mystat where rownum=1 

 

9 SCOTT select * from t where rownum!=7 

 

 

通过如上的执行结果可以知道，游标被打开

3

次之后，如果这个游标关闭之后，游标会被存储到

SESSION_CACHED_CURSOR

当中。同时，通过

OPEN_CURSOR

中显示的

SQL

可以得知，

OPEN_CURSOR

中会存储保存在

SESSION_CACHED_CURSOR

以及打开的游标（不是精确值）。

 

 

其他：

SESSION_CACHED_CURSOR

采用的是

LRU

算法，如果如果有新的游标需要缓存，而当前游

标缓存已经满，

最少使用的游标将会被清除出去。

调整

SESSION_CACHED_CURSOR

参数。

通过如下

SQL

得到从缓存中取游标以及取

PARSE

的数量，为调整作参考。

 

 

select cach.value cache_hits, prs.value all_parses, 

 

prs.value-cach.value sess_cur_cache_not_used 

 

from v$sesstat cach, v$sesstat prs, 

 

v$statname nm1, v$statname nm2 

 

where cach.statistic# = nm1.statistic# and 

 

nm1.name = 'session cursor cache hits' and 

 

prs.statistic#=nm2.statistic# and 

 

nm2.name= 'parse count (total)' and 

 

cach.sid= &sid and prs.sid= cach.sid