1.11g调优参数
1.1ASM实例参数调优
alter system set sga_max_size=2G scope=spfile;
alter system set sga_target=2G scope=spfile;
1.2数据库实例参数调优
alter database force logging;
alter database add supplemental log data;
alter system set audit_trail=none scope=spfile;
alter system set session_cached_cursors=500 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set undo_retention=1800;
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter system set deferred_segment_creation=false scope=both;
alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set resource_manager_plan='';
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_undo_affinity"=false scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
alter system set PARALLEL_FORCE_LOCAL=true scope=spfile;
alter system set parallel_max_servers=16;
alter system set optimizer_index_cost_adj=10;
alter system set optimizer_index_caching=100;
alter system set "_use_adaptive_log_file_sync"=false;
alter system set "_undo_autotune"=false;
alter system set FAST_START_PARALLEL_ROLLBACK='HIGH';
alter system set "_cleanup_rollback_entries"=400 scope=spfile ;
alter system set "_optim_peek_user_binds"=FALSE scope=both;
alter system set "_optimizer_use_feedback"=false scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
alter system set event='10511 trace name context forever,level 1:28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' scope=spfile;
alter system set "_clusterwide_global_transactions"=false scope=spfile;
alter system set "_serial_direct_read"=never;
alter system set db_files=2000 scope=spfile;
alter system set processes=3000 scope=spfile;
alter system set "_bloom_filter_enabled"=FALSE ;
alter system set "_cursor_obsolete_threshold"=200 scope=spfile;
alter system set job_queue_processes=100 scope=spfile;
alter system set "_b_tree_bitmap_plans"=false;
alter system set result_cache_max_size=0 sid='*' scope=both;
--alter system set sga_max_size=16G scope=spfile;
--alter system set sga_target=12G scope=spfile;
|
1.3统计信息收集作业
--每月20号,晚21点开始全库统计信息收集,持续最大时间240分钟,并发2
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."GATHER_DB_STATS"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DBMS_STATS.GATHER_DATABASE_STATS ();
end;',
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=20;BYHOUR=21;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2021-11-14 Asia/Shanghai', 'YYYY-MM-DD TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'gather database stats on every month20-21:00:00,maxrun-240min,degree=2',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_DB_STATS"', attribute => 'max_run_duration', value => numtodsinterval(240, 'minute'));
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_DB_STATS"', attribute => 'job_weight', value => 2);
sys.dbms_scheduler.enable( '"SYS"."GATHER_DB_STATS"' );
END;
/
|
1.4数据文件调优
set linesize 500 pagesize 500
col name format a60
select file#,bytes/1024/1024||'M',name from v$datafile;
alter database datafile 3 resize 20G;
alter database datafile 3 autoextend on;
alter database datafile 5 resize 20G;
alter database datafile 5 autoextend on;
set linesize 500 pagesize 500
col name format a60
select file#,bytes/1024/1024||'M',name from v$tempfile;
alter database tempfile 1 resize 20G;
alter database tempfile 1 autoextend off;
|
1.5集群调优
--禁用chm
crsctl modify res ora.crf -attr "AUTO_START=never" -init
crsctl modify res ora.crf -attr "ENABLED=0" -init
crsctl stop res ora.crf -init
--禁用TFA
tfactl disable
/etc/init.d/init.tfa shutdown
|
2.12c调优参数
2.1ASM实例参数调优
alter system set sga_max_size=2G scope=spfile;
alter system set sga_target=2G scope=spfile;
|
2.2数据库实例参数调优
alter database force logging;
alter database add supplemental log data;
alter system set audit_trail=none scope=spfile;
alter system set session_cached_cursors=500 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set undo_retention=1800;
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter system set deferred_segment_creation=false scope=both;
alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set resource_manager_plan='';
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_undo_affinity"=false scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
alter system set PARALLEL_FORCE_LOCAL=true scope=spfile;
alter system set parallel_max_servers=16;
alter system set optimizer_index_cost_adj=10;
alter system set optimizer_index_caching=100;
alter system set "_use_adaptive_log_file_sync"=false;
alter system set "_undo_autotune"=false;
alter system set FAST_START_PARALLEL_ROLLBACK='HIGH';
alter system set "_cleanup_rollback_entries"=400 scope=spfile ;
alter system set "_optim_peek_user_binds"=FALSE scope=both;
alter system set "_optimizer_use_feedback"=false scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
alter system set event='10511 trace name context forever,level 1:28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' scope=spfile;
alter system set "_clusterwide_global_transactions"=false scope=spfile;
alter system set "_serial_direct_read"=never;
alter system set db_files=2000 scope=spfile;
alter system set processes=3000 scope=spfile;
alter system set "_bloom_filter_enabled"=FALSE ;
alter system set "_cursor_obsolete_threshold"=200 scope=spfile;
alter system set job_queue_processes=100 scope=spfile;
alter system set "_b_tree_bitmap_plans"=false;
alter system set result_cache_max_size=0 sid='*' scope=both;
--alter system set sga_max_size=16G scope=spfile;
--alter system set sga_target=12G scope=spfile;
--12c特别关注参数
alter system set "_use_single_log_writer"='true' scope=spfile;
alter system set "_dlm_stats_collect" = 0 scope = spfile sid = '*';
alter system set "_sys_logon_delay"=0 scope=spfile;
alter system set pga_aggregate_target=8G scope=spfile;
alter system set pga_aggregate_limit=16G scope=spfile;
|
2.3统计信息收集作业
--每月20号,晚21点开始全库统计信息收集,持续最大时间240分钟,并发2
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."GATHER_DB_STATS"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DBMS_STATS.GATHER_DATABASE_STATS ();
end;',
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=20;BYHOUR=21;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2021-11-14 Asia/Shanghai', 'YYYY-MM-DD TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'gather database stats on every month20-21:00:00,maxrun-240min,degree=2',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_DB_STATS"', attribute => 'max_run_duration', value => numtodsinterval(240, 'minute'));
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_DB_STATS"', attribute => 'job_weight', value => 2);
sys.dbms_scheduler.enable( '"SYS"."GATHER_DB_STATS"' );
END;
/
|
2.4sqlnet.ora调优
注:rac集群以grid用户为准,单机以oracle用户为准
cd $ORACLE_HOME/network/admin/
vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=9
|
2.5数据文件调优
set linesize 500 pagesize 500
col name format a60
select file#,bytes/1024/1024||'M',name from v$datafile;
alter database datafile 3 resize 20G;
alter database datafile 3 autoextend on;
alter database datafile 5 resize 20G;
alter database datafile 5 autoextend on;
set linesize 500 pagesize 500
col name format a60
select file#,bytes/1024/1024||'M',name from v$tempfile;
alter database tempfile 1 resize 20G;
alter database tempfile 1 autoextend off;
|
2.6集群调优
--禁用chm
crsctl modify res ora.crf -attr "AUTO_START=never" -init
crsctl modify res ora.crf -attr "ENABLED=0" -init
crsctl stop res ora.crf -init
--禁用TFA
tfactl disable
/etc/init.d/init.tfa shutdown
|
3.19c调优参数
3.1ASM实例参数调优
alter system set sga_max_size=2G scope=spfile;
alter system set sga_target=2G scope=spfile;
3.2数据库实例参数调优
alter database force logging;
alter database add supplemental log data;
alter system set audit_trail=none scope=spfile;
alter system set session_cached_cursors=500 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set undo_retention=1800;
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter system set deferred_segment_creation=false scope=both;
alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set resource_manager_plan='';
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_undo_affinity"=false scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
alter system set PARALLEL_FORCE_LOCAL=true scope=spfile;
alter system set parallel_max_servers=16;
alter system set optimizer_index_cost_adj=10;
alter system set optimizer_index_caching=100;
alter system set "_use_adaptive_log_file_sync"=false;
alter system set "_undo_autotune"=false;
alter system set FAST_START_PARALLEL_ROLLBACK='HIGH';
alter system set "_cleanup_rollback_entries"=400 scope=spfile ;
alter system set "_optim_peek_user_binds"=FALSE scope=both;
alter system set "_optimizer_use_feedback"=false scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
alter system set event='10511 trace name context forever,level 1:28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' scope=spfile;
alter system set "_clusterwide_global_transactions"=false scope=spfile;
alter system set "_serial_direct_read"=never;
alter system set db_files=2000 scope=spfile;
alter system set processes=3000 scope=spfile;
alter system set "_bloom_filter_enabled"=FALSE ;
alter system set "_cursor_obsolete_threshold"=200 scope=spfile;
alter system set job_queue_processes=100 scope=spfile;
alter system set "_b_tree_bitmap_plans"=false;
alter system set result_cache_max_size=0 sid='*' scope=both;
--alter system set sga_max_size=16G scope=spfile;
--alter system set sga_target=12G scope=spfile;
--19c特别关注参数
alter system set "_use_single_log_writer"='true' scope=spfile;
alter system set "_sys_logon_delay"=0 scope=spfile;
alter system set pga_aggregate_target=8G scope=spfile;
alter system set pga_aggregate_limit=16G scope=spfile;
|
3.3统计信息收集作业
--每月20号,晚21点开始全库统计信息收集,持续最大时间240分钟,并发2
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."GATHER_DB_STATS"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DBMS_STATS.GATHER_DATABASE_STATS ();
end;',
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=20;BYHOUR=21;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2021-11-14 Asia/Shanghai', 'YYYY-MM-DD TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'gather database stats on every month20-21:00:00,maxrun-240min,degree=2',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_DB_STATS"', attribute => 'max_run_duration', value => numtodsinterval(240, 'minute'));
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_DB_STATS"', attribute => 'job_weight', value => 2);
sys.dbms_scheduler.enable( '"SYS"."GATHER_DB_STATS"' );
END;
/
|
3.4sqlnet.ora调优
注:rac集群以grid用户为准,单机以oracle用户为准
cd $ORACLE_HOME/network/admin/
vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=9
|
3.5数据文件调优
set linesize 500 pagesize 500
col name format a60
select file#,bytes/1024/1024||'M',name from v$datafile;
alter database datafile 3 resize 20G;
alter database datafile 3 autoextend on;
alter database datafile 5 resize 20G;
alter database datafile 5 autoextend on;
set linesize 500 pagesize 500
col name format a60
select file#,bytes/1024/1024||'M',name from v$tempfile;
alter database tempfile 1 resize 20G;
alter database tempfile 1 autoextend off;
|
3.6集群调优
--禁用chm
crsctl modify res ora.crf -attr "AUTO_START=never" -init
crsctl modify res ora.crf -attr "ENABLED=0" -init
crsctl stop res ora.crf -init
--禁用TFA
tfactl disable
/etc/init.d/init.tfa shutdown
|
附录一 参数说明
1、开启force logging
alter database force logging;
2、开启最小附加日志
alter database add supplemental log data;
3、关闭数据库标准审计
alter system set audit_trail=none scope=spfile;
4、设置会话缓存游标数500
alter system set session_cached_cursors=500 scope=spfile;
5、设置打开游标数1000
alter system set open_cursors=1000 scope=spfile;
6、设置undo保留时间1800秒
alter system set undo_retention=1800;
7、关闭自动空间管理作业
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
8、关闭sql自动优化作业
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
9、设置密码永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
10、设置密码认证失败次数无限制
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
11、关闭段延迟创建特性
alter system set deferred_segment_creation=false scope=both;
12、禁用resouce manager特性
文档 ID 2020931.1、文档 ID 1373600.1、文档 ID 1195614.1适用于解决11.1~11.2 rm引起的bug
ID 1331309.1 适用于解决10.2之后rm引起的bug
alter system set "_resource_manager_always_off"=true scope=spfile;
alter system set resource_manager_plan='';
13、关闭DRM特性
文档 ID 14588746.8、文档 ID 11875294.8、文档 ID 13457582.8、文档 ID 12777508.8、文档 ID 13583561.8、文档 ID 13397104.8、文档 ID 12834027.8、文档 ID 14409183.8、文档 ID 1946125.1适用于11.1~11.2引起的bug,12.1版本已解决bug
文档 ID 18280813.8 适用于11.2~12.2 引起的bug,12.2.0.1已解决bug
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_undo_affinity"=false scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
14、并行优化
alter system set PARALLEL_FORCE_LOCAL=true scope=spfile;
alter system set parallel_max_servers=16;
15、优化器成本估算参数优化(仅针对典型OLTP系统使用)
alter system set optimizer_index_cost_adj=10;
alter system set optimizer_index_caching=100;
16、禁用lgwr模式自适应
文档 ID 27143321.8 适用于19.1之前版本引起的bug
alter system set "_use_adaptive_log_file_sync"=false;
17、undo管理调优
文档 ID 2314796.1 介绍了即使12c禁用改参数,v$undostat仍然更新的新特性
ORA-1555 reported inspite of high undo_retention and enough UNDO space. (Doc ID 1574714.1)
alter system set "_undo_autotune"=false;
18、回滚调优
alter system set FAST_START_PARALLEL_ROLLBACK='HIGH';
alter system set "_cleanup_rollback_entries"=400 scope=spfile ;
19、优化器新特性调优
--禁用绑定变量窥测
alter system set "_optim_peek_user_binds"=FALSE scope=both;
--关闭feedback特性
alter system set "_optimizer_use_feedback"=false scope=both;
--关闭游标自适应特性
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
20、event调优
--禁用SMON OFFLINE UNDO SEGS (10511 event)
--禁用密码登录延迟认证特性 (28401 event),12c及以后版本28401 event对密码登录延迟认证特性不再生效,需使用”_sys_logon_delay”参数进行特性关闭!!!
alter system set event='10511 trace name context forever,level 1:28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' scope=spfile;
alter system set "_sys_logon_delay"=0 scope=spfile;
21、禁用全局事务
alter system set "_clusterwide_global_transactions"=false scope=spfile;
22、关闭直接路径读新特性
alter system set "_serial_direct_read"=never;
23、设置数据文件最大数
alter system set db_files=2000 scope=spfile;
24、设置最大进程数
alter system set processes=3000 scope=spfile;
25、调整ASM实例SGA
alter system set sga_max_size=2G scope=spfile;
alter system set sga_target=2G scope=spfile;
26、禁用bloom filter功能
alter system set "_bloom_filter_enabled"=FALSE ;
27、设置SQL多版本无效参数
当SQL子游标版本达到200时,不再进行软解析,而是舍弃所有游标,重新解析
alter system set "_cursor_obsolete_threshold"=200 scope=spfile;
28、指定JOB最大进程数100
alter system set job_queue_processes=100 scope=spfile;
29、设置数据库SGA大小,开启ASMM
alter system set sga_max_size=200G scope=spfile;
alter system set sga_target=100G scope=spfile;
--30、取消大小写敏感(已忽略,不作调整)
alter system set SEC_CASE_SENSITIVE_LOGON=false scope=both;
31、禁用_b_tree_bitmap_plans
alter system set "_b_tree_bitmap_plans"=false;
32、禁用12c新特性lgwr多进程
Lgwr多进程会导致严重的log file sync,将该新特性禁用,改回12c版本之前的单lgwr进程工作模式
alter system set "_use_single_log_writer"='true' scope=spfile;
33、禁用12c scm0进程
12.2 RAC DB Background process SCM0 consuming excessive CPU (Doc ID 2373451.1)
alter system set "_dlm_stats_collect" = 0 scope = spfile sid = '*';