让
Oracle的SHOW PARAMETER命令显示隐藏参数
Find internal of "show parameter" by session tracing
SQL> select * from v$version; www.2cto.com
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for
Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter session set sql_trace=true;
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zhongwc/zhongwc/trace/zhongwc_ora_32320.trc
Find internal sql statement in trace file.The major statement in trace file looks like following.
It's clear that "show parameter" actually make query on view v$parameter.
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_CL_PLUS_SHOW_PARAM
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('%optimizer%')
ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM;
Examing definition of view v$parameter
SQL> SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'V$PARAMETER';
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------
select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE ,
ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT
, HASH from GV$PARAMETER where inst_id = USERENV('Instance')
Examing definition of view gv$parameter
hidden init parameters start with character '_'
SQL> SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'GV$PARAMETER';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, decode
(bitand(ksppiflg/256,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg/65536,3),1,'I
MMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decod
e(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')), d