本文探讨了数据库中动态参数和静态参数的区别，涵盖了它们在使用场景、性能影响以及测试策略等方面的内容。对于数据库管理和优化，理解这两者的差异至关重要。
一：静态参数、动态参数的概念
1）静态参数：在修改参数以后，必须重启数据才能生效的参数
如下sql来查询所有的静态参数：
select name,value from v$parameter where issys_modifiable=upper('false');
2)动态参数：在修改参数以后，不用重启也可以生效且重启后依然生效的参数
如下sql来查询所有的动态参数：
select name,value from v$parameter where issys_modifiable<>upper('false');
二：静态参数与动态参数的区别，如下图：
| 参数类型 | scope=spfile | scope= memory | scope= both | deferred |
| 静态参数 | 重启数据库生效 | 不允许使用 | 不允许使用 | 不允许使用 |
| 动态参数(immediate) | 重启数据库生效 | 立即生效,重启数据库失效 | 立即生效,重启数据库依然有效 | 不允许使用 |
| 动态参数(deferred) | 重启数据库生效 | 不允许使用 | 不允许使用 | 允许使用 |
通过上图可以很清楚的区分它们之间的区别，特别注意的是：
在动态参数中还有两种属性
immediate：
select name,value from v$parameter where issys_modifiable=upper('immediate');
deferred：
select name,value from v$parameter where issys_modifiable=upper('deferred');
通过下面语句我们也可以看出来，issys_modifiable有三个值（false即为静态参数，其他为动态参数）
SYS@ORA11GR2>select issys_modifiable,count(*) from v$parameter group by issys_modifiable;
ISSYS_MODIFIABLE COUNT(*)
-------------------- ----------
IMMEDIATE 224
FALSE 110
DEFERRED 8
SYS@ORA11GR2>
这两种类型都属于动态参数，因为都符合动态参数的定义，不过issys_modifiable=deferred的参数的属性更像静态参数（在scope子句中，只允许scope=spfile，并且重启后才生效），不过与之不同的是，这种类型的参数允许使用deferred子句（只有这种类型的参数才允许使用deferred子句），意思是，修改完成后，当前会话不生效，新开启的会话中参数修改才生效，且重启后依然生效（符合动态参数的定义）。
下面对这几种类型的参数进行一下测试，如下：
三：测试静态参数
静态参数必须使用scope=spfile
SYS@ORA11GR2>show parameter processes
NAME TYPE VALUE
------------------------- ----------- -------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150
SYS@ORA11GR2>
SYS@ORA11GR2>alter system set processes=200;
alter system set processes=200
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS@ORA11GR2>alter system set processes=200 scope=spfile;
System altered.
SYS@ORA11GR2>alter system set processes=201 scope=memory;
alter system set processes=201 scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS@ORA11GR2>alter system set processes=201 scope=both;
alter system set processes=201 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS@ORA11GR2>alter system set processes=201 deferred;
alter system set processes=201 deferred
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS@ORA11GR2>
四：测试动态参数（immediate）
注：issys_modifiable为immediate;
oracle10g job_queue_processes默认值为10，oracle11g job_queue_processes默认值为1000
1)初始默认值为1000
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 1000
SYS@ORA11GR2>
2)将其修改为500，不加scope子句(等同于scope=both，修改即生效，重启后依然生效)
SYS@ORA11GR2>alter system set job_queue_processes=500;
System altered.
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 500
SYS@ORA11GR2>
SYS@ORA11GR2>startup force;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1339852 bytes
Variable Size 645926452 bytes
Database Buffers 201326592 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 500
SYS@ORA11GR2>
3)将其修改为600，scope=spfile(修改后不立即生效，重启后生效)
SYS@ORA11GR2>alter system set job_queue_processes=600 scope=spfile;
System altered.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 500
SYS@ORA11GR2>
SYS@ORA11GR2>startup force;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1339852 bytes
Variable Size 641732148 bytes
Database Buffers 205520896 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 600
SYS@ORA11GR2>
4)将其修改为700，scope=memory(修改后立即生效，重启后失效)
SYS@ORA11GR2>alter system set job_queue_processes=700 scope=memory;
System altered.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 700
SYS@ORA11GR2>
SYS@ORA11GR2>startup force;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1339852 bytes
Variable Size 641732148 bytes
Database Buffers 205520896 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter job_queue_processes
NAME TYPE VALUE
--------------------- ----------- -------------
job_queue_processes integer 600
SYS@ORA11GR2>
5)将其修改为1000，deferred (修改后当前会话无效，新建立会话生效) issys_modifiable为immediate的动态参数无法使用deferred
SYS@ORA11GR2>alter system set job_queue_processes=1000 deferred;
alter system set job_queue_processes=1000 deferred
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SYS@ORA11GR2>
五：测试动态参数(deferred)
注：issys_modifiable为deferred;
1)初始默认值为65536
SYS@ORA11GR2>show parameter sort_area_size
NAME TYPE VALUE
-------------------- ----------- ---------------
sort_area_size integer 65536
SYS@ORA11GR2>
2)修改sort_area_size为60000，使用scope=both子句（执行结果：不允许使用scope=both）
SYS@ORA11GR2>alter system set sort_area_size=60000 scope=both;
alter system set sort_area_size=60000 scope=both
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SYS@ORA11GR2>
3)修改sort_area_size为60000，使用scope= memory子句（执行结果：不允许使用scope= memory）
SYS@ORA11GR2>alter system set sort_area_size=60000 scope=memory;
alter system set sort_area_size=60000 scope=memory
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SYS@ORA11GR2>
4)修改sort_area_size为60000，使用scope= spfile子句（执行结果：修改成功，重启生效）
SYS@ORA11GR2>alter system set sort_area_size=60000 scope=spfile;
System altered.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter sort_area_size
NAME TYPE VALUE
-------------------- ----------- ---------------
sort_area_size integer 65536
SYS@ORA11GR2>
SYS@ORA11GR2>startup force;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1339852 bytes
Variable Size 641732148 bytes
Database Buffers 205520896 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter sort_area_size
NAME TYPE VALUE
-------------------- ----------- ---------------
sort_area_size integer 60000
SYS@ORA11GR2>
5)修改sort_area_size为66666，使用deferred子句（执行结果：新连接的会话生效，在修改之前连接的会话都不生效）
SYS@ORA11GR2>alter system set sort_area_size=66666 deferred;
System altered.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter sort_area_size
NAME TYPE VALUE
-------------------- ----------- ---------------
sort_area_size integer 60000
SYS@ORA11GR2>
SYS@ORA11GR2>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocmu ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 26 13:19:51 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORA11GR2>show parameter sort_area_size
NAME TYPE VALUE
-------------------- ----------- ---------------
sort_area_size integer 66666
SYS@ORA11GR2>
小结：
1：静态参数必须使用必须重启才能生效，即使用scope=spifle子句;
2：只要使用
scope=spifle子句修改的参数，就必须重启才能生效（无论是动态还是静态）；
3：在修改参数时如果不使用scope子句，那么默认为使用scope=both子句；
4：动态参数分为两种，一种可以使用
deferred子句（即：
issys_modifiable=upper('deferred')
）
,一种不可以使用
deferred子句（
即：
issys_modifiable=upper('immediate')
）；
5：不可以使用
deferred子句的动态参数，scope可以使用任何一种类型的值，可以是用
deferred子句的参数，scope只允许等于spfile；
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/685769/viewspace-745120/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/685769/viewspace-745120/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
