【Oracle】session_cached_cursors和open_cursors的调整

在进行数据迁移，数据导入慢，经查询可能为session_cached_cursors和open_cursors配置异常导致，遂调整参数值

查看参数

-查看session_cached_cursors

SQL> show parameter session_cached_cursors;

查看使用情况

SELECT 'session_cached_cursors' PARAMETER,
       LPAD(VALUE, 5) VALUE,
       DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE,
       value
  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') || '%',
       value
  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');

对session_cached_cursors参数进行修改。

SQL> show parameter session_cached_cursors;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50
SQL> alter system set session_cached_cursors=100 scope=spfile;


System altered.


SQL> show parameter session_cached_cursors;

NAME                                 TYPE        VALUE

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

session_cached_cursors               integer     50

 

open_cursors参数配置

查看游标打开最大值设置

SQL> show parameter open_cursors;

oracle 默认open_cursors 为300
设置open_cursors值

alter system set open_cursors = 1000;
alter system set open_cursors = 1000 scope = spfile; 

若不带scope 对应默认scope为both

重启数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 
Total System Global Area  542851072 bytes

Fixed Size                  2254952 bytes

Variable Size             163579800 bytes

Database Buffers          373293056 bytes

Redo Buffers                3723264 bytes

Database mounted.

Database opened.

 

SQL> show parameter session_cached_cursors;

 

NAME                                 TYPE        VALUE

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

session_cached_cursors               integer     100

参考：
如何正确设置session_cached_cursors参数
oracle参数open_cursors和session_cached_cursor详解
session_cached_cursors过低 导致 Execute to Parse %过低