数据库性能问题分析优化

客户反应应用频繁卡住，只能通过重启应用才能恢复，一天发生若干次。

问题初步分析处理

从最近得到的三个awr报告看，等待事件基本在于“DB CPU”，“db file sequential read”，“log file sync”，“log file switch (checkpoint ncomplete)”，具体如下。
第一次：

第二次：

第三次：

根据这几个现象等待现象，初步断定大量查询导致IO或CPU瓶颈，大批量更新插入导致redo_log checkpoint来不及刷入磁盘。由于awr取数时间范围较大，需要更进一步确认。

进一步排查过程

检查出现性能问题时的等待事件，整个堵塞的等待时间基本上是“log file sync”和“enq: TX - row lock contention”。



“log file sync”发生有多种情况，如CPU资源紧张，lgwr进程获得不了响应的CPU时间片；时间段业务量太大，产生redo 过多，如批量DML redo 日志文件太小或者组数不够。
“enq: TX - row lock contention”基本上由于性能或执行效率低导致的tx锁。
检查pdb中堵塞的session_id，“log file sync”在pdb中没查到，在容器中查询发现：


基本上“LGWR”来不及导致了堵塞，tx锁慢的源头是job任务，如下：

通过查询sql_id得出job任务的sql语句：

这些语句平时执行比较快，跟上面“log file sync”堵塞有非常大的相关。进一步分析awr和ash报告，从堵塞时间来看，是7点51分开始的，获取ash报告7点50分后面10分钟的报告情况：

看大部分等待事件为“db file parallel read”，“db file sequential read”，“log file sync”，跟io相关，瓶颈在于file文件1,3,4一般这三个文件都是系统文件system、sysaux、和undo的文件，所以当时有系统任务在执行影响了数据库性能。
比较正常情况和异常情况下的awr报告，异常情况如下：

正常情况如下：

对比sql执行语句，最耗时间的sql时间翻了三倍，sql_id为75c73u54hy17g的sql语句执行次数翻了三倍，等待事件top10前面几个都一样

与上面ash情况一起分析，主要在于系统层面的io资源消耗，需要考虑到12c的执行sql自动收集统计信息的功能，检查优化器：

检查优化器参数，所以需要调整参数：
alter system set “_optimizer_ads_use_result_cache”=FALSE;
alter system set “_optimizer_dsdir_usage_control”=0;
alter system set “_optimizer_use_feedback”=FALSE;
alter system set optimizer_adaptive_plans=FALSE;
_optimizer_ads_use_result_cache：解决“Result Cache: RC Latch”
_optimizer_dsdir_usage_control：解决多表联接SQL在12c中的解析时间很长
_optimizer_use_feedback:解决基数反馈导致后续计划不佳，“db file sequential read”top事件
optimizer_adaptive_plans：解决sql执行sql自动收集统计信息的功能。

检查redo日志情况：

当前日志是1G，日志只有三个，建议在增加三个日志。
检查alert日志，当天有统计信息维护任务


初步优化完毕，后续问题发生频率减少，但还是偶发业务卡顿，而且在业务非高峰期也发生，通过awr看，发生等待事件时，网络延迟较高伴随着tx锁等待。

经过了解业务层和数据库间的有一层代理，并且业务层和数据库间超过5分钟就会断开，所以。
推荐解决方案:

1. 因为业务和数据库都处在内网环境，对外不可见，可以修改为业务服务器直连数据库，减少中间环节。
2. sqlnet.ora 增加如下配置:

SQLNET.INBOUND_CONNECT_TIMEOUT=300
SQLNET.EXPIRE_TIME=10

经过上面逐步优化完，业务恢复稳定，偶尔卡顿也消失。

优化措施汇总

通过上面分析具体问题汇总如下：
1、redo日志文件在业务繁忙来不及切换，checkpoint来不及。
2、sql执行时sql自动收集统计信息的功能开启着，影响性能消耗，系统消耗性能较大。
3、问题当天正在执行统计信息收集，进一步影响性能，周六周日每天6点开始，持续20个小时收集统计信息。

4、数据库层面针对有网络防火墙的优化。

建议解决如下：
1、调整优化器参数：
alter system set “_optimizer_ads_use_result_cache”=FALSE;
alter system set “_optimizer_dsdir_usage_control”=0;
alter system set “_optimizer_use_feedback”=FALSE;
alter system set optimizer_adaptive_plans=FALSE;

1. 增加日志文件，由3个增加到6个

alter database add logfile group 4 (‘/odata/data/RON1122N3/datafile/redo_log4.log’) size 1024M;
alter database add logfile group 5 (‘/odata/data/RON1122N3/datafile/redo_log5.log’) size 1024M;
alter database add logfile group 6 (‘/odata/data/RON1122N3/datafile/redo_log6.log’) size 1024M;

1. 修改统计信息收集时间，放在业务空闲时间

查询执行开始时间和周期：
SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c.window_name = w.window_name
AND c.optimizer_stats = ‘ENABLED’;
修改周六、周日统计信息收集开始时间及执行周几，如需修改22点开始，执行周期5个小时：
–SATURDAY
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => ‘“SYS”.“SATURDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
VALUE => ‘FREQ=daily;BYDAY=SAT;BYHOUR=22;BYMINUTE=0;BYSECOND=0’);
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“SATURDAY_WINDOW”’,
attribute => ‘DURATION’,
value => numtodsinterval(300,‘minute’));
END;
/

–SUNDAY
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => ‘“SYS”.“SUNDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
VALUE => ‘FREQ=daily;BYDAY=SUN;BYHOUR=22;BYMINUTE=0;BYSECOND=0’);
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“SUNDAY_WINDOW”’,
attribute => ‘DURATION’,
value => numtodsinterval(300,‘minute’));
END;
/

4、修改sqlnet.ora 增加如下配置，减少中间防火墙的影响:
SQLNET.INBOUND_CONNECT_TIMEOUT=300
SQLNET.EXPIRE_TIME=10

官方参考文档

1、Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)
2、Customer RecommendedHigh “Latch Free” Waits with Contention on ‘Result Cache: RC Latch’ when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1)
3、High parse time in 12c for multi-table join SQL with SQL plan directives enabled (Doc ID 20807398.8)
4、“Connection timed out”, ORA-03113, and “Closed Connection” on Idle Connections (Doc ID 1060344.1)