某核心系统oracle 19c rac 最佳实践参数

--database  参数优化：
alter system set "_and_pruning_enabled"=FALSE;
alter system set "_b_tree_bitmap_plans"=FALSE;
alter system set "_bloom_filter_enabled"=FALSE;
alter system set "_cleanup_rollback_entries"=5000 scope=spfile;
alter system set "_clusterwide_global_transactions"=FALSE scope=spfile;
alter system set "_cursor_obsolete_threshold"=100  scope=spfile;
alter system set "_datafile_open_errors_crash_instance"=FALSE;
alter system set "_datafile_write_errors_crash_instance"=FALSE;
alter system set "_disable_file_resize_logging"=TRUE;
alter system set "_disable_last_successful_login_time"=TRUE;
alter system set "_drop_stat_segment"=1;
alter system set "_optim_peek_user_binds"=FALSE;
alter system set "_optimizer_adaptive_cursor_sharing"=FALSE;
alter system set "_optimizer_ads_use_result_cache"=FALSE;
alter system set "_optimizer_cartesian_enabled"=FALSE;
alter system set "_optimizer_enhanced_join_elimination"=FALSE;
alter system set "_optimizer_extended_cursor_sharing"='NONE';
alter system set "_optimizer_extended_cursor_sharing_rel"='NONE';
alter system set "_optimizer_partial_join_eval"=FALSE;
alter system set "_optimizer_unnest_scalar_sq"=FALSE;
alter system set "_optimizer_use_feedback"=FALSE;
alter system set "_partition_large_extents"='FALSE';
alter system set "_PX_use_large_pool"=TRUE scope=spfile;
alter system set "_report_capture_cycle_time"=0;
alter system set "_rollback_segment_count"=2000;
alter system set "_securefiles_concurrency_estimate"=50 scope=spfile;
alter system set "_shared_pool_reserved_pct"=20 scope=spfile;
alter system set "_smu_debug_mode"=134217728;
alter system set "_sql_plan_directive_mgmt_control"=0;
alter system set "_sys_logon_delay"=0 scope=spfile;
alter system set "_use_single_log_writer"='TRUE' scope=spfile;
alter system set "db_cache_advice"='OFF';
alter system  set parallel_force_local=TRUE scope=spfile sid='*';
alter system set event='10849 trace name context forever,level 1','10949 trace name context forever,level 1','19823 trace name context forever,level 90','28401 trace name context forever,level 1','44951 trace name context forever,level 1024' scope=spfile;
alter system  set db_files=8000 scope=spfile sid='*';
alter system  set client_statistics_level=OFF scope=spfile;
alter system  set control_file_record_keep_time=39  scope=spfile sid='*';
alter system set "_undo_autotune" = false sid='*';
alter system  set undo_retention=7200   scope=spfile sid='*';    
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_undo_affinity"=FALSE scope=spfile;
alter system set "_use_adaptive_log_file_sync"='FALSE';
alter system  set deferred_segment_creation = false   scope=spfile sid='*';
alter system  set ARCHIVE_LAG_TARGET=1800 scope=spfile sid='*';
alter system  set DB_BLOCK_CHECKING=MEDIUM   scope=spfile sid='*';
alter system  set DB_BLOCK_CHECKSUM=FULL     scope=spfile sid='*';
alter system  set DB_LOST_WRITE_PROTECT=TYPICAL      scope=spfile sid='*';
alter system  set ENABLE_DDL_LOGGING=true scope=spfile sid='*';     
alter system  set OPTIMIZER_ADAPTIVE_PLANS=false scope=spfile sid='*';
alter system  set OPTIMIZER_ADAPTIVE_STATISTICS=false scope=spfile sid='*';
ALTER SYSTEM SET "_lm_drm_disable"=7 SCOPE=BOTH SID='*';
alter system set sec_max_failed_login_attempts=100 scope=spfile;
alter system set parallel_execution_message_size=32768  sid='*' scope=spfile;
alter  system  set audit_trail=none  scope=spfile;
alter system set result_cache_max_size= 0  sid='*';                                
alter system set resource_manager_plan='force:' sid='*' scope=spfile;
alter  system set  "_optimizer_mjc_enabled"=false   sid='*' scope=spfile;
alter system set enable_ddl_logging=true sid='*';
alter system set "_gc_bypass_readers"=FALSE sid='*' scope=spfile;
alter system set "_gc_read_mostly_locking"=false sid='*' scope=spfile;
alter system set "_keep_remote_column_size"=true sid='*' scope=spfile;
alter system set "_ash_size"=254M sid='*';
alter  system set  trace_enabled=false sid='*'  sid='*';
alter system set "_column_tracking_level"=1 scope=spfile;
alter system set max_dump_file_size='1024M' scope=spfile sid='*';
alter system set "_enable_spacebg"= false sid='*';
alter system set parallel_max_servers=cpu_count乘以2   ;




--asm参数优化：

alter system set memory_target=4g scope=spfile;
alter system set memory_max_target=4g scope=spfile;
alter system set processes=2000 scope=spfile;




--关闭auto space advisor
--系统自带进行空间优化的job，在系统维护窗口执行，会占用系统资源，并且可能导致bug
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor',
                               operation   => NULL,
                               window_name => NULL);
END;
/

select client_name, status
  from DBA_AUTOTASK_CLIENT
 where client_name = 'auto space advisor';


--关闭sql turning
--系统自带的sql自动调优的job，在系统维护窗口执行，会占用系统资源，并且可能导致bug
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',
                               operation   => NULL,
                               window_name => NULL);
END;
/

select client_name, status
  from DBA_AUTOTASK_CLIENT
 where client_name = 'sql tuning advisor';


--关闭ORACLE_OCM的job
--没有使用ORACLE_OCM，建议关闭其job
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

select JOB_NAME, ENABLED, STATE
  from dba_scheduler_jobs
 where owner = 'ORACLE_OCM';


--修改统计信息收集的默认属性
--关闭12c的自适应统计信息收集，并且将统计信息收集柱状图的方式修改为repeat重复上次
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS','OFF') ;
exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');




--收集数据字典，固定对象以及sys用户的统计信息
--数据导入完成以后，不仅需要对应用的用户，也需要对系统的数据字典的统计信息进行收集以生成更准确的执行计划
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

--修改awr报告的保留周期
--建议讲AWR保留时间设置为30天（一个月），以备回溯一个月内的性能问题，默认只保留7天时间太短


exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>60*24*60); 


-- 禁用Optimizer Statistics Advisor Task可能导致SYSAUX表空间中SM/ADVISOR组件消耗过大
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(‘AUTO_STATS_ADVISOR_TASK’,‘EXECUTE’,NULL,'DISABLE');
END;
/




--允许低版本客户端连接,oracle用户操作
vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
复制