作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG数据库运维(如安装迁移,性能优化、故障应急处理等)
公众号:老苏畅谈运维
欢迎关注本人公众号,更多精彩与您分享。
客户的一个重要业务时不时就会有人反应卡顿,让协助帮忙排查数据库上有没有问题,该业务使用的是oracle 12c单机数据库(12.1.0.2版本),未使用CDB,没有安装任何补丁。
客户提供了几个具体卡顿时间点,我收取了相关的awr和ash报告,查看数据库性能问题。
收取awr和ash相关命令如下:
sqlplus / as sysdba
sys@orcl(254)> @?/rdbms/admin/awrrpt.sql
sys@orcl(254)> @?/rdbms/admin/ashrpt.sql
--根据提示,输入你要查看的时间段,生成html。
一、分析awr报告
查看awr报告整体负载:
整体负载看,压力不大。
addr信息显示硬解析和latch free问题比较突出:
客户的这套系统,确实硬解析比较高,
硬解析应是sql语句问题,未使用绑定变量。
查看top 10 等待事件:
latch free等待事件过高,这是要关注的重点。
二、分析ash报告
前面快速过了一下awr报告,现在来看一下ash相关报告:
ash报告与awr报告基本上吻合,问题都是出在latch free 这个等待事件,而且我们看到了一些会话被阻塞了,这才是业务卡顿的原因。
三、分析latch free产生原因
查看dba_hist_active_sess_history视图,分析卡顿时间点相关的阻塞会话
select a.sample_time,a.sql_id,a.event,a.BLOCKING_SESSION,a.BLOCKING_SESSION_SERIAL# from dba_hist_active_sess_history a where a.SAMPLE_TIME>=to_date('2024-06-25 16:10:00','yyyy-mm-dd hh24:mi:ss')
and a.SAMPLE_TIME<=to_date('2024-06-25 16:50:00','yyyy-mm-dd hh24:mi:ss')
and a.BLOCKING_SESSION is not null
可以看到不少sql由于latch free原因被阻塞了。
通过查看latch id找到latch name(latch id为p2)
select a.sql_id,a.event,a.p2 from dba_hist_active_sess_history a where a.SAMPLE_TIME>=to_date('2024-06-25 16:10:00','yyyy-mm-dd hh24:mi:ss')
and a.SAMPLE_TIME<=to_date('2024-06-25 16:50:00','yyyy-mm-dd hh24:mi:ss')
and a.event='latch free'
and a.BLOCKING_SESSION is not null
select * from v$latchname where latch#=559;
559 这个对象是Result Cache: RC Latch
从上面的步骤排查,可以知道latch free 产生的对象是Result Cache: RC Latch。
查看result cache相关参数,result cache处于开启状态
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_ads_use_result_cache boolean FALSE
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 188768K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
查看mos,有发现了一个相关bug:High “Latch Free” Waits with Contention on ‘Result Cache: RC Latch’ when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1)
解决方法有两种:
一是通过打补丁One-off Patch 26436717 can be requested and applied to fix the issue.
二是通过隐含参数禁止Automatic Dynamic Statistics使用result cache
alter system set “_optimizer_ads_use_result_cache” = FALSE scope=both;
我选择了直接修改隐含参数:alter system set “_optimizer_ads_use_result_cache” = FALSE scope=both;
更改隐含参数后,观察了一两天,发现latch free等待事件已经消失:
select round(100 * ratio_to_report(DB_TIME) over(),1) "ACTIVE_DB_TIME%",
wait_event
from
(
select
count(*) as DB_TIME,
NVL(A."EVENT",'ON CPU') wait_event
from dba_hist_active_sess_history a
where a."SAMPLE_TIME" >= to_date('2024-06-27 16:00:00','yyyy-mm-dd hh24:mi:ss')
and a."SAMPLE_TIME" <= to_date('2024-06-27 17:00:00','yyyy-mm-dd hh24:mi:ss')
group by a."EVENT"
order by 1 desc
);
大部分等待都是在CPU,这是一个非常好的现象。
当然,这个案例告诉我们数据库尽量选用稳定版本如19c版本,即使使用不稳定版本,也要打补上,减少遇到bug的概率