ITPub博客

首页 > 数据库 > Oracle > Oracle 参数的查询和修改

Oracle 参数的查询和修改

原创 Oracle 作者:zhiwliu 时间:2018-12-05 15:19:13 0 删除 编辑

我的第一篇博文,尽量写好来,如果有写的不好的地方,欢迎拍砖


测试环境

DB Version: oracle 11.2 RAC

OS: RHEL 6.x


v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter的区别

Oracle 11g里主要的查询参数的视图有v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter, dba_hist_parameter

v$parameter和v$parameter2有什么区别呢?就跟v$system_parameter和v$system_parameter2的区别是一样的。呵呵,具体为:

v$parameter里存的是每个parameter的value, 一个parameter一条记录。v$parameter2也存的是每个parameter的value, 不过在v$parameter2里是每行的name只会存一个value。以control_files为例,这个parameter会对应至少两个值,那么在v$parameter里只会有一条记录,而在v$parameter2里却会有2条记录。其实真想不明白oracle为啥要设计出一个这样的视图来,使用场景在哪?欢迎有知道的童鞋告诉我下

v$parameter和v$system_parameter的区别: v$parameter是存储当前 session 的parameter/value, 而v$system_parameter存储的是当前 instance 级别的parameter/value; 也就是说一个新建立的session, v$parameter和v$system_parameter里面的parameter/value是一致的; 因为session level parameter是从instance level parameter继承来的; 然后通过alter session可以修改v$parameter的值,但是并不会修改v$system_parameter data

我尝试从session1执行一个alter session, 然后再session1看到v$parameter值是变化了,打开session2, 看到的v$parameter的值还是老的值,这我就困惑了,不是说v$parameter能看到alter session之后的值吗?为啥session1和session2看到的结果不一样呢? 这就是因为v$parameter显示的是当前session的parameter name/value呀.

v$spparameter和v$system_parameter: 这两个view的区别在于: v$spparameter用于存储spfile 里的parameter name/value, 而v$system_parameter用于存储instance level当前的parameter name/value; 体现在哪呢?

例子

SQL> select name, value from v$system_parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


SQL> select name, value from v$spparameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


SQL> select name, value from v$parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


------------执行alter session之后三张表的查询结果

Session 1:

SQL> select userenv('sid') from dual;

USERENV('SID')

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

569


SQL> alter session set cursor_sharing='FORCE';

Session altered.


SQL> select name, value from v$parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       FORCE


SQL> select name, value from v$spparameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


SQL> select name, value from v$system_parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT



Session 2:

SQL> select userenv('sid') from dual;

USERENV('SID')

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

853


SQL> select name, value from v$parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


SQL> select name, value from v$spparameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


SQL> select name, value from v$system_parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


从这里可以看到通过alter session修改之后,当前session1里查v$parameter, cursor_sharing已经改成了FORCE, 但是另外一个session的v$parameter里的值并没有改变; 通过v$system_parameter和v$spparameter里的值也没有改变;


例子2:

仅仅修改v$spparameter

通过 alter system set ... scope=spfile , 这样修改的参数就只会进入spfile里,所以只有v$spparameter才能看到;

修改前

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME       VALUE

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

cursor_sharing       EXACT


SQL> select name, value from v$parameter where name='open_cursors';

NAME       VALUE

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

open_cursors       500


SQL> select name, value from v$system_parameter where name='open_cursors';

NAME       VALUE

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

open_cursors       500


SQL> select name, value from v$spparameter where name='open_cursors';

NAME       VALUE

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

open_cursors       500


修改后

SQL> alter system set open_cursors= 3000 scope=spfile sid='*';

System altered.


SQL> select name, value from v$system_parameter where name='open_cursors';

NAME       VALUE

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

open_cursors       500


SQL> select name, value from v$spparameter where name='open_cursors';

NAME       VALUE

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

open_cursors       3000


SQL> select name, value from v$parameter where name='open_cursors';

NAME       VALUE

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

open_cursors       500


例子3

只修改v$system_parameter

修改前

SQL> select name, value from v$spparameter where name='db_file_multiblock_read_count';

NAME       VALUE

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

db_file_multiblock_read_count

注意这里显示的是NULL,表示这个parameter value在spfile里没有设置,但是v$system_parameter里又有值,这是因为系统会根据一定的规则自己计算出一个值来


SQL> select name, value from v$system_parameter where name='db_file_multiblock_read_count';

NAME       VALUE

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

db_file_multiblock_read_count  128


SQL> select name, value from v$parameter where name='db_file_multiblock_read_count';

NAME       VALUE

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

db_file_multiblock_read_count  128


修改后

SQL> alter system set db_file_multiblock_read_count= 256 scope=memory sid='*';

System altered.


SQL> select name, value from v$spparameter where name='db_file_multiblock_read_count';

NAME       VALUE

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

db_file_multiblock_read_count


SQL> select name, value from v$system_parameter where name='db_file_multiblock_read_count';

NAME       VALUE

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

db_file_multiblock_read_count   256


SQL> select name, value from v$parameter where name='db_file_multiblock_read_count';

NAME       VALUE

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

db_file_multiblock_read_count   256

v$spparameter里的值仍然为空,v$system_parameter的值改为了256, 并且   并且 v$parameter里的值也是256. 这说明什么问题?说明如果一个参数没有执行alter session, 那么这个参数的值在v$parameter和v$system_parameter里的值是一样的;


当然也可以通过alter system set ... scope=BOTH sid='*' 让改动在v$spparameter和v$system_parameter里都能看到; 这也就是scope=BOTH/MEMORY/SPFILE的区别



oracle 11G里有355个parameter, 那到底哪个是可以通过alter session修改的,哪个是可以通过alter system修改的呢?以及那些不可以修改的呢?这个通过v$system_parameter就可以知道;

isses_modifiable: 是否能通过alter session修改. 这只有两个值: TRUE/FALSE

issys_modifiable: 是否能通过alter system修改: 这有三个值: IMMEDIATE/DEFERRED/FALSE  (IMMEDIATE表示立即生效,在当前session就生效; DEFFERED: 表示对所有当前正在连接的session都不生效,只有对这之后的连接才生效. FALSE就表示不允许alter system修改)

对于ISSYS_MODIFIABLE=DEFERRED的参数,必须通过alter system .... deferred才能修改; 否则都会报错ORA-02096

ORA-02096: specified initialization parameter is not modifiable with this option


当使用ASMM或者AMM的时候,很多 parameter value都是系统自动调整的,当你修改了某个参数之后又想把这个参数交回给系统自动来管理,那怎么办呢?其实也就是恢复默认值。可以通过SQL: alter system reset name=<value> scope=... 注意这里使用的是RESET命令。 reset的命令的意思其实就是不设置了,也就是把一个item从spfile里删除,那么如果要删除,就一定要现有这个item, 所以如果你要reset一个不存在的parameter的时候也会报错。或者在RAC里,如果设置的时候是instance by instance设置的,而reset的时候通过alter system reset ... sid='*'也会报错;

SQL> select name, value from v$spparameter where name='db_file_multiblock_read_count';

NAME   VALUE

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

db_file_multiblock_read_count

在v$spparameter里找不到这个parameter value, 说明没有设置


SQL> alter system reset db_file_multiblock_read_count scope=spfile sid='*';

alter system reset db_file_multiblock_read_count scope=spfile sid='*'

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

这里报错了


这是一种情况, 对于RAC来说,你可以instance by instance设置参数信息

这里看到每个instnace的parameter value不一样。即使value一样你也可以instance by instance设置

SQL> select sid, name, value from v$spparameter where name='db_file_multiblock_read_count';

SID NAME    VALUE

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

racaaweb1 db_file_multiblock_read_count    64

racaaweb2 db_file_multiblock_read_count    256

SQL> alter system reset db_file_multiblock_read_count scope=spfile sid='*';

alter system reset db_file_multiblock_read_count scope=spfile sid='*'

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

这里还是出错了


隐含参数

Oracle还有另外一种参数,称为隐含参数,隐含参数的名字以下划线_打头。默认情况下隐含参数不会出现在上面的几张表里,除非你修改了这些参数的值;

例子

alter system set "_undo_autotune"=TRUE;

这个语句修改了隐含参数_undo_autotune的值; 这里涉及到注意点:

1) 修改隐含参数的时候,隐含参数名字需要用双引号""括起来

2) 对于这种后面没带scope的,scope的默认值是BOTH

3) 对于这种后面没带sid的,sid的默认值是'*'

更新之后的值如下:

SQL> select name, value from v$spparameter where name='_undo_autotune';

NAME   VALUE

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

_undo_autotune   TRUE

SQL> select name, value from v$system_parameter where name='_undo_autotune';

NAME   VALUE

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

_undo_autotune   TRUE


也就是说,所有修改过的并且当前生效的参数都可以在v$parameter里看到, 不管是隐含的还是非隐含的。那么如何查询哪些没有修改过的隐含参数呢?使用一下SQL:

Hidden Parameter

set linesize 1000

SET VERIFY OFF

COLUMN parameter      FORMAT a37

COLUMN description    FORMAT a50 WORD_WRAPPED

COLUMN session_value  FORMAT a10

COLUMN instance_value FORMAT a10

SELECT a.ksppinm AS parameter,

a.ksppdesc AS description,

b.ksppstvl AS session_value,

c.ksppstvl AS instance_value

FROM   x$ksppi a,

x$ksppcv b,

x$ksppsv c

WHERE  a.indx = b.indx

AND    a.indx = c.indx

AND    a.ksppinm LIKE '%'||LOWER('&1')||'%' ESCAPE '/'

ORDER BY a.ksppinm;


补充

1:  x$ksppi: 这里存的是所有的parameter name;  x$ksppcv存的是session level的parameter value, x$ksppsv存的是instance level的parameter value; 这个通过查询x$fixed_view_definition就能看到了

2. oracle snapshot也会记录下这些parameter value,所以从这里也能看到是否有变化;

3. 当修改任何一个parameter的时候,都会在alert log里有记录,所以从这里也能看到所有被改变的parameter;

4. 到底应当怎么修改参数更合适? 是否要重启一下才更安全?哎,因人而异吧。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69900827/viewspace-2269252/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2018-12-01

  • 博文量
    1
  • 访问量
    733