ORA-4025错误

$ oerr ora 4025
04025, 00000, "maximum allowed library object lock allocated for %s%s%s%s%s"
// *Cause: Too many active locks for the object has been allocated. This error
//         can be a result of too many cursors kept open per session.
// *Action: Close cursors, reduce session_cached_cursors value.

根据上面的描述，我们知道ORA-4025错误，通常可能由于打开的游标太多，导致某个库缓存对象上，加载的active locks 太多，因为library object lock的分配数量达到了上限时发生错误。 
一般可以通过关闭游标或者 减少session_cached_cursors的值缓解这个问题。

1.open_cursors 指一个session最多打开的游标数。用一下sql来确定这个值的大小

SQL> select max(count(*)) max_cacheable_cursors
from (select p.kglobt18 schema# -- parsing schema number
from sys.x$kglcursor p
where p.kglobt12 > 2 -- enough parse_calls
union all
select s.kglntsnm schema# -- authorized schema number
from sys.x$kglcursor c, sys.x$kglsn s
where c.kglobt12 > 2
and s.kglhdadr = c.kglhdadr)
group by schema#;

MAX_CACHEABLE_CURSORS
---------------------
1487

open_cursors:该参数含义是同一个session同时打开最多在使用的游标数。在Oracle10.2.0.1.0版本中默认为300。

session_cached_cursors：SESSION_CACHED_CURSORS, 就是说的是一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解 析的问题)，软解析同硬解析一样,比较消耗资源.所以这个参数非常重要。在Oracle10.2.0.1.0版本中默认为20。

2.使用下面的sql判断session_cached_cursors的使用情况。如果使用率为100%则增大这个参数值。

SQL> Select 'session_cached_cursors' Parameter,
    Lpad(Value, 5) Value,
    Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
From (Select Max(s.Value) Used
      From V$statname n, V$sesstat s
     Where n.Name = 'session cursor cache count'
      And s.Statistic# = n.Statistic#),
    (Select Value From V$parameter Where Name = 'session_cached_cursors')
Union All
Select 'open_cursors',
    Lpad(Value, 5),
    To_Char(100 * Used / Value, '990') || '%'
From (Select Max(Sum(s.Value)) Used
      From V$statname n, V$sesstat s
     Where n.Name In
        ('opened cursors current', 'session cursor cache count')
      And s.Statistic# = n.Statistic#
     Group By s.Sid),
    (Select Value From V$parameter Where Name = 'open_cursors'); 

PARAMETER              VALUE                USAGE
---------------------- -------------------- -----
session_cached_cursors    50                 100%
open_cursors            5000                 100%

3.查看hit cache的次数

SQL> select name,value as "hit" from v$sysstat where name='session cursor cache hits';

NAME                                                                    hit
---------------------------------------------------------------- ----------
session cursor cache hits                                            596295

4.查看所有的parse的次数

SQL> select name,value from v$sysstat where name='parse count (total)';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                  612690

5.如果这两个值越接近，那就越好，因为每次parse的时候都是从cache 中hit的，所以就省去了cpu的parse的时间如果前者的值比后者的值小的多，内存够大，就可以考虑增加session_cached_cursors的值了

6.查看语句

SQL> show parameter cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     5000
session_cached_cursors               integer     50

6.由于是初始化参数所以得加上scope=spfile修改如下，注意：session_cached_cursors < open_cursors

SQL> alter system set open_cursors=10000 scope=both;

System altered.

SQL> alter system set session_cached_cursors=1400 scope=spfile;

System altered.

7.查看语句

SQL> show parameter cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     10000
session_cached_cursors               integer     50

8.oracle scope=both和scope=spfile区别；Oracle spfile就是动态参数文件，里面设置了Oracle 的各种参数。所谓的动态，就是说你可以在不关闭数据库的情况下，更改数据库参数，记录在spfile里面。更改参数的时候，有4种scope选项，scope就是范围。scope=spfile 仅仅更改spfile里面的记载，不更改内存，也就是不立即生效，而是等下次数据库启动生效。有一些参数只允许用这种方法更改，scope=memory 仅仅更改内存，不改spfile。也就是下次启动就失效了scope=both 内存和spfile都更改，不指定scope参数，等同于scope=both

9.关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

10.开启数据库

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            2382368632 bytes
Database Buffers         1.0402E+10 bytes
Redo Buffers               40861696 bytes
Database mounted.
Database opened.

11.查看语句

SQL> show parameter cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     10000
session_cached_cursors               integer     1400