open_cursors :ORA-01000 maximum open cursors exceeded

最新推荐文章于 2021-04-13 18:42:05 发布

cuixie2370

最新推荐文章于 2021-04-13 18:42:05 发布

阅读量98

点赞数

select sid,count(*) from v$open_cursor group by sid;

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

数据库环境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）,
同一时间段是怎么划分的。