别人问你数据库的某个参数是多少时,一般我们都是会直接通过show parameter,或者select value from v$parameter的方式,但是这种查询只是查到的当前会话里的参数值,而如果这个会话对参数进行过修改的情况下,查出来的值与数据库实际的值其实是不一样的。

通过字典可以查到好多带有parameter的系统视图,比如V$PARAMETER,V$SPPARAMETER,V$SYSTEM_PARAMETER,以及V$PARAMETER2和V$SYSTEM_PARAMETER2等等,那这些视图之间到底有什么区别呢?

V$PARAMETER

表示对于当前会话生效或正在起作用的参数值

V$SPPARAMETER

它显示spfile内容的信息。 如果未使用spfile启动实例,则ISSPECIFIED列为FALSE。

V$SYSTEM_PARAMETER

表示实例级别的参数信息,每开启一个新会话的时候,会话使用所有的参数都从这里进行继承。

V$PARAMETER2

V$PARAMETER一样表示对于当前会话生效或正在起作用的参数值,稍有区别的是对于那些在V$PARAMETER里的一个参数值里面有列表的情况,在本视图里就会显示成多行。

接下来会通过多个例子来详细说明他们之间的区别。

V$PARAMETER这个视图查到的结果与show parameter查到的一样,都是表示当前会话的值,我们可以通过执行计划来看到他们查到的是同样的fixed表

SQL> show parameter utl

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
create_stored_outlines                                       string
utl_file_dir                                                 string

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name             | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                  |        |       |     1 (100)|       |       |          |
|   1 |  SORT ORDER BY             |                  |      1 |    57 |     1 (100)|  2048 |  2048 | 2048  (0)|
|   2 |   COUNT                    |                  |        |       |            |       |       |          |
|   3 |    NESTED LOOPS            |                  |      1 |    57 |     0   (0)|       |       |          |
|*  4 |     FIXED TABLE FULL       | X$KSPPI          |     12 |   480 |     0   (0)|       |       |          |
|*  5 |     FIXED TABLE FIXED INDEX| X$KSPPCV (ind:1) |      1 |    17 |     0   (0)|       |       |          |
----------------------------------------------------------------------------------------------------------------

可以看到执行show parameter时,查询到的X$KSPPI和X$KSPPCV,其中X$KSPPI主要用来存放参数的名称和描述,而X$KSPPCV主要用来表示当前值(CV = CURRENT VALUE)

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

VALUE
-------------


-----------------------------------------------------------------------------------
| Id  | Operation                | Name             | E-Rows |E-Bytes| Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |        |       |     1 (100)|
|   1 |  NESTED LOOPS            |                  |      1 |    54 |     0   (0)|
|*  2 |   FIXED TABLE FIXED INDEX| X$KSPPI (ind:1)  |      1 |    37 |     0   (0)|
|*  3 |   FIXED TABLE FIXED INDEX| X$KSPPCV (ind:1) |      1 |    17 |     0   (0)|
-----------------------------------------------------------------------------------

通过比较可以看到二者查询的基表都一样,只是执行计划稍有区别。

而通过v$system_parameter去查询时,基表都变成了X$KSPPSV(SV = SYSTEM VALUE)

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

VALUE
--------------------

-----------------------------------------------------------------------------------
| Id  | Operation                | Name             | E-Rows |E-Bytes| Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |        |       |     1 (100)|
|   1 |  NESTED LOOPS            |                  |      1 |    55 |     0   (0)|
|*  2 |   FIXED TABLE FIXED INDEX| X$KSPPI (ind:1)  |      1 |    37 |     0   (0)|
|*  3 |   FIXED TABLE FIXED INDEX| X$KSPPSV (ind:1) |      1 |    18 |     0   (0)|
-----------------------------------------------------------------------------------

当一个会话里进行了某个参数修改后,其他会话查询这个视图是看不到修改结果的。

SQL> col value for a20
SQL> select value from v$parameter where name='session_cached_cursors';

VALUE
--------------------
50

SQL> alter session set session_cached_cursors=60;

Session altered.

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

VALUE
--------------------
60

而查看实例级别的参数值仍然是50

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

VALUE
--------------------
50

而V$SPPARAMETER里并没有设置这个参数

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

VALUE
--------------------

如果将spfile里的参数进行修改后会发生什么

SQL> alter system set session_cached_cursors=70 scope=spfile;

System altered.

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

VALUE
--------------------
70

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

VALUE
--------------------
50

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

VALUE
--------------------
60

可以看到三者出现了完全不一样的值,因为这个实例是通过spfile启动的,所以重启实例看看会发生什么。

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

VALUE
--------------------
70

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

VALUE
--------------------
70

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

VALUE
--------------------
70

三者都变成了spfile里设置的70,这是因为实例在启动的时候会去读取spfile的参数值,所以V$SYSTEM_PARAMETER的值就从50变成了70,而开启一个新会话是,V$PARAMETER就从实例参数里继承下来也变成了70。

对于部分参数值里有多个列表的的情况,V$PARAMETER2则可以将列表转换为多行。

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

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl, /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl

SQL> select value from v$parameter2 where name='control_files';

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl
/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl

V$SYSTEM_PARAMETER2与V$SYSTEM_PARAMETER的区别也是如此。