Shulou(Shulou.com)06/01 Report--

1. The parameters to be adjusted for the initial installation of the 11g version of the database:

There is a default risk in the parameter setting standard. The maximum number of files allowed in the db_files1000200 database is 200. If the number is too small, the data file processes cannot be increased.

1500 the maximum number of processes in the database, which can effectively prevent the insufficient number of internal processes in the database and affect the normal operation of the business sessions

2272

Undo_retention3600900undo recovery time (in seconds). When the recovery time is reached, the rollback segment of the undo segment is set to expire to prevent the undo segment from not being recycled. Whether undo_autotuneFALSETRUE enables the database undo table space automatic optimization function, which belongs to the implicit parameter of oracle, does not enable the automatic optimization function, prevents the database undo from automatic recovery, and affects the normal operation of the business event28401.

Login attempts with incorrect passwords can result in high Library Cache Locks or row cache lockaudit_traildb,extended

Control the database audit storage location and store it in the file system directory for daily operation and maintenance. Prevent the audit catalogue from being full. _ use_adaptive_log_file_syncFALSETRUE is set by the implicit parameter _ use_adaptive_log_file_sync, and automatic switching mode is disabled when the value is FALSE. _ serial_direct_readNEVERAUTO can significantly reduce the bug of direct_path_read_ktb_debug_flags80 to avoid BUG,11g 's datagaurd. When switching, it will lead to bad index blocks, so you need to set this parameter, or patch 22241601 _ gby_hash_aggregation_enabledFALSETRUE to avoid BUG. If the hash table data is large to a certain threshold, there will be a serious tablespace upgrade [bug] job_queue_processes201000 default value is too high, which will lead to the problem of high CPU load. Set this parameter to limit the maximum number of JOB that can be initiated simultaneously session_cached_cursors20050 the number of cursors that can be cached in a single session, appropriate cache cursors, enhanced soft parsing capabilities _ smu_debug_mode1342177280 will have some performance failures and BUG (Note: the killMMOM process will not terminate the instance, the main AWR process, a new MMON process after kill will automatically use _ smu_debug_mode=134217728 to start) _ clusterwide_global_transactionsFALSETRUE when _ clusterwide_global_transactions=false Oracle will coordinate these local transactions as separate transactions through multi-phase commit coordination _ PX_use_large_poolTRUE

Parallel execution slave processes exchange data and information when they work together, and memory is permanently allocated from largepool _ ges_direct_free_res_typeCTARAHDXBB

Prevent high share_pool memory overhead _ drop_stat_segment1

Improve truncate efficiency sql92_securityTRUEFALSE when sql92_security is set to TRUE, the UPDATE/DELETE operation on the table will check whether the current user has the SELECT permission of the corresponding table when the enable_ddl_loggingTRUEFALSE parameter is set to TRUE You can record the following DDL statement in the alert log deferred_segment_creationFALSETRUE to avoid segment delay creation _ resource_manager_always_onFALSETRUE disable Oracle default enabled resource scheduling, avoid possible resmgr:cpu quantum wait event case _ resource_manager_always_offTRUEFALSE disable Oracle default enabled resource scheduling Avoid possible resmgr:cpu quantum wait events _ mv_refresh_use_statsTRUEFALSE materialized view quick refresh but sometimes performance problems _ memory_imm_mode_without_autosgaFALSETRUE when using AMM Do not set If AMM is turned off, set it to FALSE to prevent ORA-4031_bloom_filter_enabledFALSETRUE11R2 from experiencing BUG 9124206 and BUG 8361126 caused by a BLOOM filter, resulting in an ORA-00060 ORA-10387 error.

_ bloom_pruning_enabled, _ bloom_filter_enabled are set to FALSE to prevent BUG_optimizer_use_feedbackFALSETRUE from shutting down the new features of Cardinality Feedback kinetic energy _ cleanup_rollback_entries2000100 increase this parameter to achieve the effect of speeding up serial transaction recovery, (also set parallel recovery fast_start_parallel_rollback to high) _ datafile_write_errors_crash_instanceFALSETRUE11.2.0.2/3 data file (sysytem in outer space) when read and write errors are found Instance down_gc_defer_time30 is used to determine the length of time the server has to wait before writing frequently used blocks to disk, in order to reduce the process's contention for hot blocks _ gc_policy_time010DRM is unstable in 11G, there are many BUG_gc_read_mostly_lockingFALSETRUEDRM instability in 11G, and there are many BUG_gc_undo_affinityFALSETRUEDRM instability in 11G When many BUGdisk_asynch_ioTRUETRUE use file systems to store data files, it is recommended to enable asynchronous I / Ofilesystemio_optionsasynchnone when using file systems to store data files. It is recommended to turn on asynchronous IBUGdisk_asynch_ioTRUETRUE O (using ASM storage, which is enabled by default) _ optimizer_adaptive_cursor_sharingFALSETRUE to disable ACS adaptive cursor sharing, and 11.2.0.4 to disable ACS adaptive cursor sharing without closing _ optimizer_extended_cursor_sharingnoneUDO 11.2.0.4 do not close _ optimizer_extended_cursor_sharing_relnoneSIMPLE close ACS adaptive cursor sharing, 11.2.0.4 do not close

Second, the parameters to be adjusted for the initial installation of the 12c version of the database:

Parameter suggests setting standard risk by default _ optimizer_aggr_groupby_elimFALSE

When performing some aggregation operations, the existence of bug 21826068 results in an error result (Doc ID 21826068.8) for 12.1.0.2_optimizer_unnest_scalar_sqFALSE

Error ORA-600 [kkqcsfixfro:1--frooutj] (Doc ID 19894622.8) for 12.1.0.2_optimizer_reduce_groupby_keyFALSE when performing certain scalar quantum queries

External connection with binding variable, when performing group by operation, resulting in an error result of 12.1.0.2 bug 20634449_kks_obsolete_dump_threshold0

The state of concurrent session dump is enhanced in 12c, which affects performance (Doc ID 1955319.1) for 12.1.0.2_sql_plan_directive_mgmt_control0 in dump

Optimizer settings close to 12.2 to prevent excessive dynamic sampling (Doc ID 2187449.1) for 12.1.0.2_optimizer_dsdir_usage_control0

Optimizer settings close to 12.2 to prevent excessive dynamic sampling (Doc ID 2187449.1) for 12.1.0.2_optimizer_cbqt_or_expansionoff

Delete statements that Hang the number of Bug 26693987_cursor_obsolete_threshold10248192 high versions (> 1024) on 12.2.0.1 will cause database performance degradation after upgrading to 12.2 and later versions.

11.2.0.3: _ cursor_obsolete_threshold=100

11.2.0.4: _ cursor_obsolete_threshold=1024

12.1: _ cursor_obsolete_threshold=1024

12.2: _ cursor_obsolete_threshold=8192

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.