查詢初始化引數的方法（六）

查詢初始化引數的方法很多，比如SHOW PARAMETER，或查詢V$PARAMETER等，這裡簡單總結一下。

這一篇描述CREATE PFILE的方法檢查初始化引數。

查詢初始化引數的方法（一）：http://yangtingkun.itpub.net/post/468/484669

查詢初始化引數的方法（二）：http://yangtingkun.itpub.net/post/468/484729

查詢初始化引數的方法（三）：http://yangtingkun.itpub.net/post/468/485623

查詢初始化引數的方法（四）：http://yangtingkun.itpub.net/post/468/485664

查詢初始化引數的方法（五）：http://yangtingkun.itpub.net/post/468/485719

 

 

前面介紹了很多種方法，這些方法都是在資料庫中查詢初始化引數的設定。其實還有一種方法更加的一目瞭然，就是CREATE PFILE的方式：

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/test/spfiletest.ora
SQL> create pfile = '/export/home/oracle/inittest1.ora' from spfile;

檔案已建立。

SQL> host more /export/home/oracle/inittest1.ora
test2.__db_cache_size=541065216
test1.__db_cache_size=524288000
test2.__java_pool_size=4194304
test1.__java_pool_size=4194304
test2.__large_pool_size=4194304
test1.__large_pool_size=4194304
test1.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
test2.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
test2.__pga_aggregate_target=260046848
test1.__pga_aggregate_target=260046848
test2.__sga_target=775946240
test1.__sga_target=775946240
test2.__shared_io_pool_size=0
test1.__shared_io_pool_size=0
test2.__shared_pool_size=218103808
test1.__shared_pool_size=234881024
test2.__streams_pool_size=0
test1.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/test/adump'
*.audit_trail='db'
*.cluster_database=true
*.cluster_database_instances=3
*.compatible='11.1.0.0.0'
*.control_files='+DATA/test/controlfile/current.529.684067899'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='test'
*.diagnostic_dest='/data/oracle'
test1.instance_number=1
test2.instance_number=2
*.log_archive_config=''
*.log_archive_dest_1='LOCATION=/data/oracle/oradata/test/archivelog'
*.open_cursors=300
test1.open_cursors=500
test2.open_cursors=700
*.pga_aggregate_target=256901120
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
test1.remote_login_passwordfile='SHARED'
*.sga_target=772800512
test1.thread=1
test2.thread=2
*.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'

使用了這個語句，所有的SPFILE中設定的初始化引數設定都一目瞭然。

除了CREATE PFILE FROM SPFILE外，11g還增加了CREATE PFILE FROM MEMORY選項，使得使用者可以直接從資料庫當前生效的引數來生成PFILE檔案，利用這個方法，就可以解決上一篇文章最後介紹的PFILE中設定的例項級引數覆蓋SPFILE中資料庫級引數的情況：

SQL> create pfile = '/export/home/oracle/inittest1.ora' from memory;

檔案已建立。

SQL> host more /export/home/oracle/inittest1.ora
# Oracle init.ora parameter file generated by instance test1 on 06/12/2009 15:18:46
test1.__db_cache_size=500M
test2.__db_cache_size=516M
*.__java_pool_size=4M
*.__large_pool_size=4M
*.__oracle_base='/data/oracle' # ORACLE_BASE set from environment
*.__pga_aggregate_target=248M
*.__sga_target=740M
*.__shared_io_pool_size=0
test1.__shared_pool_size=224M
test2.__shared_pool_size=208M
*.__streams_pool_size=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._complex_view_merging=TRUE
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._dimension_skip_null=TRUE
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times='0'
*._ksb_restart_policy_times='60'
*._ksb_restart_policy_times='120'
*._ksb_restart_policy_times='240' # internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model='CHOOSE'
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=240
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_group_by_placement=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_undo_cost_change='11.1.0.6'
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_minus_intersect=TRUE
*._px_pwg_enabled=TRUE
*._px_ual_serial_input=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._sqltune_category_parsed='DEFAULT' # parsed sqltune_category
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*.audit_file_dest='/data/oracle/admin/test/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.cluster_database_instances=3
*.compatible='11.1.0.0.0'
*.control_files='+DATA/test/controlfile/current.529.684067899'
test1.core_dump_dest='/data/oracle/diag/rdbms/test/test1/cdump'
test2.core_dump_dest='/data/oracle/diag/rdbms/test/test2/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='test'
*.diagnostic_dest='/data/oracle'
test1.instance_number=1
test2.instance_number=2
*.log_archive_config=''
*.log_archive_dest_1='LOCATION=/data/oracle/oradata/test/archivelog'
*.log_buffer=4197376 # log buffer update
*.open_cursors=500
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=245M
*.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
test1.remote_login_passwordfile='SHARED'
test2.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=3808K
*.sga_target=740M
*.skip_unusable_indexes=TRUE
test1.thread=1
test2.thread=2
test1.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'

但是這種方法顯然也存在問題，首先從得到的結果看，裡面除了包含使用者設定的初始化引數外，還包含了大量的隱含引數。如果這些隱患引數是Oracle用於自動調整的雙下劃線引數也不奇怪，問題是大部分都是Oracle不推薦設定的單下劃線隱含引數。不過這倒是一個檢視Oracle隱患引數的好辦法。

另外一個問題是，這個方法只對當前例項設定的引數有效，而無法合併多個例項的設定，對比上面的OPEN_CURSORS引數的設定和下面查詢的結果就可以發現這個問題：

SQL> select sid, name, value
  2  from v$spparameter
  3  where name = 'open_cursors';

SID        NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
*          open_cursors                   300
test1      open_cursors                   500
test2      open_cursors                   700

SQL> select inst_id, name, value
  2  from gv$system_parameter
  3  where name = 'open_cursors';

   INST_ID NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
         1 open_cursors                   500
         2 open_cursors                   500

顯然CREATE PFILE獲取的結果和SPFILE中的設定並不相符，下面修改一下初始化引數OPEN_CURSORS的值：

SQL> alter system set open_cursors = 400;

系統已更改。

SQL> create pfile = '/export/home/oracle/inittest1.ora' from memory;

檔案已建立。

SQL> host more /export/home/oracle/inittest1.ora | grep open_cursors
*.open_cursors=400

從這個結果可以看到，CREATE PFILE獲取的PFILE只對當前例項有效，雖然獲取的結果包含多個例項的設定，但是這些設定可能和其他例項上的真正設定並不相符。