ORACLE推导参数Derived Parameter介绍
Oracle的推导参数(Derived Parameters)其实是初始化参数的一种。推导参数值通常来自于其它参数的运算,依赖其它参数计算得出。官方文档关于推导参数(Derived Parameters)的概念如下:
Derived Parameters
Some initialization parameters are derived, meaning that their values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.
For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.
很奇怪的是官方资料关于推导参数(Derived Parameters)的介绍非常少,几乎就是那么一点,无法从v$parameter等系统视图获取那些是推导参数(Derived Parameters),查了一些资料似乎还有下面一些参数是推导参数.
· _enqueue_hash_chains- The default value is derived from processesparameter.
·
· db_block_checkpoint_batch - This parameter specifies the number of blocks that the DBWR writes in one batch when performing a checkpoint. Setting this value too high causes the system to flood the I/O devices during the checkpoint, severely degrades performance, and increases response times--maybe to unacceptable levels.
·
· enqueue_resources - This parameter specifies the number of resources that can be locked by the lock manager. The default value is derived fromprocesses and is usually sufficient.
·
· nls_currency - This parameter is derived from nls_territory, and specifies the string to use as the local currency symbol for the L number format element.
·
· nls_date_format - This parameter is derived from nls_territory and definesthe default date format to use with the to_char and to_date functions. The value of this parameter is any valid date format mask.
·
· nls_iso_currency - Derived from nls_territory, this parameter defines the string to use as the international currency symbol for the C number format element.
·
· nls_numeric_characters - This is derived from nls_territory, and defines the characters to be used as the group separator and decimal.
·
· nls_sort - Derived from nls_language, this parameter is set to BINARY, the collating sequence for ORDER BY is based on the numeric values of the characters. A linguistic sort decides the order based on the defined linguistic sort. A binary sort is much more efficient and uses much less overhead.
·
· sessions - This parameter specifies the total number of user and system sessions, and is set to 1.1 times the value of the processes parameter.
以前在这篇文章里面ORACLE会话连接进程三者总结,我一直有个关于修改了session值后,session与process的关系公式不成立了的问题,当时一直没有搞明白,当时不知道推导参数概念,现在想想其实非常简单,其实就是因为我修改sessions这个推导参数,覆盖了推导值。下面再演示一下:
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 10
processes integer 870
SQL> show parameter session;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 400
session_max_open_files integer 10
sessions integer 962
shared_server_sessions integer
SQL> select ceil(870*1.1) +5 from dual;
CEIL(870*1.1)+5
---------------
962
同时修改参数sessions和processes,然后重启数据库,然后检查参数processes与sessions的关系。
SQL> alter system set sessions=800 scope=spfile;
System altered.
SQL> alter system set processes=600 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2096472 bytes
Variable Size 1358955176 bytes
Database Buffers 100663296 bytes
Redo Buffers 48234496 bytes
Database mounted.
Database opened.
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 10
processes integer 600
SQL> show parameter session
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 400
session_max_open_files integer 10
sessions integer 800
shared_server_sessions integer
SQL> select ceil(1.1*600)+5 from dual;
CEIL(1.1*600)+5
---------------
665
如上所示,processes与sessions的关系已经不成立了:sessions=(1.1 * processes) + 5(Oracle 10g)。主要还是因为推导参数session设置后,覆盖了推导值。这个参数值已经写入了参数文件spfile或pfile当中。
SQL> create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/init_session.ora' from spfile;
File created.
SQL>
[oracle@DB-Server dbs]$ grep session init_session.ora
*.session_cached_cursors=400
*.sessions=800
[oracle@DB-Server dbs]$ grep process init_session.ora
*.job_queue_processes=10
*.log_archive_max_processes=10
*.processes=600
参考资料:
http://www.dba-oracle.com/t_derived_parameters.htm