open_cursors

open_cursors的讨论

数据库环境9.2.0.4 RAC两个节点
错误现象：
ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded

问题描述：最近发布了多个大的应用程序后发现open_cursors不够，而且跟recursive SQL有关，我就怀疑可能是跟触发器或者跟什么表的约束有关（注意我们的表空间设置基本都是设置Uniform），后来查看，发现涉及到有个触发器，并且涉及到删除某个有约束的表，但是现在还不能完全确定。
我用select sid,count(*) from v$open_cursor group by sid;

       SID   COUNT(*)
---------- ----------
        16         39
        20         11
        21         55
        26          3
        27         49
        29          5
        30          5
        31         54
        32         22
        33         17
        39         23


SQL>  show parameter open_cursor

NAME                                 VALUE
------------------------------------ ------------------------------
open_cursors                         200

.....
没有发现很大的值
我现在打算用alter system set open_cursors=300 scope=both;暂时增加这个open_cursors。
然后用alter session set events '604 trace name errorstack level 10'；
alter session set events '1000 trace name errorstack level 3'；
跟踪一下错误，查查究竟是什么原因。如果再发生错误，等会给大家发信息上来。

我们先看看open_cursors的概念：
open_cursors--每个用户同一时刻最多在使用的cursor数

可以有两种级别来调整：
1.Tuning at the DATABASE LEVEL
可能是由于程序过多申请cursor，修改程序，如果实在不够，那么就
alter system set open_cursors=300 scope=both;
增加他。
也有可能是由于大的insert等，而被cancel掉时，或者是一个死连接，查看sqlnet.expire_time
> 0
但是盲目增大他不是一个解决办法，因为不能更好的识别所有的cursor，而且会多使用一些内存，但对性能影响多大，谁有这方面的经验。

2. Tuning at the APPLICATION LEVEL

HOLD_CURSOR是保持静态cursor(program cursor被预编译的),保持这些静态cursor可以减少重解析次数
RELEASE_CURSOR 是一些动态的cursor(oracle cursor) ，用于释放这些资源，也可以减少重解析次数。
是基于cursor cache的使用规则
我们可以通过设置HOLD_CURSOR=NO和RELEASE_CURSOR =YES来调整对open_cursors的要求。

我们来看看tom的实例来了解一下，来了解一下open_cursors 的变化：但我在后面有一些疑惑，大家一起来讨论

ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              26
opened cursors current                  9
session cursor cache hits               0
session cursor cache count             13
cursor authentications                  1


ops$tkyte@ORA920> declare
  2          type rc is ref cursor;
  3
  4          l_cursor rc;
  5  begin
  6          for i in 1 .. 100
  7          loop
  8                  for j in 1 .. 5
  9                  loop
10                          open l_cursor for 'select * from dual xx' || i;
11                          close l_cursor;
12                  end loop;
13          end loop;
14  end;
15  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative             529
opened cursors current                  9
session cursor cache hits             400
session cursor cache count            100
cursor authentications                  1

that shows I've 100 cursors in my "cache" ready to be opened faster then
normal -- but I never exceeded my 50 open cursors at a time threshold.

ops$tkyte@ORA920> create or replace procedure p( p_more in boolean default
false
)
  2  as
  3          l_x   number;
  4  begin
  5          select 1 into l_x from dual;
  6          select 2 into l_x from dual;
  7          select 3 into l_x from dual;
  8          select 4 into l_x from dual;
  9          select 5 into l_x from dual;
10          if ( p_more )
11          then
12                  select 6 into l_x from dual;
13                  select 7 into l_x from dual;
14                  select 8 into l_x from dual;
15                  select 9 into l_x from dual;
16                  select 10 into l_x from dual;
17          end if;
18  end;
19  /

Procedure created.

ops$tkyte@ORA920> connect /
Connected.
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative               9
opened cursors current                  1
session cursor cache hits               0
session cursor cache count              7
cursor authentications                  0

ops$tkyte@ORA920> exec p

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              17
opened cursors current                  6     <<<<==== +5
session cursor cache hits               1
session cursor cache count              7
cursor authentications                  5

ops$tkyte@ORA920> exec p( true )

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              24
opened cursors current                 11    <<<==== +5 more
session cursor cache hits               2
session cursor cache count              7
cursor authentications                 10

ops$tkyte@ORA920> exec p( true )

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              26
opened cursors current                 11    <<< === same +5
session cursor cache hits               3
session cursor cache count              7
cursor authentications                 11

我疑惑的是
select sid,count(*) from v$open_cursor group by sid;

       SID   COUNT(*)
---------- ----------
        16         39
        20         11
        21         55
        26          3
        27         49
        29          5
        30          5
        31         54
        32         22
        33         17
        39         23

如何看这个cursor已经到达open_cursors200了，是opened cursors current吗？能通过 v$open_cursors看出什么名堂吗？
书上写的很模糊，open_cursors究竟是基于某个用户的一个session同一时刻的最大cursor数(一个session可能在不同时间分布cursor)还是某个用户同一时刻最大cursor数（可以是多个session）,
同一时间段是怎么划分的。
希望大家能够给予指点讨论，并对处理上面错误，提出宝贵意见。

 

 

open_cursors 应该是一个session同时打开的cursor的数量上限，如果是web应用的话，设成500－1000都没问题，我一般都设得比较高，反正不会对性能有什么影响，有问题的话也容易查找cursor泄漏得地方，仅根据上面得错误，并不能断定是recursive sql导致cursor泄漏，我的经验是PLSQL小心一点儿得话一般出现cursor泄漏得可能性比较小，JAVA什么的前台程序非常容易出现cursor泄漏.看一下http://asktom.oracle.com/pls/ask ... :F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3512824755435,
仅供参考。

 

你用这句试试  应该能看出当前有多少cursor是没关闭的
select KGLLKFLG,KGLNAOBJ from X$KGLLK where user_name=???? and KGLLKFLG=8;

X$KGLLK 是 v$open_cursor 的底层表  我看见没有释放的游标和释放了的游标之间的差距就是 KGLLKFLG 的数据显示的不同 没释放的在我的试验中都是为8  
其实不光是 select 语句， insert ，delete，update 等语句在运行的时候这个 KGLLKFLG都为8(但这时间非常的短) 我猜是一个lock的等级吧

 

我们先看看V$OPEN_CURSOR的结构
select * from v$fixed_view_definition where view_name ='GV$OPEN_CURSOR';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ ------------------------------------------------------------------------
GV$OPEN_CURSOR     select inst_id,kgllkuse, kgllksnm, user_name, kglh
                               dpar, kglnahsh, kglnaobj from x$kgllk where kglhdn
                               sp = 0 and kglhdpar != kgllkhdl


SQL> select * from tab where tname = 'fanglf';

no rows selected


SQL> select  
  2   ADDR  ,
KGLLKHDL,
KGLHDPAR,
KGLNAOBJ   from x$kgllk where KGLNAOBJ   like '%from tab where tname%'  3    4    5  ;

ADDR     KGLLKHDL KGLHDPAR KGLNAOBJ
-------- -------- -------- ------------------------------------------------------------
40696E4C B78ED998 B78EDF14 select * from tab where tname = :"SYS_B_0"
40652580 B78EDF14 B78EDF14 select * from tab where tname = :"SYS_B_0"

每个SQL语句都会有一个parent cursor ，他由一个句炳（可以在库缓存中通过hash值和hash链中找到）和一个对象列表组成（保存了所有child cursor 的指针）
child cursor 也是由一个句炳和两种堆对象组成，其中用0和6来区分，0保存了一些版本信息，比如不同schema下的相同的对象名，6保存了执行计划。
我们只要关注child cursor （kglhdpar != kgllkhdl）

我在做查询时
select * from dba_objects where rownum <50000
/

在运行阶段

ADDR       KGLLKFLG KGLLKHDL KGLHDPAR KGLNAOBJ
-------- ---------- -------- -------- ------------------------------------------------------------
40696EEC          8 B7D903B0 B7D9092C select * from dba_objects where rownum <:"SYS_B_0"
40696F94          0 B7D9092C B7D9092C select * from dba_objects where rownum <:"SYS_B_0"

状态确实是8
这个8表示这个SQL语句在运行

这个cursor主要是由于应用程序端（JAVA）造成的，测试结果是
1、对于jdbc来说，每一个从Connection中产生的Statement相当于一个Session,此时会在v$session中产生或者重用一条session记录,v$open_cursor中记录的就是每个session打开的cursor数量，一个对多个父子关系。

2、除非Statement close物理关闭，否则在这个session在v$open_cursor中相关联的记录将一直存在，不会释放。 jakarta dbcp数据库连接池有一个StatementCache功能，它不会物理关闭Statement，所以造成了我们的cursor溢出，看来Oracle的Statement不能再客户端进行Cache，当我的cache size就算为1，运行一段时间cursor也会溢出，我们必须Close Statementsession来确保相应Session中打开的游标关闭。

3、系统参数open_cursor的含义就是这个Session中能够打开游标的最大数,用SQL
表示如下：
select max(cursor_count) from (select count(*) cursor_count
from v$open_cursor where user_name='???' group by sid);
         
         当这条SQL返回结果达到open_cursor参数的取值，jdbc就会抛出
         
         Exception : java.sql.SQLException: ORA-01000: maximum open
cursors exceeded

        同时系统将结束此次会话，释放所有的cursor.

由于本人对JAVA的运行机制不了解，所以对第1点我还不敢确认
对于第2点，从获取的信息上看，好象是oracle的statementcache分为硬（物理）关闭，软关闭。
在起用statementcache下，jdbc走的是软关闭，也就是说，即使你在程序中用了stmt.close，
因为cache的作用，在db端，将仍然当作open着。为了起用软关闭，导致ORA-01000，用户通常需要设置cachesize,
在oracle jdbc中呢，这些实现都比较正常，然而在hiberate中呢，它采用的是一种非正常的方法，在这一方法下，
cachesize就不起作用。或者更准确的话，cachestatement将不起作用

 

 

出现这个问题主要问题在程序部分，打开了ResultSet或Statement，不关闭造成的。

 

SQL> desc v$Open_cursor
名称                                      是否为空? 类型
----------------------------------------- -------- -------------------

SADDR                                              RAW(4)
SID                                                NUMBER
USER_NAME                                          VARCHAR2(30)
ADDRESS                                            RAW(4)
HASH_VALUE                                         NUMBER
SQL_TEXT                                           VARCHAR2(60)
http://download-west.oracle.com/ ... /ch3126.htm#1119187
This view lists cursors that each user session currently has opened and parsed.

我平常都是通过sql_text找到游标泄漏的sql语句。




这个cursor主要是由于应用程序端（JAVA）造成的，测试结果是
1、对于jdbc来说，每一个从Connection中产生的Statement相当于一个Session,此时会在v$session中产生或者重用一条session记录,v$open_cursor中记录的就是每个session打开的cursor数量，一个对多个父子关系。

每个connection是一个siesession，每个statement值相当于打开一个游标，要不然那么多session怎么 trace 啊

还有关于session cache cursor，确实有用，到了10g为什么默认还是0那？

 

 

游标数量的不够用主要跟你的程序有关系，主要是STATEMENT和RESULT没有关闭掉，还有如果你的session_cached_cursors 这个值有很大关系，缺省值为0。ORACLE游标系统自动管理的，肯定是回收的。但是具体的机制只能问ORACLE的人了。

 

 

对于象PLSQL可以实现缓存游标,这样可以设置session_cached_cursors减少软解析的次数,也就是实现softer SOFT parse

我的理解是
硬解析  --就象一个新厨师做一道菜,这个菜的做法可能要根据自己经验怎么调配起来比较可口,所以他可能需要更多的时间去分析调配技巧.
软解析 --就好比这道菜的调配方法,这个厨师已经掌握了,只需要按照原先制定的工序进行,这道菜可能就很快就可以做出来了.
软软解析 --就好比这道菜做了几套(session_cached_cursors=n)用于备用,如果有客人又要点这道菜的话,就直接拿备用的菜给他好了,是不是更快;)(这也够损的,让客人吃冷菜呵呵),不知道理解有没有出入

我们看看官方的描述:
session_cached_cursors
说明: 指定要高速缓存的会话游标的数量。对同一 SQL 语句进行多次语法分析后,
    它的会话游标将被移到该会话的游标高速缓存中。这样可以缩短语法分析的时间,
因为游标被高速缓存, 无需被重新打开。
看看实例:
SQL> alter session set session_cached_cursors=0;
SQL> select * from emp;  -- hard parse
SQL> select * from emp; -- soft parse
SQL> select * from emp; -- soft parse
SQL> alter session set session_cached_cursors=100;
SQL> select * from emp;  -- soft parse
SQL> select * from emp; -- kinder, gentler, soft parse
值范围: 0 到根据操作系统而定的值。
默认值: 0

 

关于JDBC：

当建立一个connection,在oracle中会建立一个session(a new record in V$session)；　当生成一个resultSet，oracle中会建立一个cursor(a new record in v$open_cursor)