多分区读取直方图的导致 SQL 偶发执行时间过长

问题现象

生产业务系统，发现某一条查询 SQL 执行时间会有一定概率超过 3s，因为业务侧发现如果 SQL 执行超过 3s 即会强制断连避免慢 SQL，但是手工执行该 SQL 发现正常情况下非常快毫秒级 RT，但是也会有一定概率出现执行很慢的情况（2-3s），查看执行慢的情况下对应的 sql audit 信息，发现 GET_PLAN_TIME 占了绝大部分的时长需要分析。

关键信息

1. 查看执行慢的情况下对应的 sql audit 信息，发现 GET_PLAN_TIME 占了绝大部分的时长。

                    IS_HIT_PLAN: 0
                   REQUEST_TIME: 1733989176285438
                   ELAPSED_TIME: 5137798
                       NET_TIME: 0
                  NET_WAIT_TIME: 2
                     QUEUE_TIME: 7
                    DECODE_TIME: 0
                  GET_PLAN_TIME: 5030458
                   EXECUTE_TIME: 107323
   

2. 用户业务表有 1024 分区表，属于分区数比较多的分区表。

3. 关键日志 grep "ob_sql_client_decorator.cpp:122" observer.log.*。

   [2024-12-12 15:39:41.270354] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=30] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654271) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.272646] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=29] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654273) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.275724] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=42] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654715) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.277767] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=39] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654602) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.279826] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=32] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654879) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.282114] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=29] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654380) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.283997] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=41] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654417) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.286229] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=11] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654985) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.288268] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=28] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654646) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.290525] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=29] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654345) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.292681] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=31] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654757) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.294543] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=33] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (655058) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.297201] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=31] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654564) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.299355] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=31] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654384) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.301583] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=38] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (655072) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.303912] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=39] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654769) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1)
   [2024-12-12 15:39:41.305943] TRACE [COMMON.MYSQLP] read_without_check_sys_variable (ob_sql_client_decorator.cpp:122) [10348][T1024_L0_G0][T1024][xxxxx-xxxxx-xxxxx-xxxxx] [lt=31] read without check sys variable succeeded!(ret=0, sql="SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=654242 AND col_stat.PARTITION_ID IN (654325) AND col_stat.COLUMN_ID IN (18)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;", tenant_id=1024, check_sys_variable=false, snapshot_timestamp=-1
   

   或者 trace id 查询 sql audit 能看到大量内部 SQL 查询统计信息的 SQL。

   SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=3113202 AND col_stat.PARTITION_ID IN (3113644) AND col_stat.COLUMN_ID IN (154)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;
   
   SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=3113202 AND col_stat.PARTITION_ID IN (3113581) AND col_stat.COLUMN_ID IN (101)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;
   
   SELECT col_stat.tenant_id as tenant_id, col_stat.table_id as table_id, col_stat.partition_id as partition_id, col_stat.column_id as column_id, col_stat.object_type as stat_level, col_stat.distinct_cnt as num_distinct, col_stat.null_cnt as num_null,col_stat.b_max_value as b_max_value, col_stat.b_min_value as b_min_value,col_stat.avg_len as avg_len,col_stat.distinct_cnt_synopsis as distinct_cnt_synopsis,col_stat.distinct_cnt_synopsis_size as distinct_cnt_synopsis_size,col_stat.histogram_type as histogram_type,col_stat.sample_size as sample_size,col_stat.bucket_cnt as bucket_cnt,col_stat.density as density,col_stat.last_analyzed as last_analyzed,hist_stat.endpoint_num as endpoint_num, hist_stat.b_endpoint_value as b_endpoint_value,hist_stat.endpoint_repeat_cnt as endpoint_repeat_cnt FROM __all_column_stat col_stat LEFT JOIN __all_histogram_stat hist_stat ON col_stat.tenant_id = hist_stat.tenant_id AND    col_stat.table_id = hist_stat.table_id AND    col_stat.partition_id = hist_stat.partition_id AND    col_stat.column_id = hist_stat.column_id WHERE  (col_stat.TENANT_ID=0 AND col_stat.TABLE_ID=3113202 AND col_stat.PARTITION_ID IN (3113689) AND col_stat.COLUMN_ID IN (19)) ORDER BY tenant_id, table_id, partition_id, column_id, endpoint_num;
   

   若在 OceanBase 数据库 V4.x 版本满足以上三点会命中此问题。

问题原因

SQL 在生成计划时读取直方图耗费的时间过长，最终导致计划生成时间过慢，但是由于拉取统计信息后缓存在 kvcache 中计划下次生成就会快了，这个集群下的 kvcache 刷的也比较快，平均 30 分钟刷一次 kvcache，原因是集群的下都是这种大的分区表 1024 分区，导致 kvcache 容易被淘汰。用户首次拿直方图一共执行了大约 2023 次内部 SQL 花了 5s，OceanBase 数据库 V4.2.1 BP10 做的优化拉取统计信息时，采用分段批量拉取的优化来解决此问题。

partitions(p1, p25, p[28-30], p61, p82, p102, p112, p137,
       p139, p[141-142], p174, p189, p200, p215, p226, p232, p240, p293, p317, p321, p325, p338, p348, p359, p370, p376, p390, p403, p421, p433, p466, p[471-472],
       p485, p514, p522, p526, p[538-539], p585, p588, p632, p636, p658, p664, p685, p691, p705, p709, p724, p737, p742, p757, p783, p[796-799], p808, p815, p829,
       p843, p889, p899, p[914-915], p927, p947, p959, p1015)

排查 SQL Audit 信息，发现 GET_PLAN_TIME 占了绝大部分的时长，slow query 日志耗时在 process_end 阶段耗时久。

问题的风险及影响

SQL 计划首次产生计划执行慢 GET_PLAN_TIME 占了绝大部分。

影响租户

影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。

适用版本

OceanBase 数据库 V4.x 版本。

解决方法及规避方式

升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库 V4.2.1 BP10（oceanbase-4.2.1.10-110000072024111216）。