10g升级至11g需要考虑的参数优化

10g升级至11g除了需要做一个详尽的计划，

需要采集10g系统的负载情况，做一个整体的把握，在升级之后，再做负载分析。

保证不会出现大的问题，sql的执行计划不会有大的变动。

参数优化方面，需要考虑下面3个方面。

à对于 deprecated Parameter in 11G

The following parameters are deprecated from 11G onwards, need suggestion for these parameters.

Name	Note
background_dump_dest	Replaced by DIAGNOSTIC_DEST	/xxxx/oracle/xxxxDB01/oradmp/bdump/	/xxxxx/oracle/xxxxDB02/oradmp/bdump/
cursor_space_for_time		FALSE	FALSE
fast_start_io_target		0	0
log_archive_local_first		TRUE	TRUE
log_archive_start		FALSE	FALSE
max_enabled_roles		150	150
parallel_automatic_tuning		FALSE	FALSE
parallel_server		FALSE	FALSE
parallel_server_instances		1	1
plsql_debug	Replaced by PLSQL_OPTIMIZE_LEVEL	FALSE	FALSE
plsql_v2_compatibility		FALSE	FALSE
remote_os_authent		FALSE	FALSE
serial_reuse		disable	disable
sql_trace		FALSE	FALSE
standby_archive_dest		?/dbs/arch	?/dbs/arch
user_dump_dest		/xxxxx/oracle/xxxxDB01/oradmp/udump/	/xxxxx/oracle/xxxxDB02/oradmp/udump/

à对于 11G 新参数

Name	Value	OL Change	Note
sec_case_sensitive_logon	FALSE	Yes	to disable password case sensitive
sec_max_failed_login_attempts	10	No	Need to set realistic value as per Site Security policy. Default is 10
result_cache_max_size	?	Yes	To use result cache feature. Can increase the memory size when it is required.
deferred_segment_creation	FALSE	Yes	Make consistent for the object in the dictionary
diagnostic_dest		Yes	Replacement for udump/bdump directorires
db_unrecoverable_scn_tracking	TRUE
_optimizer_cost_model	choose	Yes
_like_with_bind_as_equality	TRUE	Yes

 

à对于10g中已经优化的参数。

below parameters are existing in production now, need suggestions if need to tune.

_optimizer_cost_model

parallel_execution_message_size

_optim_peek_user_binds

_db_block_numa

_ksmg_granule_size

_kghdsidx_count

_enable_numa_optimization

......

在１１ｇ中一下的隐含参数建议优化，不过可以根据具体的需求来判定。

_optimizer_cost_model                string      CPU

_optimizer_skip_scan_enabled         boolean     FALSE

_optimizer_sortmerge_join_enabled    boolean     FALSE

_optimizer_use_feedback              boolean     FALSE

filesystemio_options                 string      SETALL