安装GC之前需要调整的几个参数

安装GC的过程中会报出如下的错误，为了防止报错后调整，可以预先将这三个参数提前进行调整。

1.报错截图内容


2.需要调整的三个参数
1）session_cached_cursors
关于该参数的含义请参见Oracle官方文档中的描述
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams189.htm#REFRN10195

SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.

This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are re-executed by a user.

提示要求将参数session_cached_cursors调整到200以上（含200），该参数默认值是20。

2）job_queue_processes
关于该参数的含义请参见Oracle官方文档中的描述
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams089.htm#REFRN10077

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package.

Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.

提示要求将参数job_queue_processes调整到10以上（含10），该参数默认值是5。

3）open_cursors
关于该参数的含义请参见Oracle官方文档中的描述
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams138.htm#REFRN10137

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.

It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.

提示要求将参数open_cursors调整到300以上（含300），该参数默认值是50。

3.确认该三个参数的系统默认值
SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
session_cached_cursors               integer     20

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
job_queue_processes                  integer     5

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
open_cursors                         integer     50


4.通过修改spfile文件的方法调整该三个参数
SQL> alter system set session_cached_cursors = 201 scope=spfile;

System altered.

SQL> alter system set job_queue_processes = 11 scope=spfile;

System altered.

SQL> alter system set open_cursors = 301 scope=spfile;

System altered.

5.停起数据库使调整后的参数生效

6.最后的确认
SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
session_cached_cursors               integer     201

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
job_queue_processes                  integer     11

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
open_cursors                         integer     301

7.小结
本文谈到的该报错内容并不是致命的，既可以在安装GC之前对数据库进行调整，亦可在看到这个报错之后调整，调整完成之后安装可以继续完成。

Good luck.

secooler
10.07.08

-- The End --