实例恢复导致数据库性能下降

最近更新：2024-03-22 14:41:41

问题概述

2023年6月3日11点07分，监控到数据库1节点出现异常，操作系统无法连接。节点1宕机后，节点2随后也出现了异常。

结论和建议

分析结论

1、 11:07，节点1的连接会话为7537，节点2为3018； 2、 11:07，节点1数据库服务器宕机； 3、 11:07，节点2情况如下：节点1的会话全部转到节点2，导致节点2的连接数迅速从3000上升到15000；由于节点1是异常宕机，节点2开始启动实例恢复，节点2在实例恢复的时候，阻塞大量的业务会话； 4、 11:10，节点2完成实例恢复，节点2完成实例恢复后，不再堵塞其他会话。 5、 11:10：由于之前积压的业务会话连接，加上不断的新的连接建立，数据库连接超过最大限制，导致了业务无法正常使用的。

最终建议

1、数据库连接数，从15000，调整到22500； 2、数据库两个节点正常情况下，保持负载均衡的会话连接数，不出现会话数极端不均衡情况，保证在做实例恢复时不会过于太长。

问题分析

问题描述

2023年6月3日11点07分，监控到数据库1节点出现异常，操作系统无法连接。节点1宕机后，节点2随后也出现了异常。

问题分析

确认等待事件情况

查询从11:07到11:17的ASH 信息，确认等待事件情况

SQL> SET LINESIZE 200                                                         
SQL> SET PAGESIZE 50
SQL> COL EVENT_NAME FORMAT A40
SQL> WITH ASH AS
  2   (SELECT SNAP_ID,
  3           INSTANCE_NUMBER,
  4           SAMPLE_ID,
  5           TO_CHAR(SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss') AS SAMPLE_TIME,
  6           SESSION_ID,
  7           SESSION_SERIAL#,
  8           EVENT,
  9           BLOCKING_SESSION,
 10           BLOCKING_INST_ID,
 11           PROGRAM
 12      FROM ASH_060310_12
 13     WHERE SAMPLE_TIME  BETWEEN
 14         TO_DATE('2023-06-03 11:07:00', 'YYYY-MM-DD HH24:MI:SS') AND
 15         TO_DATE('2023-06-03 11:16:00', 'YYYY-MM-DD HH24:MI:SS')
 16       AND EVENT IS NOT NULL)
 17  SELECT INSTANCE_NUMBER,
 18         SAMPLE_ID,
 19         SAMPLE_TIME,
 20         EVENT,
 21         COUNT(*) AS EVENT_CNT
 22    FROM ASH  
 23   GROUP BY INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, EVENT
 24   HAVING COUNT(*) > 8
 25   ORDER BY SAMPLE_ID, COUNT(*) DESC;

INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME          EVENT                           EVENT_CNT
--------------- ---------- -------------------- ------------------------------ ----------
              1  140407625 2023-06-03 11:07:01  ges generic event                      48
              1  140407635 2023-06-03 11:07:11  ges generic event                      48
              1  140407645 2023-06-03 11:07:21  ges generic event                      48
              2  140454702 2023-06-03 11:07:04  ges generic event                      48
              2  140454712 2023-06-03 11:07:14  ges generic event                      48
              2  140454722 2023-06-03 11:07:25  ges generic event                      48
              2  140454732 2023-06-03 11:07:35  library cache lock                    216
              2  140454732 2023-06-03 11:07:35  gc cr failure                         156
              2  140454732 2023-06-03 11:07:35  gc buffer busy acquire                 48
              2  140454732 2023-06-03 11:07:35  ges generic event                      48
              2  140454732 2023-06-03 11:07:35  log file sync                          37
              2  140454732 2023-06-03 11:07:35  gc cr request                          13
              2  140454732 2023-06-03 11:07:35  gc current retry                       11
              2  140454742 2023-06-03 11:07:45  library cache lock                    461
              2  140454742 2023-06-03 11:07:45  gc cr failure                         246
              2  140454742 2023-06-03 11:07:45  gc buffer busy acquire                 79
              2  140454742 2023-06-03 11:07:45  log file sync                          55
              2  140454742 2023-06-03 11:07:45  ges generic event                      48
              2  140454742 2023-06-03 11:07:45  gc current retry                       12
              2  140454752 2023-06-03 11:07:58  gc domain validation                  673
              2  140454752 2023-06-03 11:07:58  library cache lock                    216
              2  140454752 2023-06-03 11:07:58  ges generic event                      48
              2  140454752 2023-06-03 11:07:58  library cache load lock                33
              2  140454762 2023-06-03 11:08:09  buffer busy waits                     524
              2  140454762 2023-06-03 11:08:09  Disk file operations I/O              369
              2  140454762 2023-06-03 11:08:09  read by other session                 114
              2  140454762 2023-06-03 11:08:09  ges generic event                      48
              2  140454762 2023-06-03 11:08:09  library cache load lock                36
              2  140454772 2023-06-03 11:08:19  buffer busy waits                    1436
              2  140454772 2023-06-03 11:08:19  Disk file operations I/O              785
              2  140454772 2023-06-03 11:08:19  read by other session                 458
              2  140454772 2023-06-03 11:08:19  ges generic event                      46
              2  140454772 2023-06-03 11:08:19  library cache load lock                46
              2  140454772 2023-06-03 11:08:19  library cache lock                     38
              2  140454782 2023-06-03 11:08:29  buffer busy waits                    1895
              2  140454782 2023-06-03 11:08:29  Disk file operations I/O              643
              2  140454782 2023-06-03 11:08:29  read by other session                 523
              2  140454782 2023-06-03 11:08:29  library cache load lock                58
              2  140454782 2023-06-03 11:08:29  ges generic event                      47
              2  140454782 2023-06-03 11:08:29  enq: SQ - contention                   11
              2  140454782 2023-06-03 11:08:29  cursor: pin S wait on X                10
              2  140454792 2023-06-03 11:08:40  buffer busy waits                    2223
              2  140454792 2023-06-03 11:08:40  Disk file operations I/O              415
              2  140454792 2023-06-03 11:08:40  read by other session                 300
              2  140454792 2023-06-03 11:08:40  library cache load lock                65
              2  140454792 2023-06-03 11:08:40  ges generic event                      48
              2  140454792 2023-06-03 11:08:40  cursor: pin S wait on X                18

INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME          EVENT                           EVENT_CNT
--------------- ---------- -------------------- ------------------------------ ----------
              2  140454792 2023-06-03 11:08:40  enq: SQ - contention                   11
              2  140454802 2023-06-03 11:08:50  buffer busy waits                    3223
              2  140454802 2023-06-03 11:08:50  library cache load lock                68
              2  140454802 2023-06-03 11:08:50  ges generic event                      48
              2  140454802 2023-06-03 11:08:50  cursor: pin S wait on X                20

......