查询SQL突然很慢，求解答一下，感谢

AntTech_UCVIYQ · 2024 年5 月 9 日 17:11

ob社区版，版本号：3.1.5，因为是生产数据库，不敢随意动，麻烦帮忙给看一下吧，十分感谢，爱心
SQL：
select f1,f2…f8
from table_a
where tenant_id = 1
and f9(时间字段) >= ‘2024-01-09 17:01:17’
and f9(时间字段) <= ‘2024-05-09 17:01:17’
and id > 1
order by id asc
limit 100
上述为业务的大致sql，昨日执行大约需要0.02s就出来结果了，
table_a这个表每日新增数据大约为100W+
今日上述SQL查询已经平均在5秒左右了.
分区条件:一级分区(tenant_id)，二级分区为用户id
走的索引为tenant_id+f9(时间字段)
目前tenant_id=1的数据量大约为500W左右

使用select * from gv$sql where sql_text like ‘%table_a%100%’;
select * from gv$sql_audit where sql_id= ‘’
查询的数据为
PLAN_ID 6795374
AFFECTED_ROWS 0
RETURN_ROWS 100
PARTITION_CNT 32
RET_CODE 0
QC_ID 0
DFO_ID 0
SQC_ID 0
WORKER_ID 0
EVENT default condition wait
P1TEXT address
P1 139892556401672
P2TEXT
P2 0
P3TEXT
P3 0
LEVEL 0
WAIT_CLASS_ID 104
WAIT_CLASS# 4
WAIT_CLASS CONCURRENCY
STATE WAITED SHORT TIME
WAIT_TIME_MICRO 346
TOTAL_WAIT_TIME_MICRO 416
TOTAL_WAITS 2
RPC_COUNT 5
PLAN_TYPE 3
IS_INNER_SQL 0
IS_EXECUTOR_RPC 0
IS_HIT_PLAN 1
REQUEST_TIME 1715238518942056
ELAPSED_TIME 4749533
NET_TIME 0
NET_WAIT_TIME 0
QUEUE_TIME 35
DECODE_TIME 1
GET_PLAN_TIME 758
EXECUTE_TIME 4748717
APPLICATION_WAIT_TIME 0
CONCURRENCY_WAIT_TIME 346
USER_IO_WAIT_TIME 0
SCHEDULE_TIME 0
ROW_CACHE_HIT 0
BLOOM_FILTER_CACHE_HIT 0
BLOCK_CACHE_HIT 0
BLOCK_INDEX_CACHE_HIT 0
DISK_READS 0
RETRY_CNT 0
TABLE_SCAN 0
CONSISTENCY_LEVEL 3
MEMSTORE_READ_ROW_COUNT 0
SSSTORE_READ_ROW_COUNT 0
REQUEST_MEMORY_USED 2096128
EXPECTED_WORKER_COUNT 0
USED_WORKER_COUNT 0
SCHED_INFO J10000T00000:1715238518946858,4743805,4743810;
FUSE_ROW_CACHE_HIT 0
PS_CLIENT_STMT_ID -1
PS_INNER_STMT_ID -1
TRANSACTION_HASH 0
REQUEST_TYPE 2
IS_BATCHED_MULTI_STMT 0
OB_TRACE_INFO client_ip=
PLAN_HASH 4954122119450791021
LOCK_FOR_READ_TIME 0
WAIT_TRX_MIGRATE_TIME 0

王利博 · 2024 年5 月 9 日 17:35

可以通过sql_audit 看下
OceanBase分布式数据库-海量数据笔笔算数

xuyu · 2024 年5 月 9 日 17:47

提供下explain extended 计划吧

AntTech_UCVIYQ · 2024 年5 月 9 日 18:27

===============================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |LIMIT | |1 |396198|
|1 | PX COORDINATOR MERGE SORT | |1 |396198|
|2 | EXCHANGE OUT DISTR |:EX10000 |1 |396198|
|3 | LIMIT | |1 |396198|
|4 | TOP-N SORT | |1 |396198|
|5 | PX PARTITION ITERATOR | |1 |396198|
|6 | TABLE SCAN |gic_emission_mobile(idx_tenant_id_enent_time)|1 |396198|

Outputs & filters:

0 - output([gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter(nil), limit(100), offset(nil)
1 - output([gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter(nil), sort_keys([gic_emission_mobile.id(0x7fb396c6e930), ASC])
2 - output([gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter(nil), dop=1
3 - output([gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter(nil), limit(100), offset(nil)
4 - output([gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter(nil), sort_keys([gic_emission_mobile.id(0x7fb396c6e930), ASC]), topn(100)
5 - output([gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter(nil),
force partition granule, asc.
6 - output([gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), filter([gic_emission_mobile.id(0x7fb396c6e930) > 4054565661(0x7fb396c6e280)], [gic_emission_mobile.source_name(0x7fb396c712e0) = ‘jieyi.20240509-bus.csv’(0x7fb396c70c30)]),
access([gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)], [gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.source_name(0x7fb396c712e0)], [gic_emission_mobile.app_id(0x7fb396c71b20)], [gic_emission_mobile.event_id(0x7fa7a559a6d0)], [gic_emission_mobile.emission(0x7fa7a559af10)], [gic_emission_mobile.region(0x7fa7a559b490)]), partitions(p15sp[0-31]),
is_index_back=true, filter_before_indexback[true,false],
range_key([gic_emission_mobile.tenant_id(0x7fb396c6d630)], [gic_emission_mobile.event_time(0x7fb396c6f7d0)], [gic_emission_mobile.id(0x7fb396c6e930)], [gic_emission_mobile.mobile_sha256(0x7fb396c7abc0)]), range(43,2024-05-07 16:00:00.000000,4054565661,MAX ; 43,2024-05-08 15:59:59.000000,MAX,MAX),
range_cond([gic_emission_mobile.tenant_id(0x7fb396c6d630) = 43(0x7fb396c7b3b0)], [gic_emission_mobile.event_time(0x7fb396c6f7d0) >= ?(0x7fb396c6f120)], [gic_emission_mobile.event_time(0x7fb396c6f7d0) <= ?(0x7fb396c70050)])

Used Hint:

/*+
*/

Outline Data:

/*+
BEGIN_OUTLINE_DATA
INDEX(@“SEL$1” “gic_trade.gic_emission_mobile”@“SEL$1” “idx_tenant_id_enent_time”)
END_OUTLINE_DATA
*/

Plan Type:

DISTRIBUTED

Optimization Info:

gic_emission_mobile:table_rows:3807011, physical_range_rows:1272352, logical_range_rows:1272352, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_tenant_id_enent_time,gic_emission_mobile], pruned_index_name[uk_tenant_id_mobile_event_id,idx_mobile_sha256,idx_tenant_id_region_remain_emission_event_time,idx_event_month], estimation info[table_id:1100611139454211, (table_type:1, version:0-1715191210404535-1715191210404535, logical_rc:0, physical_rc:0), (table_type:7, version:1715191201059139-1715191210404535-1715191228873141, logical_rc:0, physical_rc:0), (table_type:7, version:1715191228873141-1715245360403668-1715245360403668, logical_rc:39761, physical_rc:39761), (table_type:5, version:1715238351524650-1715245360403668-1715245360403668, logical_rc:0, physical_rc:0), (table_type:0, version:1715245360403668-1715245360403668-9223372036854775807, logical_rc:0, physical_rc:0)]
Parameters

{obj:{“TIMESTAMP”:“2024-05-07 16:00:00.000000”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“TIMESTAMP”, collation:“binary”, coercibility:“NUMERIC”}}, {obj:{“TIMESTAMP”:“2024-05-08 15:59:59.000000”}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:“TIMESTAMP”, collation:“binary”, coercibility:“NUMERIC”}}

AntTech_UCVIYQ · 2024 年5 月 9 日 18:34

这个是v$SQL_AUDIT查询的信息

REQUEST_ID	151209491
SQL_EXEC_ID	548582676
TRACE_ID	YB420A640165-00060978055AC0BE
SID	3222442560
CLIENT_PORT	30378
TENANT_ID	1001
TENANT_NAME	lph
EFFECTIVE_TENANT_ID	1001
USER_ID	1100611139404783
USER_NAME	gic_api
USER_GROUP	0
USER_CLIENT_IP	100.67.87.201
DB_ID	18446744073709551615
DB_NAME
SQL_ID	D8DAA4102CDC45E4050924FD9ED23FE7
PLAN_ID	6795374
AFFECTED_ROWS	0
RETURN_ROWS	100
PARTITION_CNT	32
RET_CODE	0
QC_ID	0
DFO_ID	0
SQC_ID	0
WORKER_ID	0
EVENT	default condition wait
P1TEXT	address
P1	139903004164104
P2TEXT
P2	0
P3TEXT
P3	0
LEVEL	0
WAIT_CLASS_ID	104
WAIT_CLASS#	4
WAIT_CLASS	CONCURRENCY
STATE	WAITED SHORT TIME
WAIT_TIME_MICRO	403
TOTAL_WAIT_TIME_MICRO	471
TOTAL_WAITS	2
RPC_COUNT	5
PLAN_TYPE	3
IS_INNER_SQL	0
IS_EXECUTOR_RPC	0
IS_HIT_PLAN	1
REQUEST_TIME	1715245921988152
ELAPSED_TIME	4660203
NET_TIME	0
NET_WAIT_TIME	0
QUEUE_TIME	29
DECODE_TIME	1
GET_PLAN_TIME	533
EXECUTE_TIME	4659626
APPLICATION_WAIT_TIME	0
CONCURRENCY_WAIT_TIME	403
USER_IO_WAIT_TIME	0
SCHEDULE_TIME	0
ROW_CACHE_HIT	0
BLOOM_FILTER_CACHE_HIT	0
BLOCK_CACHE_HIT	0
BLOCK_INDEX_CACHE_HIT	0
DISK_READS	0
RETRY_CNT	0
TABLE_SCAN	0
CONSISTENCY_LEVEL	3
MEMSTORE_READ_ROW_COUNT	0
SSSTORE_READ_ROW_COUNT	0
REQUEST_MEMORY_USED	2096128
EXPECTED_WORKER_COUNT	0
USED_WORKER_COUNT	0
SCHED_INFO	J10000T00000:1715245921991886,4655144,4655149;
FUSE_ROW_CACHE_HIT	0
PS_CLIENT_STMT_ID	-1
PS_INNER_STMT_ID	-1
TRANSACTION_HASH	0
REQUEST_TYPE	2
IS_BATCHED_MULTI_STMT	0
PLAN_HASH	4954122119450791021
LOCK_FOR_READ_TIME	0
WAIT_TRX_MIGRATE_TIME	0

xuyu · 2024 年5 月 9 日 19:52

这是explain extended sql的全部内容了吗，发个完整的文本附件上来吧

AntTech_UCVIYQ · 2024 年5 月 9 日 20:29

sql.txt (6.7 KB)

AntTech_UCVIYQ · 2024 年5 月 9 日 20:29

感谢感谢十分感谢比心

xuyu · 2024 年5 月 9 日 21:02

这些估行都是1很奇怪的，是不是没收集过统计信息，可以查下统计相关相关的stat表看下

AntTech_UCVIYQ · 2024 年5 月 10 日 10:19

能提供一个stat表查询SQL吗，感谢感谢，这个问题有个地方忘记说了，就是这个查询是一个循环一直查询，每次取100条，一直取完数据结束。拉取量大约在100W左右，在社区看到过https://open.oceanbase.com/blog/2239494400这个帖子，但是由于是生产环境，不敢随意动，特来寻找帮助，感谢感谢

xuyu · 2024 年5 月 10 日 11:35

OceanBase(admin@test)>show tables from oceanbase like "%stati%";
+-------------------------------+
| Tables_in_oceanbase (%stati%) |
+-------------------------------+
| DBA_IND_STATISTICS            |
| DBA_OB_AUX_STATISTICS         |
| DBA_PART_COL_STATISTICS       |
| DBA_SUBPART_COL_STATISTICS    |
| DBA_TAB_COL_STATISTICS        |
| DBA_TAB_STATISTICS            |
+-------------------------------+
6 rows in set (0.003 sec)

可以从上面查询确认相关的统计信息是否准确

xuyu · 2024 年5 月 10 日 11:38

还有一个比较明显的奇怪点，这里索引为什么第二个是时间，而不是这个等值条件
source_name

，这个的过滤性很差？

AntTech_UCVIYQ · 2024 年5 月 10 日 11:44

这个表之前有的索引是tenant_id+时间，source_name字段是没有索引的。但是前天这样查询是很快的，昨天就开始很慢很慢了
微信截图_20240510113658
stat表和您截图的还不太一样。这里只有3个，需要我每一个查询一条数据整合成文件发您吗