Oracle中的一些优化参数说明
自动维护任务Automatic Segment Advisor和Automatic SQL Tuning Advisor
Automatic Segment Advisor和Automatic SQL Tuning Advisor两个自动维护任务，是系统自带进行空间优化和SQL优化建议的自动任务，在系统维护窗口执行，会占用大量的CPU和I/O系统资源，并且可能影响应用程序的运行性能。
检查参数自动任务auto space advisor，sql tuning advisor的状态是否设置为ENABLED，默认值为ENABLED。
1 2 3 4 5 6 7 8 9 10 11 | col client_name for a30; select client_name, status from DBA_AUTOTASK_CLIENT where client_name in('auto space advisor', 'sql tuning advisor'); CLIENT_NAME STATUS ------------------------------ ---------------- auto space advisor ENABLED sql tuning advisor ENABLED |
将auto space advisor和sql tuning advisor自动维护任务状态设置为DISABLED。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | BEGIN DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL); END; / BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / |
内置审计功能
系统内置的审计功能和统一审计功能的审计结果默认存放在SYSTEM、SYSAUX表空间。系统内置和审计功能和统一审计功能是一项非关键性功能，并且在维护不当时会产生大量的数据存在SYSTEM和SYSAUX表空间。
系统内置的审计功能和统一审计功能通常会影响到应用程序的性能，并且在应用程序会话分配、释放频繁的环境还可能会影响到数据库的可用性。对于高负载的系统不应该打开数据库内置的审计功能和统一审计功能。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | QL> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- -------------------------- audit_trail string DB -- 统计审计功能主要对象的空间分配情况： col segment_name for a20; col partition_name for a15; col tablespace_name for a20; select segment_name,partition_name, tablespace_name,bytes/1024/1024 seg_size from dba_segments where segment_name in('AUD$','AUD$UNIFIED'); SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SEG_SIZE -------------------- --------------- -------------------- ---------- AUD$ SYSTEM 5 AUD$UNIFIED SYS_P501 SYSAUX 19 -- 检查统一审计模式： col parameter for a30; col value for a15; select * from v$option where parameter='Unified Auditing'; PARAMETER VALUE CON_ID ------------------------------ --------------- ---------- Unified Auditing FALSE 0 -- 注：TRUE: 代表完全统一审计开启； -- FALSE ：代表传统审计和统一审计的混合模式。 -- 查看已启用的统一审计策略： col policy_name for a20; col entity_name for a15; select * from audit_unified_enabled_policies; POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI -------------------- --------------- --------------- ------- --- --- ORA_SECURECONFIG BY USER ALL USERS USER YES YES ORA_LOGON_FAILURES BY USER ALL USERS USER NO YES |
出于管理需要和安全需求对数据库操作进行审计，应该交由完备的安全生产管理规范，堡磊机，旁路审计设备等的结合来完成。对于高负载的系统不应该由数据库自身来对数据库操作进行审计。建议关闭数据库的传统审计和统一审计，并禁用统一审计的所有策略。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | -- 关闭数据库传统审计功能的标准审计和所有策略 SQL> noaudit all; Noaudit succeeded. -- 禁用现已有的统一审计策略，示例： SQL> NOAUDIT POLICY ORA_SECURECONFIG; Noaudit succeeded. SQL> NOAUDIT POLICY ORA_LOGON_FAILURES; Noaudit succeeded. -- 检查禁用统一审计策略成功： col policy_name for a20; col entity_name for a15; SQL> select * from audit_unified_enabled_policies; no rows selected -- 在集群的所有节点的Unified Audit功能选项设为OFF： srvctl stop database -d <unique_database_name> cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk uniaud_off ioracle srvctl start database -d <unique_database_name> -- 清理掉传统审计和统一审计的历史数据： truncate table sys.aud$; SQL> select count(*) from unified_audit_trail; COUNT(*) ---------- 98227965 EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => FALSE); END; / SQL> select count(*) from unified_audit_trail; COUNT(*) ---------- 0 |
游标相关设置参数
如果数据库的最大打开游标数设置不足，则会引起进程的事务或查询失败。如果会话可缓存的最大游标数设置过小，则可能会影响应用的性能。如果游标的version count过高，可能导致latch或者mutex等待，当游标的version count超过特定上限后，应当舍弃这个游标并重新解析。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | -- 检查数据库实例最大打开游标数： SQL> show parameter open_cursors; NAME TYPE VALUE ------------------------------------ ----------- -------------------------- open_cursors integer 300 -- 检查会话缓存最大游标数： SQL> show parameter session_cached_cursors; NAME TYPE VALUE ------------------------------------ ----------- -------------------------- session_cached_cursors integer 50 col name for a30; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_cursor_obsolete_threshold') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ---------- ------------------ -------------------- ---------- _cursor_obsolete_threshold 8192 TRUE FALSE FALSE SYS@lhrsdb> |
修改：
1 2 3 | alter system set open_cursors=1000 scope=spfile sid='*'; alter system set session_cached_cursors=300 scope=spfile sid='*'; alter system set "_cursor_obsolete_threshold"=400 scope=spfile sid='*'; |
RAC相关的内存参数
实例的SGA使用超过200G内存时需要对一些内存参数进行的调优，包括：用于发送消息的tickets资源、LMS进程的数量、内存分配粒度大小、延迟降级锁的时长。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | -- 检查LMS进程数量参数： SQL> show parameter gcs_server_processes; NAME TYPE VALUE ------------------------------------ ----------- -------------------------- gcs_server_processes integer 0 -- 检查RAC环境大内存相关的隐含参数： col name for a30; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_lm_tickets', '_ksmg_granule_size', '_gc_defer_time') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ---------- ------------------ -------------------- ---------- _gc_defer_time 0 TRUE FALSE FALSE _ksmg_granule_size 4194304 TRUE FALSE FALSE _lm_tickets 1000 TRUE FALSE FALSE |
修改：
1 2 3 4 | alter system set "_lm_tickets"=5000 scope=spfile sid='*'; alter system set gcs_server_processes=8 scope=spfile sid='*'; alter system set "_ksmg_granule_size"=134217728 scope=spfile sid='*'; alter system set "_gc_defer_time"=3 scope=spfile sid='*'; |
RAC的DRM相关参数
DRM 动态Master调整特性已知的bug较多，且如果Remaster发生超时可能导致脑裂。此外，DRM还通常会引起额外的gc * request/busy等等待事件，严重影响集群环境下的应用性能。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | col name for a30; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_gc_policy_time','_gc_undo_affinity', '_gc_bypass_readers', '_gc_read_mostly_locking') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ---------- ------------------ -------------------- ---------- _gc_bypass_readers TRUE TRUE FALSE FALSE _gc_policy_time 20 TRUE FALSE FALSE _gc_read_mostly_locking TRUE TRUE FALSE FALSE _gc_undo_affinity TRUE TRUE FALSE FALSE |
通过以下语句修改参数：
1 2 3 4 | alter system set "_gc_policy_time"=0 scope=spfile sid='*'; alter system set "_gc_undo_affinity"=false scope=spfile sid='*'; alter system set "_gc_bypass_readers"=false scope=both sid='*'; alter system set "_gc_read_mostly_locking"=false scope=spfile sid='*'; |
RAC环境DLM统计信息收集相关参数
DLM统计信息收集和管理Slave负责收集和管理与全局排队服务（GES）和全局缓存服务（GCS）相关的统计信息。仅当启用DLM统计信息收集时，此Slave才存在。DLM统计信息收集启用后，可能导致SCM0进程的CPU占用率100%，并严重影响集群上运行的应用程序的性能。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | col name for a30; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_dlm_stats_collect') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ---------- ------------------ -------------------- ---------- _dlm_stats_collect 1 TRUE FALSE FALSE |
建议将隐含参数_dlm_stats_collect的值设置为0禁用DLM统计信息收集，通过以下语句修改参数：
1 | alter system set "_dlm_stats_collect"=0 scope=spfile sid='*'; |
注意：禁用dlm_stats_collect（即设置为0）没有负面影响。
实时DML统计信息相关参数
Oracle Database 19c引入了实时统计信息，它扩展了在线统计信息的收集范围，包括了常规的DML语句。现在，可以在常规DML操作期间“即时”收集统计信息。通常，统计信息是由传统的自动统计信息收集作业收集的，该作业在数据库维护窗口内运行-只是一天一次。但是对于数据变化频繁的表，统计信息可能在DBMS_STATS作业执行之间过时，因此，实时统计的Oracle 19c新功能可以帮助优化器为此类数据变化频繁的表生成更优化的计划。批量加载操作将收集所有必要的统计信息（Oracle 19c之前的行为）-但是，实时统计信息仅收集必要的统计信息，因此不能取代传统的统计信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | col name for a50; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_optimizer_gather_stats_on_conventional_dml') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ -------------------------------------------------- ---------- ------------------ -------------------- ---------- _optimizer_gather_stats_on_conventional_dml TRUE TRUE FALSE FALSE |
可以设置这个参数到 "FALSE" 来禁止实时统计信息的收集，通过以下语句修改参数：
1 | alter system set "_optimizer_gather_stats_on_conventional_dml"=FALSE scope=spfile sid='*'; |
注意：禁用_optimizer_gather_stats_on_conventional_dml（即设置为FALSE）没有负面影响。对于统计信息易失表，应当在有代表性数据量和数据分布的时段收集统计后，锁定表和索引的统计信息，并制定统计信息易失表手工收集统计信息的策略。
表达式使用情况统计信息相关参数
12.2数据库中默认启用了 Expression statistics monitoring 功能。对于某些特定的应用程序，这可能会导致 SYSAUX 空间使用激增。
12.2为新优化器功能而引入了 Monitoring expression statistics 的操作，以便在SQL执行中收集表达式使用情况统计信息。
监视功能由参数“_column_tracking_level”控制。
默认情况下 Monitoring expression statistics 已启用，这有时会导致SYSAUX 空间使用激增。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | col name for a50; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_column_tracking_level') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ -------------------------------------------------- ---------- ------------------ -------------------- ---------- _column_tracking_level 53 TRUE FALSE FALSE |
监视功能由参数“_column_tracking_level”控制。将参数设置为17来禁止表达式使用情况统计信息的收集，通过以下语句修改参数：
1 | alter system set "_column_tracking_level"=17 scope=both sid='*'; |
要从表中清除数据请执行以下SQL：
1 2 3 4 5 6 7 | exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO(); truncate table sys.exp_head$ drop storage; truncate table sys.exp_obj$ drop storage; truncate table sys.exp_stat$ drop storage; alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online; alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online; alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online; |
加载期间在线收集索引统计信息相关参数
Oracle Database 19c引入了在加载期间在线收集索引统计信息功能，在全局分区索引维护活动期间，尽管禁用了异步全局索引维护功能，但此功能在执行过程 SYS.DBMS_STATS.POSTPROCESS_INDSTATS 时会导致库缓存锁定等待事件。此过程用于在线收集索引统计信息并更新字典。在线收集索引统计信息时会发生库缓存锁定。这是从 19c 开始引入的一个新特性，用于在加载期间在线收集索引统计信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | col name for a50; col value for a10; select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm in('_fast_index_maintenance', '_optimizer_gather_stats_on_load_index') order by translate(x.ksppinm, ' _', ' ') / NAME VALUE ISDEFAULT ISMOD ISADJ -------------------------------------------------- ---------- ------------------ -------------------- ---------- _fast_index_maintenance TRUE TRUE FALSE FALSE _optimizer_gather_stats_on_load_index TRUE TRUE FALSE FALSE |
可以设置这些参数设置为 "FALSE" 来禁止加载期间在线索引统计信息的收集，通过以下语句修改参数：
1 2 | alter system set "_fast_index_maintenance"=FALSE scope=spfile sid='*'; alter system set "_optimizer_gather_stats_on_load_index"=FALSE scope=spfile sid='*'; |
可能影响性能和可用性的Event
设置60025事件，解决JDBC连接池或长JDBC连接不断开临时表空间不释放问题，解决此问题的补丁默认安装，但是没用启用，需要设置60025 EVENT来启用。
参考：
How to Release Temporary LOB Segments without Closing the JDBC Connection (文档 ID 1384829.1)
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (文档 ID 802897.1)
28401事件用于关闭11g版本开始的数据库中用户持续输入错误密码时的延迟用户验证特性，避免用户持续输入错误密码时产生大量的row cache lock或library cache lock等待，严重时使数据库完全不能登录。
10949事件用于关闭11g版本开始的自动serial direct path read特性，避免出现过多的直接路径读，消耗过多的IO资源。
10503事件，设置绑定变量分级，最小分配长度为2000。避免绑定变量过多且长度不一，生成过多子游标
1 2 3 4 5 | alter system set event='28401 trace name context forever,level 1', '60025 TRACE NAME CONTEXT FOREVER', '10949 trace name context forever,level 1', '10503 TRACE NAME CONTEXT FOREVER, LEVEL 2000' scope=spfile sid='*'; |
总结
1、需要根据自己的数据库环境自行斟酌修改
