数据库参数调优
2022/2/10 19:24:32
本文主要是介绍数据库参数调优,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
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 = '*';
这篇关于数据库参数调优的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-01为什么公共事业机构会偏爱 TiDB :TiDB 数据库在某省妇幼健康管理系统的应用
- 2024-04-26敏捷开发:想要快速交付就必须舍弃产品质量?
- 2024-04-26静态代码分析的这些好处,我竟然都不知道?
- 2024-04-26你在测试金字塔的哪一层?(下)
- 2024-04-26快刀斩乱麻,DevOps让代码评审也自动起来
- 2024-04-262024年最好用的10款ER图神器!
- 2024-04-2203-为啥大模型LLM还没能完全替代你?
- 2024-04-2101-大语言模型发展
- 2024-04-17基于SpringWeb MultipartFile文件上传、下载功能
- 2024-04-14个人开发者,Spring Boot 项目如何部署