本文的运行环境
SQL> select BANNER_FULL from v$version; BANNER_FULL ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
1.参数的分类
1.1 推导参数Derived Parameters
例子: SESSIONS = (1.5 * PROCESSES) + 22
缺省的每个PROCESSes 在共享池中分配8 Bytes 注册空间
验证:
SQL> select name,value from v$parameter where name in ('processes','sessions'); NAME VALUE ------------- ---------------------------------------------------------- processes 800 sessions 1224 SQL> select * from v$sgastat where name='processes'; POOL NAME BYTES CON_ID -------------- -------------------- ---------- ---------- shared pool processes 6400 1
alter system set processes=500 scope=spfile; SQL> select name,value from v$parameter where name in ('processes','sessions'); NAME VALUE ------------- -------- processes 500 sessions 772 SQL> select * from v$sgastat where name='processes'; POOL NAME BYTES CON_ID -------------- -------------------- ---------- ---------- shared pool processes 4000 1
1.2 操作系统依赖参数
1.3 可变参数
1.4 动态参数和静态参数
1.5 显示参数和隐含参数
1.6 废弃参数
SQL> select count(*) from V$OBSOLETE_PARAMETER; COUNT(*) ---------- 172
废弃参数的来源x$ksppo
SQL> select view_definition from v$fixed_view_definition where view_name='GV$OBSOLETE_PARAMETER'; VIEW_DEFINITION ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select inst_id,kspponm,decode(ksppoval,0,'FALSE','TRUE'), con_id from x$ksppo
1.7 初始化参数
1.7.1 Oracle 初始化参数可以查询 V$PARAMETER
show parameter 如何实现的
SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));TRACEFILE ----------------------------------------------------------------------------------------------------------------------------------------- /oracle/D4C/diag/rdbms/d4cdb/D4C/trace/D4C_ora_9707.trc SQL> alter session set sql_trace=true; Session altered. SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 8G sga_min_size big integer 0 sga_target big integer 0 unified_audit_sga_queue_size integer 1048576 SQL> alter session set sql_trace=false; Session altered.
trace 的内容
====================================================================================================================== SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',6,'big integer', 'unknown') TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM ======================================================================================================================
1.7.2 SQL*Plus个性化设定
启动SQL*Plus工具时，会自动调用$ORACLE_HOME/sqlplus/admin/glogin.sql 文件执行一系列的参数设置
set sqlprompt "_user @ _connect_identifier>" --登陆 SQL*Plus 就会自动在ᨀ示符前显示用户名和实例信息 set sqlprompt "&_user> " set sqlprompt "_user _privilege> "
1.7.3 V$PARAMETER的定义
SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like 'GV$PARAMETER'; VIEW_DEFINITION --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select /*+ use_hash(x y) */ x.inst_id, x.indx+1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdfl, ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'), decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg,4),4,'FALSE',decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')), decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE'), decode(bitand(ksppilrmflg/64,1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash, y.con_id from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and bitand(ksppiflg,268435456) = 0 and ((translate(ksppinm,'_','$') not like '$$%') and ((translate(ksppinm,'_','$') not like '$%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))
以“_”开头的初始化参数通常被称为隐含参数，Oracle 通常不建议修改这些参数，但是因为某些隐含参数有着特殊的功能,V$PARAMETER 视图的创建语句中我们可以发现,这个视图实际上是建立在两个底层数据字典表X$KSPPI和X$KSPPCV 之上的。
通过以下查询我们可以从内部表直接获得所有参数及其述信息：
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.KSPPDESC PDESC FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
比较常用的几个隐含参数有：
NAME VALUE PDESC
------------------------------ ---------- ------------------------------------------------
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
_offline_rollback_segments offline undo segment list
_corrupted_rollback_segments corrupted undo segment list
1.7.4 参数的可选值
V$PARAMETER_VALID_VALUES
SQL> col NUM for 9999 SQL> col name for A50 SQL> col value for A20 SQL> select * from V$PARAMETER_VALID_VALUES where name like '%cursor%'; NUM NAME ORDINAL VALUE ISDEFAULT CON_ID ----- -------------------------------------------------- ---------- -------------------- ---------------------------------------------------------------- ---------- 3458 cursor_sharing 1 FORCE FALSE 0 3458 cursor_sharing 2 EXACT TRUE 0 3458 cursor_sharing 3 SIMILAR FALSE 0 3946 cursor_bind_capture_destination 1 OFF FALSE 0 3946 cursor_bind_capture_destination 2 MEMORY FALSE 0 3946 cursor_bind_capture_destination 3 MEMORY+DISK FALSE 0 4389 cursor_invalidation 1 DEFERRED FALSE 0 4389 cursor_invalidation 2 IMMEDIATE TRUE 0 8 rows selected. col view_name for A30 select VIEW_NAME,VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like '%V$PARAMETER_VALID_VALUES'; VIEW_NAME VIEW_DEFINITION ------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GV$PARAMETER_VALID_VALUES SELECT INST_ID, PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES, ISDEFAULT_KSPVLD_VALUES, CON_ID FROM X$KSPVLD_VALUES WHERE TRANSLATE(NAME_KSPVLD_VALUES,'_','#') NOT LIKE '#%' V$PARAMETER_VALID_VALUES select num, name, ordinal, value, isdefault, con_id from GV$PARAMETER_VALID_VALUES where INST_ID = USERENV('Instance')
可以由以下SQL查询参数的可选值
SELECT INST_ID, PARNO_KSPVLD_VALUES pvalid_par#, NAME_KSPVLD_VALUES pvalid_name, VALUE_KSPVLD_VALUES pvalid_value, DECODE(ISDEFAULT_KSPVLD_VALUES, 'FALSE', '', 'DEFAULT' ) pvalid_default FROM X$KSPVLD_VALUES WHERE LOWER(NAME_KSPVLD_VALUES) LIKE LOWER('%&1%') ORDER BY pvalid_par#,pvalid_default,pvalid_Value;