oracle 11g 常见参数优化记录

一笑而起 2019-03-22

2530

序号	参数	参数说明	修改理由	修改方式	复核方式	备注
1	archive_lag_target	强制切出归档日志的时间间隔	为了選免Dataguard的日志丢失 , 主库每半小时切出一次日志。	alter system set archive_lag_target=1800 scope=spfile;	show parameter archive_lag_target
2	parallel_max_servers	最大并发数	这个参数默认值与CPU相关，OLTP系统中将这个参数设置小一些，可以避免过多的并行对系统造成冲击。	alter system set parallel_max_servers=cpu_count逻辑CPU数 sid='*' scope=spfile;	show parameter parallel_max_servers
3	job_queue_processes	发起最多的job的梳理	默认値是1000 ,太高。也会导致CPU负载过高的间题。设置此參数限制同时发起的最多的iob数量。建议调整为CPU核数	alter system set job_queue_processes=32 scope=spfile;	show parameter job_queue_processes
4	recyclebin	数据库回收站	启用回收站会导致査询某些基表递归SQL性能不佳	alter system set recyclebin=off scope=spfile;	show parameter recyclebin
5	deferred_segment_creation	延时段创建，默认true。创建表后，没有插入数据时，这个表不会立即分配extent	延迟段创建会导致使用 Direct 方式的 Export 出来的 DMP 文件无法正常导入（文档 ID 1604983.1），建议关闭延迟段创建的特性。需要确认一下expdp？	alter system set deferred_segment_creation=FALSE #INSTANCE#;	show parameter deferred_segment_creation
6	DB_SECUREFILE	LOB的secure file	11g中关于LOB的算法已经改进 ,采用secure file将統減少LOB对象的enqueue等待。	alter system set DB_SECUREFILE=always scope=spfile;	show parameter DB_SECUREFILE
7	enable_ddl_logging	记录数据库中的DDL	记录数据库中的DDL。白天数据库不允许DDL, 通过启用该功能进行DDL审计。便于故障排查。	alter system set enable_ddl_logging=true scope=spfile;	show parameter enable_ddl_logging
8	db_files	数据库中可以容纳的最大的dbfile数量	修改该参数需要重启数据库,因此提前设置一个1ilt1l:的参数	alter system set db_files=800 scope=spfile;	show parameter db_files
9	session_cached_cursors	单个session中可以缓存游标的	加大cursor的复用率	alter system set session_cached_cursors=200 scope=spfile;	show parameter session_cached_cursors
10	cell_offload_processing	是否启用exadata的cell offload	不是exadata,关闭该參数	alter system set cell_offload_processing=false scope=spfile;	show parameter cell_offload_processing
11	_serial_direct_read	全表扫描时是否启用直接路径读	OLTP系统的数据尽量进缓存建议启用该功能，但是设置好上下线。	alter system set "_serial_direct_read"=never scope=spfile;	select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.KSPPINM='_serial_direct_read';
12	_use_adaptive_log_file_sync	是否启用log file的自适应切换選免10ms的代码限制,尽快的传递信息	使得 LGWR 进程写日志的方式能自动在 post/wait 和 polling 两种方式之间进行取舍，可能会导致比较严重的写日志等待（log file sync的平均单次等待时间较高）,建议关闭此功能。	alter system set "_use_adaptive_log_file_sync"=false scope=spfile;	select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.KSPPINM='_use_adaptive_log_file_sync';
13	_kttext_warning	数据文件自动预扩展的比例,默认是整个文件的5%	選免生产时间进行预扩展, 且扩展比例很大	alter system set "_kttext_warning"=1 scope=spfile;	select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.KSPPINM='_kttext_warning';
14	_high_priority_processes	CPU高优先级的进程	将lgwr进程放入高优先级 r减少log file sync等待中的CPU调度时间	alter system set "_high_priority_processes"='LMSIVKTMILGWR' scope=spfile;	select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.KSPPINM='_high_priority_processes';
15	sec_case_sensitive_logon	密码大小写敏感	忽略密码大小写	alter system set sec_case_sensitive_logon= false scope=both;	show parameter sec_case_sensitive_logon	非必需修改
16	failed_login_attempts	尝试登陆的次数	设置default的profile的尝试登陆次数为无限	alter profile default limit failed_login_attempts unlimited;	select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME=upper('failed_login_attempts');
17	password_grace_time	密码的grace时间	设置default的profile的密码grace次数为无限	alter profile default limit password_grace_time unlimited;	select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME=upper('password_grace_time');
18	password_lock_time	密码的限时间	设置default的profile的密码lock次数为无限	alter profile default limit password_lock_time unlimited;	select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME=upper('password_lock_time');
19	password_life_time	密码的使用周期时间	设置default的profile的密码生存周期为无限	alter profile default limit password_life_time unlimited;	select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME=upper('password_life_time');
20	default smallfiletablespace	采用small file	不使用big file table , big file table会造成性能问题,如数据文件头要争用,如自动预扩展Elit扩展超大空间	alter database set default smallfile tablespace;	select name,value$ from props$ where name='DEFAULT_TBS_TYPE';
21	default tablespace		指定系统的默认表空间, 選免使用system表空间	alter database default tablespace USERS;	select name,value$ from props$ where name='DEFAULT_PERMANENT_TABLESPACE';
22	utl_file_dir		提前设置好文件操作路径 ,为log miner做好准备	alter system set utl_file_dir='?l/dbs' scope=spfile;	show parameter utl_file_dir
23	forcelogging	强制日志	建立dataguard必须设置的參数	alter database force logging;	SELECT force_logging FROM v$database;
24	supplemental log data	i開外日志	有助于log miner	alter database add supplemental log data;	SELECT supplemental_log_data_min FROM v$database;
25	supplemental log data(primary key)	记录关于主键的额外日志	有助于log miner	alter database add supplemental log data (primary key) columns;	select SUPPLEMENTAL_LOG_DATA_PK from v$database;
26	supplemental log data(unique)	记录关于unique索引的额外日志	有助于log miner	alter database add supplemental log data (unique) columns;	select SUPPLEMENTAL_LOG_DATA_UI from v$database;
27	dbms_statsset_global_prefs	设置统计信息参数的属性	選免执行计划频繁跳动 ,不收集直方图	exec dbms_stats.set_global_prefs(pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 1');	select dbms_stats.get_prefs('METHOD_OPT') chk_result from dual;
28	MODIFY_SNAPSHOT_SETTINGS	设置awr收集的时间间隔	awr的snapshot间隔时间为15分钟 ,保留62天	exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>15,RETENTION=>622460);	select SNAP_INTERVAL,RETENTION from dba_hist_wr_control;
29	gather_dictionary_stats	收集数据字典信息	优化査询数据字典性能	exec dbms_stats.gather_dictionary_stats;		系统一周后晩上收集
30	GATHER_FIXED_0BJECTS_STATS;	收集类似x$等基表信息	优化査询基表性能	exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;		系统一周后晩上收集
31	28401 事件	该event可以防止当应用使用错误密码高并发登录的时候, 导致数据库library cachelock	28401事件用于关闭11g数据库中用户持续输入错误密码时的延迟用户验证特性，避免用户持续输入错误密码时产生大量的row cache lock或library cache lock等待，严重时使数据库完全不能登录。	ALTER SYSTEM SET EVENT= '28401 TRACE NAME CONTEXT FOREVER, LEVELl' scope=spfile;	show parameter event
32	_undo_autotune	是否启用自动的undo tune(此时undo retention将失效)	若由 Oracle 自动负责 undo retention，则 Oracle 会根据事务量来占用 undo 表空间，可能会形成 undo 表空间的争用，建议将其关闭。	alter system set "_undo_autotune"=false scope=both;	select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.KSPPINM='_undo_autotune';
33	_ktb_debug_flags	设置索引坏块自我修复	11g的datagaurd的bug , 在进行switchover的时候,会能引起坏块需要设置此参数 ,或者打补丁22241601。	alter system set "_ktb_debug_flags"=8 scope=both;	select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.KSPPINM='_ktb_debug_flags';
34	Flashback on	启用闪回功能	dataguard备库启用闪回，用于闪回数据	alter database flashback on;	select flashback_on from v$database;	在mount状态下设置
35	db_flashback_retention_target	设置闪回时间	可以闪回8天的数据	alter system set db_flashback_retention_target=11520 scope=spfile;	SELECT name, value FROM gv$parameter WHERE name='db_flashback_retention_target';
36	_PX_use_large_pool	并行执行的从属进程在工作时需要交换数据和信息，默认从 Shared Pool 中分配内存空间。	当 _PX_use_large_pool=TRUE 时并行进程将从 Large Pool 中分配内存，减少对共享池（Shared Pool）的争用。
37	_clusterwide_global_transactions	集群范围全局性事务(Clusterwide global transactions)是11g的新特性，其容许XA事务(XA分布式事务)在RAC中更透明。基本上，一个集群范围全局性事务是一个在RAC中的每个节点均有一个本地事务的分布式事务，当_clusterwide_global_transactions=true(默认)时，ORACLE会把这些本地事务当做一个事务对待，当_clusterwide_global_transactions=false时，ORACLE会将这些本地事务当做单独的事务通过多阶段提交协调处理。设置该参数为false不会有任何性能影响。	设置该参数值为FALSE可以解决如下等问题：Bug 13605839 ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds] ORA-600 [kdBlkCheckError]. Corruption in Rollback with Clusterwide Global Transactions in RACORA-00600: [kjuscl:!free]			参见下文详解
38	_gc_defer_time		用于确定服务器在将频繁使用的块写入磁盘之前要等待的时间长度 (以 1/1000 秒为单位)，以减少进程对热块的争用，默认为0。
39	_resource_manager_always_off---默认FALSE_resource_manager_always_on--默认TRUE	默认FALSE、TRUE，其默认是启用资源调度。将_resource_manager_always_off = true、_resource_manager_always_on = false即为禁用Oracle缺省启用的资源调度,	避免可能产生resmgr:cpu quantum等待事件情况。由于在11g中资源调度存在诸多BUG，故选择关闭。部分官档：'resmgr:cpu quantum' wait event in 11g when VKRM process is not present (文档 ID 1603996.1)Awr Reports hang, MMon slaves are waiting on resmgr:cpu quantum (文档 ID 1530676.1)
40	_cleanup_rollback_entries	该参数指定回滚时每次回滚的ENTRIES个数，默认为100	设置成400加快回滚速度
41	_optimizer_use_feedback	开始Oracle有了一种新的特性Cardinality Feedback，Cardinality Feedback是一个优化器自动优化的过程，优化器会自动修正重复执行的查询的执行计划。对于一些复杂的查询，比如多字段条件，字符串范围比较，数据SKEW等等，以及缺乏统计信息，优化器可能不能够产生一个完全准确的基数估计, 如丢失或统计数据不准确，或复杂的谓词的基数估计。cardinality feedback 就是基于这一原因而产生的。_optimizer_use_feedback参数默认是TRUE，即开启Cardinality Feedback，FALSE为关闭Cardinality feedback。	基数反馈（Cardinality Feedback）是 Oracle 11.2 中引入的关于 SQL 性能优化的新特性，该特性主要针对统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况，Cardinality 基数的计算直接影响到后续的 JOIN COST 等重要的成本计算评估，造成 CBO 选择不当的执行计划。但是该参数存在不稳定因素，可能会带来执行效率的问题，建议关闭优化器反馈。	alter system set "_optimizer_use_feedback"=FALSE #INSTANCE#;
42	_optimizer_null_aware_antijoin	是在 Oracle 11g 引入的新参数，它用于解决在反连接（Anti-Join）时，关联列上存在空值（NULL）或关联列无非空约束的问题。	但是该参数不稳定，存在较多的 Bug，为避免触发相关 Bug，建议关闭。	alter system set "_optimizer_null_aware_antijoin"=FALSE #INSTANCE#;
43	_partition_large_extents		分区使用大的初始化区（Extent）	alter system set "_partition_large_extents"=FALSE #INSTANCE#;
44	_memory_imm_mode_without_autosga	11.2.0.3开始，即使是手工管理内存方式下，如果某个POOL内存吃紧，Oracle仍然可能会自动调整内存，用这个参数来关闭这种行为		alter system set "_memory_imm_mode_without_autosga"=false sid='*' scope=spfile;
45	_optimizer_reduce_groupby_key		关闭COST查询转换。	alter system set "_optimizer_reduce_groupby_key"=off scope=spfile sid='*';
46	_b_tree_bitmap_plans	对于OLTP系统，Oracle可能会将两个索引上的ACCESS PATH得到的rowid进行bitmap操作再回表	这种操作有时逻辑读很高，对于此类SQL使用复合索引才能从根本上解决问题。	alter system set “_b_tree_bitmap_plans”=false sid=’*’ scope=spfile;
47	_optimizer_extended_cursor_sharing_rel、_optimizer_extended_cursor_sharing、_optimizer_adaptive_cursor_sharing	自适应游标共享(Adaptive Cursor Sharing： ACS)	Oracle 建议在非技术指导下，将其关闭掉。官档：Bug 11657468 - Excessive mutex waits with adaptive cursor sharing (文档 ID 11657468.8)Bug 12333007 - Dump on kkocscopycolstats (文档 ID 12333007.8)	alter system set "_optimizer_extended_cursor_sharing_rel"=none;alter system set "_optimizer_extended_cursor_sharing"=none;alter system set "_optimizer_adaptive_cursor_sharing"=false;
48	_gc_policy_time	DRM（Dynamic Resource Mastering）负责将 Cache 资源 Remaster 到频繁访问这部分数据的节点上，从而提高 RAC 的性能。	但是 DRM 在实际使用中存在诸多 Bug，频繁的 DRM 会引发实例长时间 Hang 住甚至是宕机，建议关闭 DRM。	alter system set "_gc_policy_time"=0 #INSTANCE# scope=spfile;
49	_gc_undo_affinity		建议关闭集群 Undo Affinity，降低集群交互，避免触发相关 BUG。	alter system set "_gc_undo_affinity"=FALSE #INSTANCE# scope=spfile;
50	PARALLEL_FORCE_LOCAL	在11.2以前，当用户发出并行查询，Oracle会将负载分配到所有的实例上，而在11.2中，Oracle提供了初始化参数，可以限制并行只运行在当前实例上。	默认关闭，建议开启，以便降低集群间的数据交互。 http://blog.itpub.net/4227/viewspace-684317/select * from gv$px_process; select /+ parallel(4) / count(*) from tabletest;这样测
51	Audit_trail	默认开启，db模式	建议关闭审计	alter system set audit_trail = none scope=spfile;	show parameter audit;
52	_dbms_sql_security_level	该参数有0,1,2共3个值(默认值为1)，0关闭dbms_sql包的安全检查，打开光标级别为1的要求执行/绑定和解析用户id是相同的。2级是更严格的和需要id和角色是相同的所有操作,如绑定、描述、执行、提取等。	如果出现ORA-29471的错误之后，只有断开当前这个session，然后重新连接数据库才可以正常调用DBMS_SQL包。若是想封闭security check，须要将一个隐含参数_dbms_sql_security_level设置成0	alter system set "_dbms_sql_security_level"=FALSE #INSTANCE# scope=spfile;
53	_bloom_pruning_enabled、_bloom_filter_enabled	布隆过滤器(Bloom Filter)算法在Oracle Database 10gR2中被引入到Oracle数据库中,布隆过滤能够使用极低的存储空间，存储海量数据的映射，从而可以提供快速的过滤机制。	11R2会遇到一个BLOOM过滤器导致的BUG 9124206和BUG 8361126，出现ORA-00060 ORA-10387错误，_bloom_pruning_enabled、_bloom_filter_enabled均设为FALSE避免BUG详细错误如下：ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal)	alter system set "_bloom_pruning_enabled"=FALSE #INSTANCE# scope=spfile;alter system set "_bloom_filter_enabled"=FALSE #INSTANCE# scope=spfile;
54	_gc_read_mostly_locking	read mostly locking机制，能减少读访问的消息传递和CPU消耗.参数默认是TRUE，即开启read mostly locking	但是写访问就会比传统的cache fusion locking机制消耗更多的IO。read-mostly的特性是给那些读很多，写很少的系统来启用比较合适。	alter system set "_gc_read_mostly_locking"=FALSE #INSTANCE# scope=spfile;
55	_smu_debug_mode	默认为0，会有部分性能故障及BUG需要设置"_smu_debug_mode" = 134217728来避免，另通过设置_smu_debug_mode值可以很好的实现在undo自动管理模式下,指定事务在特定的回滚段,在某些极限情况下,可以通过该操作来减少回滚段争用。	官档：Minact-Scn Master-Status: Grec-Scn Messages In Trace File (文档 ID 1361567.1)SMON Is Waiting On Latch High CPU Resource consumption MMON blocking SMON (文档 ID 1496453.1)	alter system set "_smu_debug_mode"=134217728;kill MMON进程(注：kill MMOM进程不会终止实例，AWR主要的进程，kill之后一个新的MMON进程会自动使用_smu_debug_mode=134217728启动)kill -9

oracle

文章转载自一笑而起，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

oracle 11g 常见参数优化记录

评论

相关阅读