判定大查询导致第二次执行慢的问题

问题现象

DBMS_STATS 收集统计信息时，同一 SQL 执行第二次变慢/出现快慢交替的情况。

示例

1. 第一次执行 SQL 语句如下。

   obclinet > SELECT COUNT(*) FROM comments as c, posts as p, postLinks as pl, postHistory as ph, votes as v, badges as b, users as u WHERE p.Id = pl.RelatedPostId AND b.UserId = u.Id AND c.UserId = u.Id AND p.Id = v.PostId AND p.Id = c.PostId AND p.Id = ph.PostId AND c.Score=0 AND c.CreationDate>='2010-07-26 17:09:48' AND p.PostTypeId=1 AND p.AnswerCount>=0 AND p.CommentCount>=0 AND p.CommentCount<=14 AND pl.CreationDate>='2010-10-27 10:02:57' AND pl.CreationDate<='2014-09-04 17:23:50' AND ph.CreationDate<='2014-09-11 20:09:41' AND v.CreationDate>='2010-07-21 00:00:00' AND v.CreationDate<='2014-09-14 00:00:00';
   

   输出结果如下。

    +-----------+
    | COUNT(*)  |
    +-----------+
    | 299574955 |
    +-----------+
    1 row in set (41.14 sec)
   

2. 执行收集统计信息 PL 存储过程。

   obclient > CALL DBMS_STATS.GATHER_SCHEMA_STATS('stats');
   

3. 第二次执行该 SQL 语句。

   obclinet > SELECT COUNT(*) FROM comments as c, posts as p, postLinks as pl, postHistory as ph, votes as v, badges as b, users as u WHERE p.Id = pl.RelatedPostId AND b.UserId = u.Id AND c.UserId = u.Id AND p.Id = v.PostId AND p.Id = c.PostId AND p.Id = ph.PostId AND c.Score=0 AND c.CreationDate>='2010-07-26 17:09:48' AND p.PostTypeId=1 AND p.AnswerCount>=0 AND p.CommentCount>=0 AND p.CommentCount<=14 AND pl.CreationDate>='2010-10-27 10:02:57' AND pl.CreationDate<='2014-09-04 17:23:50' AND ph.CreationDate<='2014-09-11 20:09:41' AND v.CreationDate>='2010-07-21 00:00:00' AND v.CreationDate<='2014-09-14 00:00:00';
   

   输出结果如下。

    +-----------+
    | COUNT(*)  |
    +-----------+
    | 299574955 |
    +-----------+
    1 row in set (2 min 55.17 sec)
   

4. 租户关闭 SPM 功能。

   obclient > set @@global.optimimzer_use_sql_plan_baselines = '0';
   

   1. 第一次执行该 SQL 语句。

      obclinet > SELECT COUNT(*) FROM comments as c, posts as p, postLinks as pl, postHistory as ph, votes as v, badges as b, users as u WHERE p.Id = pl.RelatedPostId AND b.UserId = u.Id AND c.UserId = u.Id AND p.Id = v.PostId AND p.Id = c.PostId AND p.Id = ph.PostId AND c.Score=0 AND c.CreationDate>='2010-07-26 17:09:48' AND p.PostTypeId=1 AND p.AnswerCount>=0 AND p.CommentCount>=0 AND p.CommentCount<=14 AND pl.CreationDate>='2010-10-27 10:02:57' AND pl.CreationDate<='2014-09-04 17:23:50' AND ph.CreationDate<='2014-09-11 20:09:41' AND v.CreationDate>='2010-07-21 00:00:00' AND v.CreationDate<='2014-09-14 00:00:00';
      

      输出结果如下。

        +-----------+
        | COUNT(*)  |
        +-----------+
        | 299574955 |
        +-----------+
        1 row in set (1 min 1.01 sec)
      

   2. 第二次执行该 SQL 语句。

      obclinet > SELECT COUNT(*) FROM comments as c, posts as p, postLinks as pl, postHistory as ph, votes as v, badges as b, users as u WHERE p.Id = pl.RelatedPostId AND b.UserId = u.Id AND c.UserId = u.Id AND p.Id = v.PostId AND p.Id = c.PostId AND p.Id = ph.PostId AND c.Score=0 AND c.CreationDate>='2010-07-26 17:09:48' AND p.PostTypeId=1 AND p.AnswerCount>=0 AND p.CommentCount>=0 AND p.CommentCount<=14 AND pl.CreationDate>='2010-10-27 10:02:57' AND pl.CreationDate<='2014-09-04 17:23:50' AND ph.CreationDate<='2014-09-11 20:09:41' AND v.CreationDate>='2010-07-21 00:00:00' AND v.CreationDate<='2014-09-14 00:00:00';
      

      输出结果如下。

        +-----------+
        | COUNT(*)  |
        +-----------+
        | 299574955 |
        +-----------+
        1 row in set (4 min 30.80 sec)
      

问题原因

对于已经执行过的 SQL，在第二次执行前，会通过执行计划预判是否为大查询，如果预判到为大查询，会送到专门的租户工作线程分组 G100。这个组里的请求在处理过程中，如果判断到执行时间长于 large_query_threshold 此阈值默认值为 5s，会进行 sleep 来限速。其目的是防止大查询请求挤占过多 CPU 资源，影响小请求的处理。

可以通过查询 GV$OB_SQL_AUDIT 的 USER_GROUP 是否等于 100 来判断是否在 G100 租户工作线程组执行，语句如下。

obclient [oceanbase]> select * from gv$ob_sql_audit where user_group = 100;

问题的风险及影响

SQL 查询第二次执行时间变长。

影响的版本

OceanBase 数据库 V4.1.x 和 V4.2.x 所有版本。

解决方法及规避方式

可以通过提前调大 large_query_threshold 来避免对大查询的 CPU 限制，命令如下。

obclient [oceanbase]> ALTER SYSTEM SET large_query_threshold= '40';
Query OK, 0 rows affected (0.102 sec)

不过这样造成的影响是，大查询可能会挤占小请求的 CPU 资源，使小请求得不到及时处理。