数据库性能优化：Oracle实例级别和会话级别的参数设置辨析

2017年12月26日

杨廷琨

1545

【云和恩墨，提供7*24最专业的数据恢复（Oracle，MySQL，SQL server）服务，致力于为您的数据库系统做最后一道安全防护！服务热线：010-59007017-7030】数据恢复|数据库运维|性能优化|安全保障

Oracle实例级别和会话级别的参数有时候容易混淆，必须清晰的明确这两者的差别，才能在种种变更中成竹在胸。我们来看看V$PARAMETER 和 V$SYSTEM_PARAMETER 视图的区别。

一般在查询初始化参数的时候都习惯性的使用 SHOW PARAMETER，也就是查询 V$PARAMETER 视图。但是有些时候查询V$PARAMETER视图得到的结果并不准确。

我们通过query_rewrite_enabled这个参数来做一个验证。

SQL> show parameter query_rewrite_enabled
NAME TYPE VALUE
------------------------------------ ----------- --------------------
query_rewrite_enabled string TRUE
SQL> select name, value
2 from v$parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ----------------------------
query_rewrite_enabled TRUE
SQL> select name, value
2 from v$system_parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ----------------------------
query_rewrite_enabled TRUE

这时候如果在会话级修改 query_rewrite_enabled 这个初始化参数：

SQL> alter session set query_rewrite_enabled = false;
会话已更改。
SQL> show parameter query_rewrite_enabled
NAME TYPE VALUE
------------------------------------ ----------- -------------------
query_rewrite_enabled string FALSE
SQL> select name, value
2 from v$parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ---------------------------
query_rewrite_enabled FALSE
SQL> select name, value
2 from v$system_parameter
3 where name = 'query_rewrite_enabled';
NAME VALUE
---------------------------------------- ---------------------------
query_rewrite_enabled TRUE

可以看到，show parameter 和查询 v$parameter 视图的结果都是 FALSE，而刚才做的修改只是会话级，并没有修改系统的初始化参数。

我们应该形成的知识常识：V$PARAMETER 视图反映的是初始化参数在当前会话中生效的值，而 V$SYSTEM_PARAMETER 反映的才是实例级上的初始化参数。

再来看看延迟参数修改的情况：

SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on
SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on
SQL> alter system set recyclebin = off deferred scope = memory;
系统已更改。
SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on
SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ------------------------------------
recyclebin OFF

结果和前面的恰好反过来，v$parameter 视图中的结果没有发生变化，而 v$system_parameter 视图的结果变成了 OFF。

这是因为延迟修改对数据库中当前存在的会话不生效，因此反映当前会话情况的 v$parameter 视图结果不变，而对于系统而言，初始化参数已经改变，而且所有新建会话的参数也会改变，所以 v$system_parameter 视图的结果发生了改变。

SQL> CONN YANGTK/YANGTK@YTK111
已连接。
SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ---------------------------
recyclebin OFF
SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';
NAME VALUE
---------------------------------------- ---------------------------
recyclebin OFF

根据这两个例子可以获得的常识是：利用 V$PARAMETER 视图获取系统的启动初始化参数是不准确的，应该从 V$SYSTEM_PARAMETER 视图来获取。