我的第一篇博文，尽量写好来，如果有写的不好的地方，欢迎拍砖 测试环境 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. 到底应当怎么修改参数更合适? 是否要重启一下才更安全？哎，因人而异吧。
