我眼中的国产数据库

发表于2021 年 01 月 12 日由liangce

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性能问题

发表于2021 年 01 月 10 日由liangce

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

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

问题时间段的AWR：

换一个角度，我们从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效率下降，延时变高。

继续阅读 →

关于QB_NAME的hint

发表于2020 年 12 月 20 日由liangce

近期，在优化一个SQL时用到了QB_NAME的HINT，此HINT我经常使用的原因目的是让子查询单独作为一个整体，与其他表关联时可以进行调整驱动顺序，举例：

SELECT b.owner,
       b.object_name,
       b.created,
       b.status
FROM albert.fc b
WHERE 1=1
  AND b.object_id IN
    (SELECT c.object_id
     FROM albert.P c
     WHERE c.owner='NFS_SN')
执行计划如下：
Plan hash value: 3977992979

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |   569 (100)| 	 |    119 |00:00:00.05 |    1445 |
|*  1 |  HASH JOIN RIGHT SEMI	     |	       |      1 |  64830 |   569  (11)| 00:00:07 |    119 |00:00:00.05 |    1445 |
|   2 |   TABLE ACCESS BY INDEX ROWID| P       |      1 |   2192 |   116   (0)| 00:00:02 |    119 |00:00:00.01 |      17 |
|*  3 |    INDEX RANGE SCAN	     | P_IDX01 |      1 |   2192 |     6   (0)| 00:00:01 |    119 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS FULL	     | FC      |      1 |   3000K|   439  (11)| 00:00:06 |    100K|00:00:00.02 |    1428 |
--------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / C@SEL$2
   3 - SEL$5DA710D3 / C@SEL$2
   4 - SEL$5DA710D3 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "B"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$2" ("P"."OWNER"))
      LEADING(@"SEL$5DA710D3" "B"@"SEL$1" "C"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "C"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "C"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   1 - access("B"."OBJECT_ID"="C"."OBJECT_ID")
   3 - access("C"."OWNER"='NFS_SN')

继续阅读 →

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

发表于2020 年 12 月 20 日由liangce

今年以来，公司多套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故障修复

发表于2020 年 11 月 11 日由liangce

近日，同事反馈一套测试测试数据库宕机，进行重启数据库操作时发现数据库无法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

发表于2020 年 11 月 05 日由liangce

同事发来一个核心业务库的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优化处理过程

发表于2020 年 10 月 30 日由liangce

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

活动会话多数在等待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优化

发表于2020 年 10 月 18 日由liangce

近期整理案例时发现，同事曾咨询过一个问题，数据库服务器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高案例分析

发表于2020 年 10 月 07 日由liangce

近期整理案例，发现前不久同事反馈一个数据库服务器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执行计划突变案例分析

发表于2020 年 10 月 05 日由liangce

前几日，同事发现一套库数据库的活动会话数激增，大量活动会话集中等待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效率恢复至毫秒级别。