--- title: 多分区读取直方图的导致 SQL 偶发执行时间过长-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于多分区读取直方图的导致 SQL 偶发执行时间过长相关的常见问题和使用技巧，帮助您快速解决多分区读取直方图的导致 SQL 偶发执行时间过长的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 多分区读取直方图的导致 SQL 偶发执行时间过长 更新时间：2026-05-21 09:16 适用版本： V4.0.x、V4.1.x、V4.2.x 内容类型：Troubleshoot ## 问题现象 生产业务系统，发现某一条查询 SQL 执行时间会有一定概率超过 3s，因为业务侧发现如果 SQL 执行超过 3s 即会强制断连避免慢 SQL，但是手工执行该 SQL 发现正常情况下非常快毫秒级 RT，但是也会有一定概率出现执行很慢的情况（2-3s），查看执行慢的情况下对应的 sql audit 信息，发现 `GET_PLAN_TIME` 占了绝大部分的时长需要分析。 ## 关键信息 1. 查看执行慢的情况下对应的 sql audit 信息，发现 `GET_PLAN_TIME` 占了绝大部分的时长。 ```shell 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.*`。 ```shell [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。 ```shell 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 做的优化拉取统计信息时，采用分段批量拉取的优化来解决此问题。 ```shell 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 阶段耗时久。 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20250227multi-partition-reading-histograms.png) ## 问题的风险及影响 SQL 计划首次产生计划执行慢 `GET_PLAN_TIME` 占了绝大部分。 ## 影响租户 影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。 ## 适用版本 OceanBase 数据库 V4.x 版本。 ## 解决方法及规避方式 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库 V4.2.1 BP10（oceanbase-4.2.1.10-110000072024111216）。 上一篇 [OBServer 告警：partition not exist in observer](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207678) 下一篇 [删分区导致执行计划改变 CPU 用满](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002396056) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线