数据库管理-第二十二期 记一系列问题处理（20211109）

第二十二期 记一系列问题处理

距离上期更新已经快6个月了，中间还是一个人管一堆事情，还好9月来了个小伙伴，分担了一些压力。这一期还是回归本系列老生常谈的那套一体机，最近出了一系列的问题，这里将问题具体内容，分析流程和最终解决风险给大家。

1 背景和问题描述

在处理过之前的一些bug后，这套一体机已稳定运行一年有余。期间客户觉得只跑一个业务有点浪费一体机性能和存储空间，因此计划新迁移一个业务上来。由于暂时没有停机窗口调整内存，因此只给新业务PDB的SGA分配了24GB内存，用于功能性测试和迁移测试。问题也就从这24GB内存开始了。
在新业务计划正式上线之前，由于业务测试很多功能响应很慢，主要是分配内存太小了，因此开始了一系列的操作：
1.将CDB的SGA配置从192G扩展到210G
2.将PDB1（老业务）的SGA从160G降低到130G
3.将PDB2（新业务）的SGA从24G扩展到64G
继续新业务测试，没啥问题。
<问题1> 但是有一天下午，数据库出现大规模无法访问的现象，经查节点1数据库PDB2的连接数已超过1W（数据库processes配置7680），连接数被撑爆，紧急关闭新业务应用程序后数据库服务恢复。经查新业务每个应用节点配置自身连接池且未做限制，因此连接逐步扩大撑爆数据库节点1连接数。修改后重新启动新业务应用。
<问题2> 以为连接数的事情就过去了？然鹅！不是，应用在后续进行更新的时候将连接池配置文件覆盖了，再次启动应用时，同时启动了8个节点，在10分钟内扩展了4000+的连接数，造成连接风暴计算节点CPU100%引起数据库无法访问，期间因操作系统响应问题重启了该节点（[1] 这里是一个坑）。关闭新业务应用调整连接池参数。
<问题3> 新业务在进行了近半个月的测试后终于准备正式上线了，然鹅，在上线前，业务开发方要求重启PDB2，重启后在迁移数据过程与数据模型转换的时候速度变慢，且出现了ORA-4031（Shared pool out of memory）的错误。经检查，部分节点的shared pool的自动增长速率满足不了SQL执行的需求。因此…暂时让操作单独连到内存扩展正常的节点跑完了迁移工作。同时重启了一下内存扩展迟滞的那个节点，后面开始发现节点1响应不如节点2，但无伤大雅（[2] 这里又是一个坑）。
<问题4> 继续无事了两周，一天下午数据库响应突然变慢，发现节点1出现了大量的GC freelist等待，造成节点1的CPU占用率飙升且性能严重下降。配置所有PDB的最小db_cache_size后性能恢复（[3] 原因下一节解释）。
<问题5> 第二天生产开始，节点1又开始卡，并出现大量的latch: cahe buffer (lru) chains。但同时段节点2响应又十分正常。随即将部分应用切换至节点2，暂时保持数据库响应正常，此时节点1响应仍然较慢。（[4] 该问题已排查解决，下一节讲）
<问题6> 解决问题5之后，周末无事，数据库双节点运行正常，速度跑来飞起，然鹅，周一又出现了ORA-4031（Shared pool out of memory）的错误，通过应急flush PDB的shared pool解决问题。（[5] 该问题已排查解决，下一节讲）
至此所有问题如上描述，下一节讲进行逐一讲解。

2 问题分析及解决

首先，<问题1> 和 <问题2> 都是应用侧配置的问题，很好分析解决，这里就不细讲了。重点要从 <问题3> 的分析和处理开始。在解决这个问题的过程中，迁移前的重启造成了PDB2的SGA被刷新，启动后shared pool的自动增长速率满足不了需求，而这时对节点1的完全重启操作造成了后面的一系列问题。
<问题4> 的问题时在ASMM下，节点1上两个PDB的shared pool扩展的过大，造成buffer cache过小（分别仅有5G和3G左右）引发GC freelist造成的。
而 <问题5> 则是因为cbc latch数量造成的，这里解释一下latch：每一条SQL来到数据库都要获取一个闩锁，用于事务控制、SQL执行等，闩锁的数量在数据库启动时就已经确定，并且不再变化。闩锁数量和启动时的DB Cache大小有关，但由于Oracle数据库的内存管理是采用的自动动态管理，在1节点启动时，Oracle只初始化分配了很小的内存大小，所以导致了该闩锁数量太少，引发了性能下降。其实这个问题就源自于对节点1的重启操作。cbc latch的数量可以通过“select count(1) from v$latch_children where NAME =‘cache buffers chains’”语句查询。当时节点1的数量仅为16384，节点2为131072。因此申请了割接，对数据库进行了以下操作：

alter system set sga_target=220g scope=spfile sid='*';  --CDB的SGA为220GB。
alter system set sga_max_size=220g scope=spfile sid='*';  --CDB的SGA为220GB。
alter system set shared_pool_size=50g scope=spfile sid='*';  --CDB的share pool最小50GB。
alter system set db_cache_size=150g scope=spfile sid='*'; --CD的db cache最小150GB
alter system set "_memory_imm_mode_without_autosga"=true scope=spfile sid='*';  --避免内存初始化效率不高
alter system set "_cursor_db_buffers_pinned"=20 scope=spfile sid='*';  --闩锁数量相关的参数
alter system set "_db_block_hash_latches"=131072 scope=spfile sid='*';  --闩锁数量参数

alter session set container=pdb1; --登录pdb1
alter system set shared_pool_size=25g sid='*';  --保证pdb1的share pool至少25GB
alter system set db_cache_size=0 sid='*';

alter session set container=pdb2;  --登录到pdb2
alter system set sga_target=70g sid='*'; --pdb2的SGA为70GB
alter system set shared_pool_size=15g sid='*'; --保证pdb的share pool至少15GB
alter system set db_cache_size=0 sid='*';

<问题6> 其实是涉及以下一些问题：
1.本次数据库重启完成后NUMA pool使用了23G的内存（重启之前查询为15G），所以PDB1+PDB2的SGA配置总量200G，加上NUMA pool超过了CDB SGA配置的220G。
2.在PDB级别同时配置sga_min_size和shared_pool_size时，会触发shared pool回收复用机制的问题（该问题出现在12.2及以上版本），同样会因为ORA-4031的问题。
根据分析为了保证SGA个内存组件都有足够的空间，因此在线执行了以下操作：

alter system set sga_min_size=0 scope=spfile sid='*';  --重置CDB的sga_min_size参数。
alter system set shared_pool_size=60g scope=spfile sid='*';  --CDB的share pool最小60GB。
alter system set db_cache_size=130g scope=spfile sid='*'; --CD的db cache最小130GB

alter session set container=pdb1; --登录pdb1
alter system set sga_min_size=0 sid='*'; --重置pdb1的sga_min_size参数。
alter system set shared_pool_size=0 sid='*';  --重置pdb1的share pool参数

alter session set container=pdb2;  --登录到pdb2
alter system set sga_target=60g sid='*'; --保证pdb2的SGA区最大60GB
alter system set sga_min_size=0 sid='*'; --重置pdb2的sga_min_size参数。
alter system set shared_pool_size=0 sid='*'; --重置pdb2的share pool参数

至此所有问题解决。其实，从问题出现过程和处理过程中不难发现，基于内存的一系列问题其实源自于操作系统、数据库实例及PDB的各种重启操作，因此在数据库运行过程中，如无必须重启的必要，一般不要进行重启操作。当然问题彻底解决前，也有一个试错的过程，延长了问题影响的时间。

3 锦上添花

其实本次问题的各类分析主要有几方势力参与：我（现场工程师，最菜的那个）、SR后台工程师、一体机资深售前工程师及RWP工程师（后面两位贡献最大）。在解决问题的过程中，RWP的工程师通过AWR报表，AWRDump信息以及其他脚本收集信息，还给数据库从SQL（执行计划、绑定变量等）、表（结构、统计信息等）、索引（是否存在、是否合理等）等方面提出了全方位优化建议。在过去正常情况下执行较慢的SQL速度也得到了极大的提升。也检查了为何在ASMM下shared pool很大的原因。
下面也将分享一些数据库各类检查的SQL语句：
1.cbc latch数量及配置查询：

select count(1) from v$latch_children where NAME ='cache buffers chains'；
select a.ksppinm "Parameter",a.KSPPDESC "Description",b.ksppstvl "Value" from x$ksppi a, x$ksppcv b where a.indx = b.indx and  a.ksppinm like '%_db_block_hash_latches%';

2.NUMA pool大小查询

select inst_id,sum(bytes)/1024/1024/1024 from GV$SGASTAT where upper(pool) like '%NUMA%' group by inst_id; --查询NUMA POOL大小
select inst_id,name,bytes/1024/1024/1024 GB from gv$sgainfo where name like '%NUMA%' order by inst_id; --查询NUMA POOL初始大小

3.按小时统计实例SQL平均执行时间

WITH r AS
 (SELECT trunc(end_interval_time, 'hh24') stamp,
         stat_name||'-'||instance_number NAME,
         VALUE -
         nvl(LAG(VALUE) OVER(PARTITION BY instance_number, stat_name, startup_time ORDER BY end_interval_time), 0) VALUE
  FROM   dba_hist_sysstat
  JOIN   dba_hist_snapshot
  USING  (dbid, snap_id, instance_number)
  WHERE  stat_name IN ('DB time', 'user calls'))
SELECT stamp,
       ROUND(SUM(DECODE(NAME, 'DB time-1', VALUE*10)) / SUM(DECODE(NAME, 'user calls-1', VALUE)),2) inst1,
       round(SUM(DECODE(NAME, 'DB time-2', VALUE*10)) / SUM(DECODE(NAME, 'user calls-2', VALUE)),2) inst2
FROM   r
GROUP BY stamp
ORDER  BY stamp DESC

4.查询shared pool中占用较大的组件

select inst_id,con_id,pool,name,bytes/1024/1024/1024 GB from GV$SGASTAT where upper(pool) like '%SHARED%' and bytes/1024/1024/1024>1 order by 1,2; --查询shared pool中占用超过1G的组件，包括剩余空间

5.查询shared pool中占用SQLArea较多的语句（>100MB）

select * from (select count(1),count(DISTINCT(sql_id)),max(sql_id) sql_id,sum(typecheck_mem)/1024/1024 MB from gv$sql a group by force_matching_signature order by 4 desc) where MB>100; --其中前两列比值代表SQL的version数量比值，可以通过下面的语句查询原因
select * from gv$sql_shared_cursor where sql_id='xxxxxxx'; --通过查看值为Y的列分析为何出SQL会出现多version

通过这些语句也发现，pdb1中大量建表DDL语句占用SQLArea较大，根据分析为中转临时数据是通过建表删表方式实现。
6.分实例与PDB查看SGA中pool占用情况

SELECT con_id,
       pool,
       round(SUM(bytes / 1024 / 1024 * decode(inst_id, 1, 1))) inst1,
       round(SUM(bytes / 1024 / 1024 * decode(inst_id, 2, 1))) inst2
FROM   gv$sgastat
GROUP  BY con_id, pool
ORDER  BY pool, con_id;

7.查询所有容器库的内存占用情况

SELECT r.inst_id,r.CON_ID, p.NAME, r.SGA_BYTES/1024/1024/1024 SGA, r.PGA_BYTES/1024/1024/1024 PGA, r.BUFFER_CACHE_BYTES/1024/1024/1024 CACHE,r.SHARED_POOL_BYTES/1024/1024/1024 SHARED_POOL FROM GV$RSRCPDBMETRIC r, GV$CONTAINERS p WHERE r.CON_ID = p.CON_ID and r.INST_ID=p.INST_ID order by inst_id，con_id;

其他SQL后续补充中

下期预告：

待定