MRP0进程Hung?

前天晚上,同事发来微信求助,监控到一套2节点数据库RAC 数据库主备库同步状态异常,并且同事经过分析发现造成此问题的是因为Dataguard MRP0进程存在,但是停止在某一个seqence#上,应用的sequnce#与block不进行恢复。

随即登录备库数据库查看同步状态,发现MRP0进程停滞在sequnce:197788与block:186030上,实际上并没有进行“APPLYING_LOG”。

SQL> set linesize 200
select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0
SQL>   2  ;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS       IDLE         LGWR              1     199217     123144             0            0
RFS       IDLE         LGWR              2     142409      77956             0            0
MRP0      APPLYING_LOG N/A               1     197788     186030             9            9

此日志197788归档日志文件存在与备库,并且同事反馈尝试alter database register logfile、主库RMAN重新恢复此197788归档日志,均无效。

查询v$archived_log确认197788这个归档文件为20210109 15:00左右主库产生的,分别查看一下主库/备库的alert日志,看看那时数据库有无异常情况发生:

节点1,传输至备时出现异常:
Thread 1 advanced to log sequence 197788 (LGWR switch)
  Current log# 5 seq# 197788 mem# 0: +DG_REDO/Travelskydba/onlinelog/group_5.287.1003922183
  Current log# 5 seq# 197788 mem# 1: +DG_REDO/Travelskydba/onlinelog/group_5.286.1003922183
Sat Jan 09 15:32:25 2021
Archived Log entry 275332 added for thread 1 sequence 197787 ID 0x9c755e67 dest 1:
Sat Jan 09 15:32:25 2021
ARCj: Standby redo logfile selected for thread 1 sequence 197787 for destination LOG_ARCHIVE_DEST_2
Sat Jan 09 15:33:14 2021
LNS: Standby redo logfile selected for thread 1 sequence 197788 for destination LOG_ARCHIVE_DEST_2
Sat Jan 09 15:43:17 2021
Thread 1 advanced to log sequence 197789 (LGWR switch)
  Current log# 1 seq# 197789 mem# 0: +DG_REDO/Travelskydba/onlinelog/group_1.295.1003922179
  Current log# 1 seq# 197789 mem# 1: +DG_REDO/Travelskydba/onlinelog/group_1.294.1003922179
Sat Jan 09 15:43:17 2021
Archived Log entry 275335 added for thread 1 sequence 197788 ID 0x9c755e67 dest 1:
Sat Jan 09 15:47:06 2021
WARN: ARC1: Terminating pid 4047 hung on an I/O operation
Killing 1 processes with pids 4047 (Process by index) in order to remove hung processes. Requested by OS process 31580 on instance 1
ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2
ARCo: Standby redo logfile selected for thread 1 sequence 197788 for destination LOG_ARCHIVE_DEST_2

节点2,传输至备库时出现异常:
Sat Jan 09 15:47:15 2021
Archived Log entry 275337 added for thread 2 sequence 141662 ID 0x9c755e67 dest 1:
Sat Jan 09 15:47:35 2021
WARN: ARC2: Terminating pid 27590 hung on an I/O operation
Killing 1 processes with pids 27590 (Process by index) in order to remove hung processes. Requested by OS process 27465 on instance 2
ARC2: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2
Sat Jan 09 15:47:37 2021
NSA2 started with pid=29, OS id=2625
Sat Jan 09 15:47:42 2021


备库节点:
Archived Log entry 77427 added for thread 1 sequence 197786 ID 0x9c755e67 dest 1:
Media Recovery Log /oracle/archive_Travelskydba/Travelskydba/2_141661_853598219.arc
Media Recovery Waiting for thread 2 sequence 141662 (in transit)
Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937
Media Recovery Waiting for thread 1 sequence 197788 (in transit)
Recovery of Online Redo Log: Thread 1 Group 7 Seq 197788 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_7.282.1003923169
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_7.280.1003923169

备库节点在1月9日 16:24:31时已经应用完成197788日志
Sat Jan 09 16:24:31 2021
Media Recovery Log /oracle/archive_Travelskydba/Travelskydba/1_197788_853598219.arc

197788这个日志已经应用完毕,并且197789日志也完好的存在与备库节点 ,那么很有可能目前备库MRP0进程不工作的原因是在等其他日志应用完毕,。备库应用日志需要连续,那么是否有可能其他节点或本节点的其他日志?

我们可以利用主库如下sql查询进行确认:

SQL> SELECT FIRST_CHANGE#,
       THREAD#,
       DEST_ID,
       STATUS,
       FAL,
       APPLIED,
       DELETED,
       to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME,
       to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') COMPLETION_TIME,
       sequence#
FROM v$archived_log
WHERE to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') BETWEEN '20210109 15:00:17' AND '20210109 16:00:17'  order by 1,5  2    3    4    5    6    7    8    9   10   11   12  ;

   FIRST_CHANGE#          THREAD#          DEST_ID S FAL APPLIED   DEL FIRST_TIME        COMPLETION_TIME          SEQUENCE#
---------------- ---------------- ---------------- - --- --------- --- ----------------- ----------------- ----------------
   9555246783243                2                2 A NO  YES       NO  20210109 14:32:30 20210109 15:04:31           141659
   9555246783243                2                1 D NO  NO        YES 20210109 14:32:30 20210109 15:04:32           141659
   9555247407959                1                1 D NO  NO        YES 20210109 14:54:15 20210109 15:04:31           197783
   9555247407959                1                2 A NO  YES       NO  20210109 14:54:15 20210109 15:04:31           197783
   9555247700807                1                2 A NO  YES       NO  20210109 15:04:30 20210109 15:15:10           197784
   9555247700807                1                1 D NO  NO        YES 20210109 15:04:30 20210109 15:15:10           197784
   9555247701783                2                2 A NO  YES       NO  20210109 15:04:31 20210109 15:32:20           141660
   9555247701783                2                1 D NO  NO        YES 20210109 15:04:31 20210109 15:32:21           141660
   9555248017764                1                1 D NO  NO        YES 20210109 15:15:10 20210109 15:25:40           197785
   9555248017764                1                2 A NO  YES       NO  20210109 15:15:10 20210109 15:25:40           197785
   9555248313954                1                2 A NO  YES       NO  20210109 15:25:40 20210109 15:33:14           197786
   9555248313954                1                1 D NO  NO        YES 20210109 15:25:40 20210109 15:32:19           197786
   9555248508993                1                1 D NO  NO        YES 20210109 15:32:19 20210109 15:32:25           197787
   9555248508993                1                2 A YES YES       NO  20210109 15:32:19 20210109 15:32:26           197787
   9555248509181                2                1 D NO  NO        YES 20210109 15:32:20 20210109 15:32:23           141661
   9555248509181                2                2 A NO  YES       NO  20210109 15:32:20 20210109 15:32:23           141661
   9555248510236                2                1 D NO  NO        YES 20210109 15:32:23 20210109 15:47:15           141662
   9555248510774                1                1 D NO  NO        YES 20210109 15:32:25 20210109 15:43:17           197788
   9555248831803                1                1 D NO  NO        YES 20210109 15:43:17 20210109 15:47:14           197789
   9555248831803                1                2 A YES NO        NO  20210109 15:43:17 20210109 15:52:30           197789
   9555248943929                1                1 D NO  NO        YES 20210109 15:47:14 20210109 15:52:14           197790
   9555248944032                2                1 D NO  NO        YES 20210109 15:47:15 20210109 15:47:42           141663
   9555248944032                2                2 A YES NO        NO  20210109 15:47:15 20210109 15:53:18           141663
   9555248950448                2                1 D NO  NO        YES 20210109 15:47:42 20210109 15:53:18           141664
   9555248950448                2                2 A YES NO        NO  20210109 15:47:42 20210109 15:55:13           141664

25 rows selected.

上述查询确认后,每个sequence应当归档到dest_id=1(本地),dest_id=2(备库),但是发现thread 2的141662,未显示归档到DEST_ID=2,并且下一个日志便是197788
Media Recovery Log /oracle/archive_Travelskydba/Travelskydba/2_141661_853598219.arc
Media Recovery Waiting for thread 2 sequence 141662 (in transit)
Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937
Media Recovery Waiting for thread 1 sequence 197788 (in transit)
Recovery of Online Redo Log: Thread 1 Group 7 Seq 197788 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_7.282.1003923169
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_7.280.1003923169
Sat Jan 09 15:49:02 2021
RFS[31]: Possible network disconnect with primary database
后续,备库一值在读取141662这个日志文件,但

Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937

Mon Jan 18 20:42:42 2021
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Recovery of Online Redo Log: Thread 2 Group 11 Seq 141662 Reading mem 0
  Mem# 0: /oracle/redo_Travelskydba/Travelskydba/group_11.301.1003923935
  Mem# 1: /oracle/redo_Travelskydba/Travelskydba/group_11.304.1003923937

此时,主库141662这个归档认为已经传输到备库并且已经删除,但备库实际并未接收到。Threa#1 的197788无法应用的根因为Thread# 2 归档141662并未应用。

让同事将141662归档从主库归档备份中恢复出来 ,拷贝至备库注册或等待备库(fal_client)从主库(fal_server)中获取。同事恢复此归档文件后,备库MRP0进程立即开始工作,进行正常的日志应用,同时主库的v$archived_log中的日志记录变成了复合预期的状态:

SQL> SELECT FIRST_CHANGE#,
       THREAD#,
       DEST_ID,
       STATUS,
       FAL,
       APPLIED,
       DELETED,
       to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME,
       to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') COMPLETION_TIME,
       sequence#
FROM v$archived_log
WHERE sequence#=141662  2    3    4    5    6    7    8    9   10   11   12  ;

   FIRST_CHANGE#          THREAD#          DEST_ID S FAL APPLIED   DEL FIRST_TIME        COMPLETION_TIME          SEQUENCE#
---------------- ---------------- ---------------- - --- --------- --- ----------------- ----------------- ----------------
   9555248510236                2                1 D NO  NO        YES 20210109 15:32:23 20210109 15:47:15           141662
   9555248510236                2                1 A NO  NO        NO  20210109 15:32:23 20210118 21:39:53           141662
   9555248510236                2                2 A YES YES       NO  20210109 15:32:23 20210118 21:40:33           141662

我眼中的国产数据库

2020年,国内的国产数据库开始蓬勃发展,各个国产数据库厂商产品犹如经历了寒冬过后的春风一般,让广大数据库从业者深刻感受到“寒冬过后便是春天”。我司也不例外,在2020年测试了国产数据库选型,国产化的话题出现在大大小小的会议中,虽然起初的春风仍有些刺骨,但是无法掩盖风中浓烈希望的味道。

在国产数据库选型过程中,我们参考了国内数据库专业社区墨天轮的排行榜https://www.modb.pro/dbRank

墨天轮国产数据库RANK中,供上榜了100+个数据库,在我们的选型中提供了具非常价值的参考意义,为我们从众多繁杂的信息甄选出一套选型标准。

对于传统企业来说,也许之前都是采用的Oracle数据库,很多应用也是基于Oracle的特性与功能进行开发与演进,所以Oracle的兼容性也是国产数据库主要攻破的难题之一,POLARDB-O,OCEANBASE oracle租户模式等等,从应用角度来看,出发点均是使得客户尽少量的对应用代码的改造;运维角度上,国产数据库产品中均提供了相对丰富的白屏可视化操作界面,也许是从云下落地的缘故,也许是想向云上靠拢,”云上“同国产化一样,同样“大势所趋”,同样”已不可逆“。

产品迭代能力层面,国产数据库与Oracle的差距显而易见,与很多传统企业的朋友谈及国产化问题的时候,同我们一样,除了架构与产品特性之外,更加关心的是“厂商实力”+”产品生态“,因为虽然国产数据库百花齐放,但是成功的”通用“案例很少,可用性与可靠性与会出现的任何问题,不去实际测试很难得出结论,所以在测试过程中与启用产品之前,均会对厂商的后台支持度会严重关切,例如测试中出现了一个问题,厂商确认为Bug或功能尚未完善,反馈至厂商的响应程度如何?需要多久可以进行实际的修复?无论是新产品还是从云下落地的产品,相信出现问题是必然的,同样是正常的,但是厂商响应并修复的力度如何,也许直接反映体现在客户选择此产品迭代能力的信任程度,同时,侧面体现出此产品适配架构与场景的过程的长短,选择产品的魄力很大程度上是基于测试过程中对产品的信心与感受。

生态方面,墨天轮数据库社区积极的为国产数据库产品提供一个专业,开放的生态社区,相信生态方面的质变,只是时间问题,那么我们最为数据库从业人员,能见证众多国产数据库的诞生,兴起,是一件幸事。无论是人还是技术,有了希望,“盛夏”指日可待。

利用sysmetric_history与dba_hist_event_histogram定位IO性能问题

近期发现,公司生产4套数据库从12月9日起,每周三22:00-22:30时间段均有应用事务DML缓慢的现象,IO等待事件明显增多:

IO等待类事件明显均有显著抬头的现象,例如log file sync等,但是我们如何定位是否真正的数据库IO真正出现问题?是因还是果?是否是io requeset变多还是IO真正出现问题?

问题时间段的AWR:

AWR中的Avg Wait极有可能被平均
另外一套库的显示的较为直观,log file sync平均等待高达17ms之多

换一个角度,我们从Oracle提供的数据库内部的v$metric_history与dba_hist_sysmetric_history动态视图中可以从“ Average Synchronous Single-Block Read Latency、I/O Megabytes per Second、I/O Requests per Second”等指标中辅助定位:

select to_char(end_time, 'yyyymmdd hh24:mi:ss') as time,
       max(case
             when metric_name = 'Average Synchronous Single-Block Read Latency' then
              round(value,2)
             else null
           end) as "Single Block Read Latency",
       max(case
             when metric_name = 'I/O Megabytes per Second' then
              round(value,2)
             else null
           end) as "IO Megabytes per Second",
       max(case
             when metric_name = 'I/O Requests per Second' then
              round(value,2)
             else null
           end) as "I/O Requests per Second"
  from dba_hist_sysmetric_history where instance_number=1 and to_char(end_time, 'yyyymmdd hh24:mi:ss') between '20210106 22:00:00' and '20210106 23:00:00'
 group by to_char(end_time, 'yyyymmdd hh24:mi:ss') order by 1

TIME              Single Block Read Latency IO Megabytes per Second I/O Requests per Second
----------------- ------------------------- ----------------------- -----------------------
20210106 21:00:44                      1.75                   23.69                 2060.17
20210106 21:01:44                       2.1                   13.32                 1474.73
20210106 21:02:43                      1.99                    18.6                 1716.56
20210106 21:03:43                      1.43                   11.54                 1089.31
20210106 21:04:43                      1.69                   20.77                 1567.28
20210106 21:05:43                      1.61                   17.92                 1806.92
20210106 21:06:44                      2.21                   27.95                 2466.91
20210106 21:07:44                      1.63                   11.29                 1052.18
20210106 21:08:44                      2.01                   17.23                 1729.85
20210106 21:09:43                      1.63                   12.53                  1309.2
20210106 21:10:43                      1.36                   15.53                 1180.81
20210106 21:11:43                      2.57                   12.97                 1399.38
20210106 21:12:43                      1.42                   16.71                 1323.95
20210106 21:13:44                      1.65                   10.37                 1367.22
20210106 21:14:44                      1.42                   20.49                    1493
20210106 21:15:44                      1.66                    8.21                 1186.28
20210106 21:16:43                      2.39                   23.23                 2012.56
20210106 21:17:43                       1.3                   11.34                 1219.04
20210106 21:18:43                      1.15                   15.06                 1219.79
20210106 21:19:43                      1.66                       7                 1057.58
20210106 21:20:44                      1.38                   19.74                 1402.93
20210106 21:21:44                      2.59                    7.66                 1086.08
20210106 21:22:44                      1.22                   16.51                 1307.65
20210106 21:23:43                      1.39                   12.68                 1391.17
20210106 21:24:43                      1.43                   15.38                 1255.99
20210106 21:25:43                      1.37                   13.82                 1489.76
20210106 21:26:44                       1.9                   24.64                 2456.92
20210106 21:27:44                      1.49                    7.35                 1232.54
20210106 21:28:44                      1.06                   50.22                  1287.7
20210106 21:29:44                      2.06                   31.16                 1516.66
20210106 21:30:43                      1.21                   67.67                 1750.49
20210106 21:31:43                      2.52                   67.19                  1398.3
20210106 21:32:43                      1.68                   38.94                 1831.17
20210106 21:33:44                      1.46                    9.81                 1366.68
20210106 21:34:44                      1.61                   17.64                 1447.77
20210106 21:35:44                      1.52                   15.25                 1860.36
20210106 21:36:44                      1.61                   26.07                 2350.26
20210106 21:37:43                      2.18                   11.01                 1161.19
20210106 21:38:43                      1.38                   16.08                 1438.71
20210106 21:39:44                      1.56                   10.44                 1005.47
20210106 21:40:44                      1.23                   15.76                 1202.61
20210106 21:41:44                      2.21                    8.56                 1374.73
20210106 21:42:44                      1.71                    19.9                 1298.54
20210106 21:43:44                      1.49                    9.08                 1196.41
20210106 21:44:43                      1.79                   18.07                 1715.96
20210106 21:45:43                      1.86                   11.46                 1055.36
20210106 21:46:44                      1.36                   22.71                  1960.6
20210106 21:47:44                      1.13                    27.1                 1589.31
20210106 21:48:44                      1.39                   18.62                 1142.78
20210106 21:49:44                      1.88                    6.48                  904.06
20210106 21:50:44                      1.22                   20.12                  1420.4
20210106 21:51:43                      1.79                    7.54                 1054.08
20210106 21:52:44                      1.32                   16.79                 1246.04
20210106 21:53:44                       1.5                   12.89                 1509.54
20210106 21:54:44                      1.59                   14.58                  961.99
20210106 21:55:44                      1.75                    8.41                 1114.03
20210106 21:56:44                      1.42                   29.02                 2388.62
20210106 21:57:44                       1.7                    6.22                  815.38
20210106 21:58:43                      1.36                   14.27                 1024.69
20210106 21:59:44                      1.89                   10.66                 1133.77
20210106 22:00:44                      1.02                   52.87                 3232.29
20210106 22:01:44                       .71                   94.45                 4846.43
20210106 22:02:44                      5.28                   83.54                 2627.49
20210106 22:03:44                      7.04                   69.19                 2067.08
20210106 22:04:44                      7.07                   74.68                 2382.61
20210106 22:05:43                      4.87                   79.73                 2654.56
20210106 22:06:44                      5.94                   90.77                 3937.21
20210106 22:07:44                      8.01                   31.11                 1074.18
20210106 22:08:44                      6.16                   26.27                 1639.99
20210106 22:09:44                      2.71                    31.5                 1797.32
20210106 22:10:44                      5.12                   37.47                 1533.45
20210106 22:11:44                      5.04                   62.63                 1520.07
20210106 22:12:44                      3.81                   35.91                 1402.06
20210106 22:13:44                      3.66                   18.25                 1626.73
20210106 22:14:44                      3.01                   61.43                  2179.6
20210106 22:15:44                      2.59                   75.41                 1789.91
20210106 22:16:44                      3.13                    91.9                 2745.88
20210106 22:17:44                      2.97                   86.56                  1798.5
20210106 22:18:45                      2.33                   84.64                 1505.69
20210106 22:19:44                      1.74                    72.1                 1378.42
20210106 22:20:44                      1.84                   91.02                 1957.69
20210106 22:21:44                      2.72                   68.14                 1447.02
20210106 22:22:44                      2.18                   80.76                 1649.19
20210106 22:23:44                      2.75                   75.58                 1676.43
20210106 22:24:44                       2.2                   36.45                 1502.78
20210106 22:25:45                      2.21                   21.77                 1429.71
20210106 22:26:44                      2.61                   40.56                 2667.25
20210106 22:27:44                      2.09                   19.95                 1068.11
20210106 22:28:44                      1.39                   57.66                 1419.02
20210106 22:29:44                      1.61                   44.66                 1701.81
20210106 22:30:44                       1.2                   74.61                 2328.65
20210106 22:31:45                      1.29                   83.01                 2020.88
20210106 22:32:45                       .76                   94.96                 2706.07
20210106 22:33:44                      1.02                   22.84                 2032.87
20210106 22:34:44                       .74                   58.67                 2297.62
20210106 22:35:44                      1.76                   81.66                 1727.17
20210106 22:36:44                       .58                    66.2                 4839.24
20210106 22:37:44                       .87                   29.04                 1617.19
20210106 22:38:45                      1.54                   53.16                 1539.99
20210106 22:39:45                      1.21                    51.9                  1420.5
20210106 22:40:44                         1                   49.76                 1646.52
20210106 22:41:44                      2.23                   74.05                 1761.78
20210106 22:42:44                      1.17                  146.94                 1505.77
20210106 22:43:44                      1.39                   75.94                 1620.12
20210106 22:44:45                      1.07                   73.43                 2045.32
20210106 22:45:45                       .44                   82.36                 2572.26
20210106 22:46:44                         2                   54.02                 1909.32
20210106 22:47:44                      1.61                   43.08                 1479.45
20210106 22:48:44                      1.29                   47.81                 1296.04
20210106 22:49:44                      1.39                    50.6                 1337.25
20210106 22:50:44                       1.3                   55.93                 1497.06
20210106 22:51:45                      1.85                   43.28                 1074.38
20210106 22:52:45                      1.06                   93.48                 1559.25
20210106 22:53:44                      1.17                   39.43                 2132.45
20210106 22:54:44                       .54                   50.37                 2259.39
20210106 22:55:44                      1.35                   47.12                 1347.34
20210106 22:56:44                      2.09                   57.55                 2647.21
20210106 22:57:45                      1.33                   46.67                  980.51
20210106 22:58:45                       .68                   74.52                 1480.33
20210106 22:59:45                      1.19                    51.5                 1871.55

可以从上述三个指标中观察,Single Block Read Latency高的时候,IO请求量并不匹配,并非因为IO请求量导致IO效率下降,延时变高。
延时指标高,IO量并不大
继续阅读

诡异的ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

今年以来,公司多套RAC数据库(版本:11.2.0.4)ASM实例频繁报出ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O错误。这些数据库早在2016年时印象中便出现了此类报错,并且报错并非单个数据库偶发现象,而是大面积爆发,从3月至今仍然有些许数据库会抛出零星错误出现。

什么是ORA-27090?<参考 Doc ID 579108.1 >文中提到,此类报错的原因是操作系统异步IO请求参数 aio-max-nr 过低并且需要调整并发未完成的异步请求数目,避免异步IO请求失败的情况, Oracle官方文档中的建议值如下:

The "aio-max-nr" kernel limit is too low.
io_setup(4096, 0x66e1588) = -1 EAGAIN (Resource temporarily unavailable),

但是很诡异的是,我们的数据库早在2016年左右(第一次触发此问题)时,已经将该参数调整至4194304,理论上是不应该触发异步IO不足的问题。并且此报错风险性较高,ASM实例异步IO失败会导致数据库面临crash问风险,例如DBWR将脏块落盘(数据文件)时,如果此时DBWR进程无法进行时,数据库崩溃等其他异常行为会有极大可能性的。虽然可以将systcl.conf中的aio-max-nr一直调大,但是总归不是长久之计,并且在某年某月时会再次“爆发”。

最可怕的是,通过分析alert中的trace文件时,并没有发现任何有价值信息,所有的线索就是异步IO请求超过最大限制,导致某些IO请求失败抛出错误。。。

通过查看cat /proc/sys/fs/aio-nr查看当前的异步IO请求数量,此值偶尔下降但总量一直处于增长态势, 此时严重怀疑是否数据库中有某些进程执行异步IO请求后并没有释放IO描述符?造成异步IO描述符泄露?

我们数据库集群后发现异步IO请求变回归到正常范围内,但是态势仍然处于上升趋势:

ora11g@travelskydba-rac$ cat /proc/sys/fs/aio-nr 
22784
继续阅读

ORA-00704: bootstrap process failure故障修复

近日,同事反馈一套测试测试数据库宕机,进行重启数据库操作时发现数据库无法open,抛出ORA-00704: bootstrap process failure错误,版本为11.2.0.4

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  671089544 bytes
Database Buffers	  390070272 bytes
Redo Buffers		    5517312 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57338
Session ID: 96 Serial number: 3

错误一并抛出了ORA-00942: table or view does not exist错误,应该是系统表丢失导致的bootstrap失败,并且alert日志中没有任何其他日志输出。

将数据库启动到nomount与mount状态,利用10046 event跟踪bootstrap过程:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57585
Session ID: 96 Serial number: 3

访问/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc发现访问access$表时抛出异常:

继续阅读

核心系统表关联顺序错误导致的低效SQL

同事发来一个核心业务库的TOP SQL,占据并浪费了大量的服务器CPU资源,此SQL执效率信息如下:

SELECT a.ID,
       a.SEG_FLIGHT_ID,
       a.SEG_DEP_STATION_CODE,
       a.SEG_ARR_STATION_CODE,
       a.SEG_CLASS_SEQUENCE,
       a.SEG_TRAFFIC_RESTRICTION_CODE,
       a.SEG_DEP_TERMINAL,
       a.SEG_ARR_TERMINAL,
       a.SEG_OC_TYPE,
       a.SEG_OC_MAP_NUMBER,
       a.SEG_OC_CLASS_MAPPING,
       a.SEG_DATA_SOURCE
FROM T_SEGMENT a,
  (SELECT id
   FROM
     (SELECT t.id
      FROM T_FLIGHT t,
           T_TASK ta
      WHERE 1=1
        AND ta.id IN ('11008',
                      '11003',
                      '11014',
                      '11012',
                      '14001',
                      '11001')
        AND t.FLT_AIRLINE_CODE = ta.FLT_AIRLINE_CODE
        AND t.FLT_FLIGHT_NO = ta.FLT_FLIGHT_NO
        AND t.FLT_FLIGHT_DATE = ta.FLT_FLIGHT_DATE
        AND t.FLT_OC_FLIGHT_ID IS NULL
        AND t.IS_VALID = 1
        AND nullif('AAAA',t.FLT_OFFICE) IS NOT NULL )
   LEFT JOIN t_dep_schema s ON id = s.flt_id
   WHERE s.flt_id IS NULL
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_TCARD_PROCESS_FLAG ='N'
              OR s.FLT_IN_TCARD_PROCESS_FLAG IS NULL))
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_INIT_PROCESS_FLAG IS NULL
              OR s.FLT_IN_INIT_PROCESS_FLAG ='N')) ) f
WHERE 1=1
  AND a.SEG_FLIGHT_ID = f.ID
  AND a.IS_VALID = 1

SQL执行计划如下:

从执行计划中看到,估算行数与实际行数差别巨大,实际行数只是899行,但是NESTED LOOP之后估算结果集为46K,并且TASK表与S表选择了HASH JOIN,在未收集个性化扩展统计信息的情况,针对这种复杂情况,估算不准的情况是比较常见的,通常需要利用HINT来调整表连接顺序来优化。此SQL的涉及表都不存在索引缺失的情况,只是行数估算错误导致表连接顺序有误,从而选择了低效的执行计划。

继续阅读

原平均1762秒提升至0.035秒的TOP SQL优化处理过程

同事在日常巡检中发现一套数据库资源瓶颈严重,如下图所示:

活动会话多数在等待read by other session与CPU,根据硬件,多数为SQL问题导致,协助同事最终定位了TOP SQL,该SQL执行计划与执行效率信息如下图所示:

从执行计划中得知,最消耗资源的为TABLE ACCESS FULL全表扫描这步,该SQL有上百行,截取执行计划中此步SQL_TEXT如下:

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2pzyb63zhr1qp, child number 0
-------------------------------------
SELECT *
FROM
  (SELECT iti.owner,
          iti.object_name,
          iti.object_type,
          iti.OBJECT_ID,
          iti.DATA_OBJECT_ID,
          iti.STATUS,
          iti.CREATED,
          iti.LAST_DDL_TIME,
          iti.SECONDARY,
          iti.NAMESPACE,
          iti.EDITION_NAME
   FROM t iti
   WHERE iti.OBJECT_ID > 88
     AND iti.object_type != 'INDEX'
   ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100

Plan hash value: 3299198703

------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |   657K(100)|          |    100 |00:00:07.90 |     263K|
|*  1 |  COUNT STOPKEY          |      |      1 |        |            |          |    100 |00:00:07.90 |     263K|
|   2 |   VIEW                  |      |      1 |     15M|   657K  (1)| 02:11:28 |    100 |00:00:07.90 |     263K|
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |     15M|   657K  (1)| 02:11:28 |    100 |00:00:07.90 |     263K|
|*  4 |     TABLE ACCESS FULL   | T    |      1 |     15M| 71738   (1)| 00:14:21 |     17M|00:00:03.36 |     263K|
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2
   4 - SEL$2 / iti@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)

可以看到,该SQL用了232K个BUFFER,大约用了7秒多的时间。根据SQL逻辑,我们可以建立created,object_id两个字段索引,使得执行计划只扫描100行所需要的buffer,理想执行计划是走索引的INDEX FULL SCAN DESCENDING,A-ROWS只有100:

SQL> create index t_idx01 on t(created,object_id);

Index created.

再次测试SQL:

SQL>
SELECT *
FROM
  (SELECT iti.owner,
          iti.object_name,
          iti.object_type,
          iti.OBJECT_ID,
          iti.DATA_OBJECT_ID,
          iti.STATUS,
          iti.CREATED,
          iti.LAST_DDL_TIME,
          iti.SECONDARY,
          iti.NAMESPACE,
          iti.EDITION_NAME
   FROM t iti
   WHERE iti.OBJECT_ID > 88
     AND iti.object_type != 'INDEX'
   ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2pzyb63zhr1qp, child number 0
-------------------------------------
SELECT *
FROM
  (SELECT iti.owner,
          iti.object_name,
          iti.object_type,
          iti.OBJECT_ID,
          iti.DATA_OBJECT_ID,
          iti.STATUS,
          iti.CREATED,
          iti.LAST_DDL_TIME,
          iti.SECONDARY,
          iti.NAMESPACE,
          iti.EDITION_NAME
   FROM t iti
   WHERE iti.OBJECT_ID > 88
     AND iti.object_type != 'INDEX'
   ORDER BY iti.CREATED DESC)
WHERE ROWNUM<=100

Plan hash value: 4274603649

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     4 (100)|          |    100 |00:00:00.01 |     110 |      2 |
|*  1 |  COUNT STOPKEY                |         |      1 |        |            |          |    100 |00:00:00.01 |     110 |      2 |
|   2 |   VIEW                        |         |      1 |     15M|     4   (0)| 00:00:01 |    100 |00:00:00.01 |     110 |      2 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T       |      1 |     15M|     4   (0)| 00:00:01 |    100 |00:00:00.01 |     110 |      2 |
|*  4 |     INDEX FULL SCAN DESCENDING| T_IDX01 |      1 |      1 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |      10 |      2 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=100)
   3 - filter("iti"."OBJECT_TYPE"<>'INDEX')
   4 - access("iti"."OBJECT_ID">88)
       filter("iti"."OBJECT_ID">88)

我们看到,执行计划自动选择了INDEX FULL SCAN DESCENDING,无需单独指定降序扫描,优化器会自动选择此方式;A-ROWS只有100行(需求是order by + 小于等于100 row),相对于非全表扫描后过滤出100行的方式,BUFFER GET只需要110个即可。

在生产库上创建索引后性能对比:

最终执行计划如下:


-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                         |      1 |        |  1181 (100)|          |    104 |00:00:00.02 |    2476 |
|*  1 |  FILTER                           |                         |      1 |        |            |          |    104 |00:00:00.02 |    2476 |
|   2 |   NESTED LOOPS                    |                         |      1 |    496 |  1181   (1)| 00:00:15 |    508 |00:00:00.01 |    1542 |
|   3 |    NESTED LOOPS                   |                         |      1 |     89 |   673   (0)| 00:00:09 |    100 |00:00:00.01 |     963 |
|   4 |     VIEW                          |                         |      1 |    100 |    94   (0)| 00:00:02 |    100 |00:00:00.01 |     401 |
|*  5 |      COUNT STOPKEY                |                         |      1 |        |            |          |    100 |00:00:00.01 |     401 |
|   6 |       VIEW                        |                         |      1 |    100 |    94   (0)| 00:00:02 |    100 |00:00:00.01 |     401 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| ITINE                   |      1 |     22M|    94   (0)| 00:00:02 |    100 |00:00:00.01 |     401 |
|*  8 |         INDEX SKIP SCAN DESCENDING| ITIN_IDX_01             |      1 |    104 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |     301 |
|*  9 |     TABLE ACCESS BY INDEX ROWID   | SPNR                    |    100 |      1 |     6   (0)| 00:00:01 |    100 |00:00:00.01 |     562 |
|* 10 |      INDEX RANGE SCAN             | SPNR_PK                 |    100 |      9 |     3   (0)| 00:00:01 |    960 |00:00:00.01 |     291 |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | ITINE                   |    100 |      6 |     6   (0)| 00:00:01 |    508 |00:00:00.01 |     579 |
|* 12 |     INDEX RANGE SCAN              | ITINE_PK                |    100 |      6 |     3   (0)| 00:00:01 |    508 |00:00:00.01 |     271 |
|* 13 |   TABLE ACCESS BY INDEX ROWID     | ITINE                   |    404 |      1 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |     934 |
|* 14 |    INDEX UNIQUE SCAN              | ITINE_PK                |    404 |      1 |     3   (0)| 00:00:01 |    404 |00:00:00.01 |     530 |
-----------------------------------------------------------------------------------------------------------------------------------------------

执行效率高但“低效”的SQL优化

近期整理案例时发现,同事曾咨询过一个问题,数据库服务器CPU频繁报警,同事最终定位到一个TOP SQL,但是执行效率已经”算不错“,是否还有优化的余地?

SQL_TEXT如下:

SELECT DISTINCT S.*,
                BD.*,
                SD.*,
                UD.*
FROM T S
LEFT JOIN T1 BD ON S.DATA_OBJECT_ID = BD.DATA_OBJECT_ID
AND S.OBJECT_ID = BD.OBJECT_ID
LEFT JOIN T2 SD ON S.DATA_OBJECT_ID = SD.DATA_OBJECT_ID
AND S.OBJECT_ID = SD.OBJECT_ID
LEFT JOIN T3 UD ON S.DATA_OBJECT_ID = UD.DATA_OBJECT_ID
AND S.OBJECT_ID = UD.OBJECT_ID
WHERE S.OBJECT_ID = 88
  AND 1 = 1

原执行计划如下:
Plan hash value: 1035031963

----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        | 84081 (100)|          |      1 |00:00:00.60 |     309K|
|   1 |  HASH UNIQUE          |      |      1 |      1 | 84081   (1)| 00:16:49 |      1 |00:00:00.60 |     309K|
|*  2 |   HASH JOIN OUTER     |      |      1 |      1 | 84080   (1)| 00:16:49 |      1 |00:00:00.60 |     309K|
|*  3 |    HASH JOIN OUTER    |      |      1 |      1 | 79935   (1)| 00:16:00 |      1 |00:00:00.55 |     294K|
|*  4 |     HASH JOIN OUTER   |      |      1 |      1 | 75791   (1)| 00:15:10 |      1 |00:00:00.50 |     278K|
|*  5 |      TABLE ACCESS FULL| T    |      1 |      1 | 71647   (1)| 00:14:20 |      1 |00:00:00.44 |     263K|
|*  6 |      TABLE ACCESS FULL| T3   |      1 |    168 |  4144   (1)| 00:00:50 |      1 |00:00:00.05 |   15260 |
|*  7 |     TABLE ACCESS FULL | T2   |      1 |    168 |  4144   (1)| 00:00:50 |      1 |00:00:00.05 |   15260 |
|*  8 |    TABLE ACCESS FULL  | T1   |      1 |    168 |  4145   (1)| 00:00:50 |      1 |00:00:00.05 |   15260 |
----------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."OBJECT_ID"="BD"."OBJECT_ID" AND "S"."DATA_OBJECT_ID"="BD"."DATA_OBJECT_ID")
   3 - access("S"."OBJECT_ID"="SD"."OBJECT_ID" AND "S"."DATA_OBJECT_ID"="SD"."DATA_OBJECT_ID")
   4 - access("S"."OBJECT_ID"="UD"."OBJECT_ID" AND "S"."DATA_OBJECT_ID"="UD"."DATA_OBJECT_ID")
   5 - filter("S"."OBJECT_ID"=88)
   6 - filter("UD"."OBJECT_ID"=88)
   7 - filter("SD"."OBJECT_ID"=88)
   8 - filter("BD"."OBJECT_ID"=88)

SQL涉及四张表T,T1,T2,T3,数据量均在100万+。OBJECT_ID选择率非常好(相当于主键),因为缺乏多个索引,执行计划选择全表扫描+HASH_JOIN的方式针对此SQL是严重低效的,即使执行效率还能接受,平均单次执行时间需要0.237秒,但是309K的BUFFER GET。SQL执行效率高不等于一定是高效率的。

优化建议:T表谓词创建索引,返回行数少(驱动表),剩余表连接列上创建索引(被驱动表),使得SQL走Nested Loop关联方式,小结果集驱动大结果集,最终只需要4000多个BUFFER即可。

模拟案例SQL:

create index t_idx01 on t(object_id);
create index t1_idx01 on t1(data_object_id);
create index t2_idx01 on t2(data_object_id);
create index t3_idx01 on t3(data_object_id);

Plan hash value: 3722872203

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	   |	  1 |	     |	 782 (100)|	     |	    1 |00:00:00.01 |	4018 |
|   1 |  HASH UNIQUE			|	   |	  1 |	   1 |	 782   (1)| 00:00:10 |	    1 |00:00:00.01 |	4018 |
|   2 |   NESTED LOOPS OUTER		|	   |	  1 |	   1 |	 781   (0)| 00:00:10 |	    1 |00:00:00.01 |	4018 |
|   3 |    NESTED LOOPS OUTER		|	   |	  1 |	   1 |	 522   (0)| 00:00:07 |	    1 |00:00:00.01 |	2680 |
|   4 |     NESTED LOOPS OUTER		|	   |	  1 |	   1 |	 263   (0)| 00:00:04 |	    1 |00:00:00.01 |	1342 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T	   |	  1 |	   1 |	   4   (0)| 00:00:01 |	    1 |00:00:00.01 |	   4 |
|*  6 |       INDEX RANGE SCAN		| T_IDX01  |	  1 |	   1 |	   3   (0)| 00:00:01 |	    1 |00:00:00.01 |	   3 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| T1	   |	  1 |	   1 |	 259   (0)| 00:00:04 |	    1 |00:00:00.01 |	1338 |
|*  8 |       INDEX RANGE SCAN		| T1_IDX01 |	  1 |	 258 |	   2   (0)| 00:00:01 |	 4608 |00:00:00.01 |	  12 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | T2	   |	  1 |	   1 |	 259   (0)| 00:00:04 |	    1 |00:00:00.01 |	1338 |
|* 10 |      INDEX RANGE SCAN		| T2_IDX01 |	  1 |	 258 |	   2   (0)| 00:00:01 |	 4608 |00:00:00.01 |	  12 |
|* 11 |    TABLE ACCESS BY INDEX ROWID	| T3	   |	  1 |	   1 |	 259   (0)| 00:00:04 |	    1 |00:00:00.01 |	1338 |
|* 12 |     INDEX RANGE SCAN		| T3_IDX01 |	  1 |	 258 |	   2   (0)| 00:00:01 |	 4608 |00:00:00.01 |	  12 |
------------------------------------------------------------------------------------------------------------------------------

很多时候,执行效率浪费资源严重的TOP SQL往往被日益逐渐增强的系统资源所掩盖,记得一位前辈说过的话,TOP SQL不要看单纯的执行效率,要看所谓的”TOP”来综合判断是否有优化的余地,例如参考平均buffer get与平均返回行数等其他SQL性能指标,是否是合适,还是“血亏”。

ROW CACHE LOCK导致数据库CPU高案例分析

近期整理案例,发现前不久同事反馈一个数据库服务器CPU居高不下,无法找到根因过来询问,详情如下:

EVENT                                      COUNT(*)
---------------------------------------- ----------
row cache lock                                 1007
                                                213
buffer busy waits                               165
log file switch (checkpoint incomplete)         137
free buffer waits                               114
db file async I/O submit                         75
log file sync                                    73
db file sequential read                          62
read by other session                            36
log file parallel write                          15
resmgr:cpu quantum                               15
enq: TX - row lock contention                    13
enq: HW - contention                              9
direct path read                                  8
enq: SQ - contention                              4
latch: undo global data                           3
write complete waits                              2
log file sequential read                          1
null event                                        1
db file scattered read                            1
Log archive I/O                                   1
LNS wait on SENDREQ                               1
enq: US - contention                              1

从ASH中看到,CPU高的时间段内,等待事件最多的为ROW CACHE LOCK,

TIME                              USER_ID SQL_ID        EVENT                                            P1
------------------------------ ---------- ------------- ---------------------------------------- ----------
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 c25ar03n0p5sz row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 11xs00r4qzb7n row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13
20200929 01:11:25                      91 7265mcg5kwyc3 row cache lock                                   13

确认该时间段内的具体SQL,发现均存在SEQUENCE调用

SQL_ID          COUNT(*)
------------- ----------
7265mcg5kwyc3        357
11xs00r4qzb7n        341
c25ar03n0p5sz        277
f9ym730zzf1s2         12
9dnygr7asqmy9         12
dksd9txy8vuk6          6
9qzwmgp034kx2          2

SQL> SELECT DISTINCT sql_id,
                sql_text
FROM v$sql
WHERE sql_id IN ('dksd9txy8vuk6',
                 '11xs00r4qzb7n',
                 '9qzwmgp034kx2',
                 '9dnygr7asqmy9',
                 '7265mcg5kwyc3',
                 'c25ar03n0p5sz',
                 'f9ym730zzf1s2');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------
9qzwmgp034kx2 INSERT INTO opfqresultdata VALUES(OPFQRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 )
f9ym730zzf1s2 select SUBSFLT_HISTORY_SEQUENCE_ID.nextval from dual
7265mcg5kwyc3 INSERT INTO pnrresultdata VALUES(PNRRESULTDATA_SEQUENCE_ID.NEXTVAL,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 )
c25ar03n0p5sz INSERT INTO etresultdata VALUES(ETRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 )
9dnygr7asqmy9 select SUBSFLTREQUEST_SEQUENCE_ID.nextval from dual
11xs00r4qzb7n INSERT INTO unknowdata VALUES(UNKNOWDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 )
dksd9txy8vuk6 INSERT INTO fltresultdata VALUES(FLTRESULTDATA_SEQUENCE_ID.Nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:1

SQL> select DISTINCT P1
FROM
  (SELECT to_char(SAMPLE_TIME,'YYYYMMDD hh24:mi:ss') time,
          USER_ID,
          SQL_ID,
          EVENT,
          P1
   FROM dba_hist_active_sess_history
   WHERE SAMPLE_TIME>to_date('20200929 01:00:00','YYYYMMDD hh24:mi:ss')
     AND SAMPLE_TIME<to_date('20200929 01:15:00','YYYYMMDD hh24:mi:ss')
     AND EVENT = 'row cache lock');


        P1
----------
        13  --正是dc_sequences

SQL> select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=13;

PARAMETER                              GETS  GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- -------------
dc_sequences                      766929021       5065     766929020

发现此sequence中的cache size均为0,出现row cache lock与cpu高便不足为奇

将频繁调用的sequence cache size调整至500后,等待与cpu高的现象随之解决,不再复现。

SQL执行计划突变案例分析

前几日,同事发现一套库数据库的活动会话数激增,大量活动会话集中等待gc buffer busy acquire上,如下图所示

经过更一步的查看,发现这些等待均在sql_id:f7hnmtx8bd5q3上,利用如下sql进行查看发现:

SQL_TEXT如下:
SELECT *
FROM
  (SELECT p.*
   FROM t p,
        TD_PLAN_LEG d
   WHERE p.THR_DEP = d.THR_DEP
     AND p.THR_ARR = d.THR_ARR
     AND p.FLTNO = d._FLTNO
     AND pDATE = d.DATE
     AND (p.THR_DEP = :1
          OR p.THR_ARR = :2)
     AND nvl(d.STATUS_inner, ' ') != '??'
     AND d.LT_ID IS NULL
     AND nvl(d.STATUS_inner, ' ') != '??'
     AND p.OP_TM > :3
   ORDER BY p.OP_TM)
WHERE ROWNUM <= :4


SQL历史执行效率:
TIME      PLAN_HASH_VALUE EXECU_D        BG_D        DR_D      ET_D      CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME GET_ONETIME   ROWS_PRO ROWS_ONETIME ET_MS_ONCE
------------ --------------- ------- ----------- ----------- --------- --------- ------- --------- ------- ------- ----------- ---------- ------------ ----------
2020092201        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092201        2495209816    3333  1351590946           0      2403      2409       0         0       0       0      405518        261            0        721
2020092202        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092202        2495209816    3238  1381884655           0      2449      2477       0         0       0       0      426771         82            0        756
2020092203        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092203        2495209816    3228  1380940555           0      2464      2475       0         0       0       0      427801         12            0        763
2020092204        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092204        2495209816    3249           0           0      2469      2486       0         0       0       0           0          0            0        760
2020092205        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092205        2495209816    3239  1401077673           0      2496      2510       0         0       0       0      432565          0            0        771
2020092206        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092206        2495209816    3240  1374013765           0      2449      2467       0         0       0       0      424078          0            0        756
2020092207        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092207        2495209816    3319           0           0      2584      2589       0         0       0       0           0        181            0        778
2020092208        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092208        2495209816    3656  1628720696           0      2827      2826       0         0       0       0      445493       1439            0        773
2020092209        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092209        2495209816    3238  1434887002           0      2610      2611       0         0       0       0      443140       2253            1        806
2020092210        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092210        2495209816    3240           0           0      2623      2629       0         0       0       0           0       2046            1        809
2020092211        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092211        2495209816    3240  1442329567           0      2672      2669       0         0       0       0      445163       2659            1        825
2020092212        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092212        2495209816    3240  1437033232           0      2676      2675       0         0       0       0      443529       2561            1        826
2020092213        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092213        2495209816    3222           0           0      2687      2689       0         0       0       0           0       2888            1        834
2020092214        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092214        2495209816    3240  1440743191           0      2706      2707       0         0       0       0      444674       3367            1        835
2020092215        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092215        2495209816    3243  1444795359           0      2747      2747       0         0       0       0      445512       2677            1        847
2020092216        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092216        2495209816    3243           0           0      2742      2749       0         0       0       0           0       2975            1        846
2020092217        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092217        2495209816    3256  1464276872           0      2774      2771       0         0       0       0      449716       2929            1        852
2020092218        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092218        2495209816    3240  1459846368           0      2802      2801       0         0       0       0      450570       3543            1        865
2020092219        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092219        2495209816    3240           0           0      2806      2806       0         0       0       0           0       3022            1        866
2020092220        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092220        2495209816    3221  1443354745           0      2819      2820       0         0       0       0      448108       2694            1        875
2020092221        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092221        2495209816    3239  1458055393           0      2891      2879       0         0       0       0      450156       2592            1        892
2020092222        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092222        2495209816    3240           0           0      2883      2887       0         0       0       0           0       2059            1        890
2020092223        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092223        2495209816    3240  1458681730           0      2926      2928       0         0       0       0      450210       1936            1        903
2020092300        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092300        2495209816    3238  1437599879           0      2925      2923       0         0       0       0      443978       1370            0        903
2020092301        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092301        2495209816    3251  1265725098           0      2330      2326       0         0       0       0      389334        695            0        717
2020092302        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092302        2495209816    3240  1322541308           0      2443      2447       0         0       0       0      408192        223            0        754
2020092303        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092303        2495209816    3240           0           0      2466      2471       0         0       0       0           0         41            0        761
2020092304        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092304        2495209816    3240  1309926437           0      2432      2431       0         0       0       0      404298          0            0        751
2020092305        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092305        2495209816    3250  1347086218           0      2466      2458       0         0       0       0      414488          0            0        759
2020092306        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092306        2495209816    3248  1327059467           0      2452      2452       0         0       0       0      408577          0            0        755
2020092307        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092307        2495209816    3242           0           0      2491      2488       0         0       0       0           0        194            0        768
2020092308        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092308        2495209816    3245  1405256862           0      2544      2538       0         0       0       0      433053       1625            1        784
2020092309        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092309        2495209816    3246  1421208667           0      2590      2579       0         0       0       0      437834       2518            1        798
2020092310        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092310        2495209816    3248           0           0      2619      2609       0         0       0       0           0       1961            1        806
2020092311        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092311        2495209816    3239  1426602319           0      2637      2632       0         0       0       0      440445       2673            1        814
2020092312        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092312        2495209816    3195  1403306564           0      2610      2603       0         0       0       0      439220       3029            1        817
2020092313        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092313        2495209816    3247           0           0      2698      2687       0         0       0       0           0       2976            1        831
2020092314        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092314        2495209816    3238  1418853144           0      2696      2690       0         0       0       0      438188       3496            1        833
2020092315        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092315        2495209816    3236  1443916772           0      2736      2728       0         0       0       0      446204       3600            1        846
2020092316        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092316        2495209816    3239           0           0      2731      2726       0         0       0       0           0       3630            1        843
2020092317        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092317        2495209816    3239  1435088513           0      2780      2768       0         0       0       0      443065       3446            1        858
2020092318        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092318        2495209816    3256  1422988985           0      2794      2782       0         0       0       0      437036       3043            1        858
2020092319        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092319        2495209816    3239           0           0      2816      2811       0         0       0       0           0       3108            1        869
2020092320        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092320        2495209816    3237  1414992685           0      2818      2813       0         0       0       0      437131       2993            1        870
2020092321        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092321        2495209816    3239  1450253448           0      2867      2859       0         0       0       0      447747       2686            1        885
2020092322        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092322        2495209816    3239           0           0      2866      2853       0         0       0       0           0       2661            1        885
2020092323        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092323        2495209816    3239  1440828417           0      2906      2889       0         0       0       0      444837       2007            1        897
2020092400        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092400        2495209816    3256  1405971757           0      2892      2884       0         0       0       0      431810       1569            0        888
2020092401        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092401        2495209816    3239   262264721           0      2332      2331       0         0       0       0       80971        570            0        720
2020092402        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092402        2495209816    3204  1328880815           0      2433      2428       0         0       0       0      414757        252            0        759
2020092403        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092403        2495209816    3240  1367144118           0      2459      2453       0         0       0       0      421958        101            0        759
2020092404        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092404        2495209816    3237  1318574101           0      2437      2432       0         0       0       0      407344         35            0        753
2020092405        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092405        2495209816    3220           0           0      2438      2435       0         0       0       0           0          0            0        757
2020092406        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092406        2495209816    3254  1346142138           0      2480      2469       0         0       0       0      413688          0            0        762
2020092407        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092407        2495209816    3238  1397020466           0      2503      2492       0         0       0       0      431445        160            0        773
2020092408        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092408        2495209816    3235           0           0      2529      2520       0         0       0       0           0       1401            0        782
2020092409        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092409        2495209816    3239  1424896212           0      2566      2556       0         0       0       0      439919       2275            1        792
2020092410        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092410        2495209816    3237  1421132007           0      2572      2569       0         0       0       0      439027       1956            1        795
2020092411        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092411        2495209816    3248           0           0      2629      2614       0         0       0       0           0       2913            1        809
2020092412        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092412        2495209816    3249  1448536971           0      2666      2651       0         0       0       0      445841       2722            1        820
2020092413        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092413        2495209816    3238  1437662381           0      2675      2668       0         0       0       0      443997       2445            1        826
2020092414        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092414        2495209816    3202           0           0      2680      2672       0         0       0       0           0       3238            1        837
2020092415        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092415        2495209816    3256  1450466091           0      2768      2758       0         0       0       0      445475       2920            1        850
2020092416        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092416        2495209816    3240  1450111226           0      2794      2779       0         0       0       0      447565       3109            1        862
2020092417        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092417        2495209816    3237           0           0      2824      2804       0         0       0       0           0       2818            1        873
2020092418        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092418        2495209816    3239  1424042026           0      2840      2831       0         0       0       0      439655       3346            1        877
2020092419        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092419        2495209816    3256  1435714284           0      2894      2885       0         0       0       0      440944       3120            1        889
2020092420        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092420        2495209816    3237           0           0      2914      2902       0         0       0       0           0       2788            1        900
2020092421        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092421        2495209816    3240  1433834204           0      2967      2946       0         0       0       0      442541       2857            1        916
2020092422        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092422        2495209816    3200  1413189546           0      2938      2924       0         0       0       0      441622       2346            1        918
2020092423        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092423        2495209816    3256           0           0      3032      3014       0         0       0       0           0       2108            1        931
2020092500        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092500        2495209816    3238  1417260989           0      3005      2982       0         0       0       0      437696       1358            0        928
2020092501        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092501        2495209816    3233  1296496694           0      2456      2444       0         0       0       0      401020        416            0        760
2020092502        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092502        2495209816    3220   473519624           0      2521      2518       0         0       0       0      147056        164            0        783
2020092503        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092503        2495209816    3257  1419336329           0      2546      2548       0         0       0       0      435780          9            0        782
2020092504        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092504        2495209816    3239  1410697902           0      2554      2548       0         0       0       0      435535          0            0        789
2020092505        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092505        2495209816    3239           0           0      2562      2548       0         0       0       0           0          0            0        791
2020092506        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092506        2495209816    3238  1422005253           0      2555      2551       0         0       0       0      439162          0            0        789
2020092507        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092507        2495209816    3244  1442245766           0      2591      2583       0         0       0       0      444589        276            0        799
2020092508        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092508        2495209816    3247           0           0      2642      2634       0         0       0       0           0       1679            1        814
2020092509        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092509        2495209816    3236  1467804298           0      2665      2656       0         0       0       0      453586       2599            1        824
2020092510        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092510        2495209816    3238  1462271228           0      2677      2670       0         0       0       0      451597       2009            1        827
2020092511        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092511        2495209816    3235           0           0      2720      2709       0         0       0       0           0       3010            1        841
2020092512        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092512        2495209816    3254  1465449842           0      2739      2723       0         0       0       0      450353       2555            1        842
2020092513        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092513        2495209816    3240  1459543406           0      2726      2709       0         0       0       0      450476       2664            1        841
2020092514        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092514        2495209816    3185           0           0      2702      2694       0         0       0       0           0       3784            1        848
2020092515        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092515        2495209816    3240  1447314954           0      2746      2742       0         0       0       0      446702       3116            1        848
2020092516        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092516        2495209816    3258  1475259620           0      2867      2821       0         0       0       0      452811       3183            1        880
2020092517        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092517        2495209816    3239           0           0      2841      2822       0         0       0       0           0       3168            1        877
2020092518        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092518        2495209816    3237  1455741217           0      2835      2815       0         0       0       0      449719       3258            1        876
2020092519        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092519        2495209816    3237  1456570263           0      2858      2846       0         0       0       0      449975       3135            1        883
2020092520        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092520        2495209816    3238           0           0      2881      2869       0         0       0       0           0       3033            1        890
2020092521        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092521        2495209816    3238  1464946512           0      2912      2898       0         0       0       0      452423       2918            1        899
2020092522        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092522        2495209816    3256  1468725511           0      2946      2929       0         0       0       0      451083       2386            1        905
2020092523        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092523        2495209816    3240           0           0      2948      2936       0         0       0       0           0       2258            1        910
2020092600        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092600        2495209816    3238  1437269657           0      2915      2904       0         0       0       0      443876       1648            1        900
2020092601        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092601        2495209816    3239  1358782568           0      2399      2387       0         0       0       0      419507        577            0        741
2020092602        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092602        2495209816    3240  1385828878           0      2525      2519       0         0       0       0      427725        158            0        779
2020092603        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092603        2495209816    3203  1391299844           0      2507      2503       0         0       0       0      434374         23            0        783
2020092604        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092604        2495209816    3237  1351047932           0      2500      2497       0         0       0       0      417377          0            0        772
2020092605        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092605        2495209816    3243           0           0      2529      2522       0         0       0       0           0          0            0        780
2020092606        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092606        2495209816    3235  1392017833           0      2526      2517       0         0       0       0      430299          0            0        781
2020092607        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092607        2495209816    3239  1424101671           0      2559      2544       0         0       0       0      439673        189            0        790
2020092608        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092608        2495209816    3239   496269954           0      2593      2583       0         0       0       0      153217       1619            0        800
2020092609        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092609        2495209816    3244  1473823605           0      2627      2617       0         0       0       0      454323       2200            1        810
2020092610        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092610        2495209816    3253  1473324036           0      2670      2658       0         0       0       0      452912       1995            1        821
2020092611        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092611        2495209816    3238           0           0      2716      2707       0         0       0       0           0       2491            1        839
2020092612        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092612        2495209816    3237  1477721737           0      2741      2733       0         0       0       0      456510       2460            1        847
2020092613        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092613        2495209816    3239  1483323345           0      2768      2755       0         0       0       0      457957       2757            1        855
2020092614        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092614        2495209816    3241  1487746111           0      2808      2792       0         0       0       0      459039       3145            1        866
2020092615        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092615        2495209816    3214  1478749632           0      2820      2809       0         0       0       0      460096       2790            1        878
2020092616        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092616        2495209816    3239           0           0      2869      2858       0         0       0       0           0       3045            1        886
2020092617        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092617        2495209816    3240  1483068741           0      2880      2871       0         0       0       0      457737       2691            1        889
2020092618        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092618        2495209816    3256  1496586513           0      2925      2913       0         0       0       0      459640       3351            1        898
2020092619        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092619        2495209816    3240   686804354           0      2943      2923       0         0       0       0      211977       2956            1        908
2020092620        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092620        2495209816    3239  1492171972           0      2955      2941       0         0       0       0      460689       2728            1        912
2020092621        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092621        2495209816    3242  1499007445           0      2971      2961       0         0       0       0      462371       2518            1        917
2020092622        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092622        2495209816    3254           0           0      2983      2974       0         0       0       0           0       2323            1        917
2020092623        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092623        2495209816    3201  1489464912           0      3019      3006       0         0       0       0      465312       1919            1        943
2020092700        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092700        2495209816    3239  1489900471           0      3025      3011       0         0       0       0      459988       1195            0        934
2020092701        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092701        2495209816    3240  1357716042           0      2434      2429       0         0       0       0      419048        521            0        751
2020092702        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092702        2495209816    3244  1448164591           0      2566      2560       0         0       0       0      446413        276            0        791
2020092703        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092703        2495209816    3252           0           0      2571      2559       0         0       0       0           0         72            0        791
2020092704        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092704        2495209816    3221  1417052962           0      2503      2501       0         0       0       0      439942          2            0        777
2020092705        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092705        2495209816    3240  1435598638           0      2533      2534       0         0       0       0      443086          0            0        782
2020092706        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092706        2495209816    3244           0           0      2527      2528       0         0       0       0           0          0            0        779
2020092707        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092707        2495209816    3254  1470802612           0      2605      2599       0         0       0       0      451998        204            0        801
2020092708        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092708        2495209816    3239  1466922394           0      2601      2589       0         0       0       0      452894       1735            1        803
2020092709        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092709        2495209816    3238  1462310461           0      2647      2640       0         0       0       0      451609       2688            1        818
2020092710        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092710        2495209816    3244  1460262764           0      2656      2651       0         0       0       0      450143       2056            1        819
2020092711        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092711        2495209816    3254  1485315112           0      2720      2708       0         0       0       0      456458       2956            1        836
2020092712        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092712        2495209816    3239  1475673310           0      2718      2710       0         0       0       0      455595       2987            1        839
2020092713        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092713        2495209816    3240  1480502924           0      2752      2741       0         0       0       0      456945       3107            1        849
2020092714        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092714        2495209816    3242           0           0      2770      2758       0         0       0       0           0       3774            1        855
2020092715        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092715        2495209816    3254  1485748247           0      2802      2783       0         0       0       0      456591       3499            1        861
2020092716        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092716        2495209816    3185           0           0      2774      2759       0         0       0       0           0       3173            1        871
2020092717        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092717        2495209816    3239  1488000864           0      2849      2839       0         0       0       0      459401       3113            1        880
2020092718        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092718        2495209816    3238  1480569006           0      2868      2860       0         0       0       0      457248       3294            1        886
2020092719        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092719        2495209816    3243  1479350901           0      2897      2886       0         0       0       0      456167       2813            1        893
2020092720        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092720        2495209816    3240  1466487471           0      2915      2902       0         0       0       0      452620       2953            1        900
2020092721        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092721        2495209816    3254  1473511907           0      2981      2966       0         0       0       0      452831       2821            1        916
2020092722        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092722        2495209816    3240           0           0      2987      2976       0         0       0       0           0       2468            1        922
2020092723        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092723        2495209816    3240  1485591039           0      3023      3010       0         0       0       0      458516       2491            1        933
2020092800        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092800        2495209816    3239  1492094762           0      3032      3023       0         0       0       0      460665       1508            0        936
2020092801        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092801        2495209816    3244  1381156470           0      2472      2464       0         0       0       0      425757        752            0        762
2020092802        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092802        2495209816    3251           0           0      2585      2581       0         0       0       0           0        265            0        795
2020092803        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092803        2495209816    3239  1465140224           0      2599      2588       0         0       0       0      452343         10            0        802
2020092804        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092804        2495209816    3190  1446640595           0      2553      2540       0         0       0       0      453492          0            0        800
2020092805        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092805        2495209816    3253           0           0      2607      2600       0         0       0       0           0          0            0        801
2020092806        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092806        2495209816    3240  1467000606           0      2597      2590       0         0       0       0      452778          0            0        802
2020092807        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092807        2495209816    3242  1472818082           0      2610      2604       0         0       0       0      454293        183            0        805
2020092808        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092808        2495209816    3236           0           0      2631      2616       0         0       0       0           0       1622            1        813
2020092809        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092809        2495209816    3240  1489167496           0      2670      2650       0         0       0       0      459620       2737            1        824
2020092810        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092810        2495209816    3239  1480634051           0      2677      2666       0         0       0       0      457127       2304            1        826
2020092811        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092811        2495209816    3240           0           0      2729      2716       0         0       0       0           0       3116            1        842
2020092812        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092812        2495209816    3244  1490574897           0      2738      2727       0         0       0       0      459487       2975            1        844
2020092813        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092813        2495209816    3239  1495331918           0      2775      2758       0         0       0       0      461665       3305            1        857
2020092814        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092814        2495209816    3253           0           0      2811      2791       0         0       0       0           0       3988            1        864
2020092815        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092815        2495209816    3239  1494368501           0      2810      2799       0         0       0       0      461367       3532            1        867
2020092816        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092816        2495209816    3208           0           0      2798      2786       0         0       0       0           0       3395            1        872
2020092817        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092817        2495209816    3253  1503663984           0      2890      2868       0         0       0       0      462239       3418            1        888
2020092818        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092818        2495209816    3239  1492963087           0      2880      2870       0         0       0       0      460933       3405            1        889
2020092819        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092819        2495209816    3239           0           0      2924      2914       0         0       0       0           0       3268            1        903
2020092820        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092820        2495209816    3243  1502574273           0      2932      2925       0         0       0       0      463328       3413            1        904
2020092821        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092821        2495209816    3254  1511131260           0      2960      2945       0         0       0       0      464392       2994            1        910
2020092822        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092822        2495209816    3240           0           0      2963      2950       0         0       0       0           0       2852            1        915
2020092823        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092823        2495209816    3240  1511966632           0      3007      2992       0         0       0       0      466656       2395            1        928
2020092900        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092900        2495209816    3206  1483716066           0      2963      2954       0         0       0       0      462794       1355            0        924
2020092901        1636324798       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092901        2495209816    3254           0           0      2412      2406       0         0       0       0           0        406            0        741
2020092902        1636324798      99   352210787    13407233     21201      2845    7253      9775       0       6     3557685         37            0     214150
2020092902        2495209816    2178   981309957           0      1711      1707       0         0       0       0      450556         66            0        786
2020092903        1636324798     318  1160978121    38861241     60087     10011    3682     42585       0      31     3650875         56            0     188954
2020092903        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092904        1636324798     315  1138406066    37854487     59574     10038    3329     41809       0      29     3613988          0            0     189123
2020092904        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092905        1636324798     313  1143701796    38668999     59974     10059    3587     41987       0      33     3653999          0            0     191611
2020092905        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092906        1636324798     306           0    42268789     62487      9733    4179     44183       0      31           0          0            0     204205
2020092906        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092907        1636324798     323  1158824752    41644602     61905     10292    4001     43486       0      40     3587693        201            1     191657
2020092907        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092908        1636324798     309  1167837295    37852269     63066     10364    3967     44390       0      42     3779409       2124            7     204097
2020092908        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092909        1636324798     310  1118885638    36122238     63801      9831    4341     45415       0      46     3609309       3514           11     205811
2020092909        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092910        1636324798     275           0    33482927     65518      9199    9300     43109       0      48           0       3574           13     238246
2020092910        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092911        1636324798     235   915691746    71725197     66676      9162    7097     46978       0      44     3896561       4472           19     283727
2020092911        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092912        1636324798     257   942550994    60478871     65961      9210    6233     46797       0      42     3667514       5398           21     256657
2020092912        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092913        1636324798     163   646322619    45033189     68480      8699   10553     46613       0      86     3965169       4002           25     420122
2020092913        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092914        1636324798     178   655193101    34549897     67433      8435    9002     46876       0      78     3680860       4798           27     378839
2020092914        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092915        1636324798     162           0    42181497     69005      8512    9628     48119       0      73           0       5236           32     425955
2020092915        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092916        1636324798     180   659119160    37016987     67935      8320    8938     47657       0      74     3661773       6284           35     377415
2020092916        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092917        1636324798     178   668652544    36068743     66633      8204    8260     47129       0      66     3756475       4672           26     374341
2020092917        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092918        1636324798     182   690411953    47612967     68886      8662    9144     48329       0      64     3793472       6218           34     378496
2020092918        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092919        1636324798     180   716579287    38972462     68804      8613    8099     49062       0      68     3980996       5828           32     382243
2020092919        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092920        1636324798     198   725411011    42912011     66999      8657    8366     46635       0      72     3663692       4517           23     338377
2020092920        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092921        1636324798     182           0    56233403     68130      9001    9442     46655       0      79           0       3744           21     374340
2020092921        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092922        1636324798     200   761626806    56780398     67513      9089    8585     47243       0      60     3808134       3849           19     337564
2020092922        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020092923        1636324798     217   785661905    49927309     66720      9066    7530     46950       0      59     3620562       3528           16     307464
2020092923        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093000        1636324798     203   827248817    65561662     67630      9780    8513     46470       0      70     4075117       2246           11     333155
2020093000        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093001        1636324798     325  1200248776   124828228     62141     10674    6906     40984       0      19     3693073        619            2     191202
2020093001        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093002        1636324798     310  1110431303    39739255     57335      9643    2766     40456       0      23     3582036        185            1     184951
2020093002        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093003        1636324798     320  1149651569    39694801     61120      9942    3466     43492       0      27     3592661         47            0     191000
2020093003        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093004        1636324798     327           0    40854731     62249     10587    3609     43766       0      31           0         21            0     190365
2020093004        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093005        1636324798     319  1125342936    38863217     59590     10107    3507     41533       0      32     3527721          0            0     186801
2020093005        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093006        1636324798     309  1145849658    38889950     62572     10367    3745     44353       0      34     3708251          0            0     202500
2020093006        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093007        1636324798     327  1171387941    39245953     63327     10553    3852     44758       0      40     3582226        241            1     193662
2020093007        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093008        1636324798     296           0    38143234     64608     10248    4218     45887       0      41           0       2377            8     218271
2020093008        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093009        1636324798     291  1070234826    36419287     65024      9485    4331     46952       0      42     3677783       3606           12     223449
2020093009        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0
2020093010        1636324798     297  1060264318    35236356     65779      9293    4500     47678       0      43     3569914       3473           12     221476
2020093010        2495209816       0           0           0         0         0       0         0       0       0           0          0            0          0

404 rows selected.

从DBA_HIST_SQLSTAT中看到,该SQL有两个执行计划:

PLAH_HASH_VALUE:(1) 1636324798 (2)2495209816其中2495209816效率相对较高,1636324798一直存在但是在9月29日之前并没有启用过,一直在用2495209816,直到2020092902(2点)开始启用,并且在2020092903(3点)完全使用。但是ORACLE为何突然使用了差的执行计划了?


Inst: 1   Child: 0    Plan hash value: 2495209816

                      --------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                       | Name                         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      --------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                |                              |        |       |   372K(100)|          |       |       |          |
                      |*  1 |  COUNT STOPKEY                  |                              |        |       |            |          |       |       |          |
                      |   2 |   VIEW                          |                              |      1 |   176 |   372K  (1)| 01:14:32 |       |       |          |
                      |*  3 |    SORT ORDER BY STOPKEY        |                              |      1 |   125 |   372K  (1)| 01:14:32 |  1024 |  1024 |          |
                      |   4 |     NESTED LOOPS                |                              |      1 |   125 |   372K  (1)| 01:14:32 |       |       |          |
                      |   5 |      NESTED LOOPS               |                              |    120K|   125 |   372K  (1)| 01:14:32 |       |       |          |
                      |*  6 |       TABLE ACCESS FULL         | T1                           |    120K|  4482K| 10211   (1)| 00:02:03 |       |       |          |
                      |*  7 |       INDEX RANGE SCAN          | T_POSITIONS_KEY              |      1 |       |     3   (0)| 00:00:01 |       |       |          |
                      |*  8 |      TABLE ACCESS BY INDEX ROWID| T                            |      1 |    87 |     3   (0)| 00:00:01 |       |       |          |
                      --------------------------------------------------------------------------------------------------------------------------------------------

Inst: 1   Child: 38   Plan hash value: 1636324798

                      ----------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation               | Name               | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
                      ----------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT        |                    |        |       |       |  1039K(100)|          |       |       |          |
                      |*  1 |  COUNT STOPKEY          |                    |        |       |       |            |          |       |       |          |
                      |   2 |   VIEW                  |                    |    118K|    19M|       |  1039K  (1)| 03:27:50 |       |       |          |
                      |*  3 |    SORT ORDER BY STOPKEY|                    |    118K|    14M|    16M|  1039K  (1)| 03:27:50 |  1024 |  1024 |          |
                      |*  4 |     HASH JOIN           |                    |    118K|    14M|  6024K|  1035K  (1)| 03:27:10 |  9516K|  3276K|   10M (0)|
                      |*  5 |      TABLE ACCESS FULL  | T1                 |    123K|  4576K|       | 10211   (1)| 00:02:03 |       |       |          |
                      |*  6 |      TABLE ACCESS FULL  | T                  |    118K|     9M|       |  1024K  (1)| 03:24:57 |       |       |          |
                      ----------------------------------------------------------------------------------------------------------------------------------

经过分析发现,此SQL涉及对象在9月28日时触发了统计信息收集,并且因此游标滚动失效,9月29日零时进行了硬解析。因数据库中绑定变量窥探关闭(_optim_peek_user_binds,被设定为FALSE),无法”窥视“直方图,根据数据分布与bind值分析,因数据倾斜严重+关闭“窥探直方图”情况下被认为平均分布,优化器选择了选择了”CBO认为效率高,实际效率差”的执行计划。针对此情形,可以利用SQL_PROFILE进行绑定(借助coe_load_sql_profile.sql)脚本绑定后

数据库GC类等待消失,SQL效率恢复至毫秒级别。