日期:2014-05-16 浏览次数:20430 次
Find internal of "show parameter" by session tracing
SQL> select * from v$version; 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
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;
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')
hidden
init parameters start with character '_'
<