慢sql问题排查

吉利蛋 · 2026 年4 月 16 日 10:30

【使用环境】生产环境
【 OB or 其他组件】OB
【使用版本】4.3.5.3
【问题描述】一个sql本来好好 mysql> SELECT plan_id, FROM_UNIXT ±--------±-------------------------± | plan_id | time_readable ±--------±-------------------------± | 251773 | 2026-04-16 10:23:12.3369 | | 251773 | 2026-04-16 09:52:54.3590 | | 251773 | 2026-04-16 09:51:34.4092 | | 251773 | 2026-04-16 09:25:34.4814 | | 251773 | 2026-04-16 09:25:33.7172 | | 251773 | 2026-04-16 09:25:27.2855 | | 251773 | 2026-04-16 09:25:20.7937 | | 251773 | 2026-04-16 09:25:13.4601 | | 251773 | 2026-04-16 09:25:04.3626 | | 250566 | 2026-04-16 09:24:28.4228 | | 250566 | 2026-04-16 09:23:22.7510 | | 250566 | 2026-04-16 09:22:17.5174 | | 250566 | 2026-04-16 09:21:11.7683 | | 250566 | 2026-04-16 09:20:33.5705 | | 250566 | 2026-04-16 09:20:04.7460 | | 250566 | 2026-04-16 09:19:28.2416 | | 250566 | 2026-04-16 09:18:22.5328 | | 250566 | 2026-04-16 09:17:17.3205 | | 250566 | 2026-04-16 09:16:11.5670 | | 250566 | 2026-04-16 09:15:42.9392 | | 250566 | 2026-04-16 09:15:04.6530 | | 250566 | 2026-04-16 09:14:29.1736 | | 250566 | 2026-04-16 09:13:22.6969 | | 250566 | 2026-04-16 09:12:17.4718 | | 250566 | 2026-04-16 09:11:11.6406 | | 250566 | 2026-04-16 09:10:04.6372 | | 250566 | 2026-04-16 09:08:36.4322 | | 250566 | 2026-04-16 09:07:17.3390 | | 250566 | 2026-04-16 09:06:11.2227 | | 250566 | 2026-04-16 09:05:04.3249 | | 250566 | 2026-04-16 09:01:28.9985 | | 250566 | 2026-04-16 09:00:09.4938 | | 250566 | 2026-04-16 08:43:05.7446 | | 250566 | 2026-04-16 06:45:52.2389 | | 250566 | 2026-04-16 05:54:25.6481 | | 250566 | 2026-04-16 05:54:10.3157 | | 250566 | 2026-04-16 05:47:04.0654 | ±--------±-------------------------± 37 rows in set (2.90 sec) 的，9点开始突然变慢，然后在09:25出现一个db file data read，此时plan_id改变，然后sql恢复
IME(request_time / 1000000) AS time_readable, elapsed_time / 1000000 AS elapsed_sec, execute_time / 1000000 AS execute_sec, queue_time / 1000000 aiit_sec, total_waits, event, wait_class, ret_code FROM gv$ob_sql_audit where sql_id = ‘11DEF3BCB4229A9A7289F801EF8588CB’ ORDER BY request_time DESC LIMIT 100;
------------±------------±----------±---------------------±------------±---------------±------------±---------------------------±-----------±---------+
| elapsed_sec | execute_sec | queue_sec | concurrency_wait_sec | io_wait_sec | total_wait_sec | total_waits | event | wait_class | ret_code |
------------±------------±----------±---------------------±------------±---------------±------------±---------------------------±-----------±---------+
0.0072 | 0.0070 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0060 | 0.0060 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0063 | 0.0062 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0015 | 0.0014 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0005 | 0.0004 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0029 | 0.0029 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
0.0014 | 0.0013 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0040 | 0.0039 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0404 | 0.0148 | 0.0000 | 0.0000 | 0.0000 | 0.0153 | 4 | db file data read | USER_IO | 0 |
331.6604 | 331.6571 | 0.0029 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
91.8451 | 91.8374 | 0.0074 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
588.2520 | 588.2441 | 0.0076 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
324.1719 | 324.1710 | 0.0006 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
824.3398 | 824.3331 | 0.0064 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
701.7351 | 701.7293 | 0.0056 | 0.0000 | 0.0000 | 0.0000 | 2 | mysql response wait client | IDLE | 0 |
332.9931 | 332.9889 | 0.0038 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
90.5519 | 90.5439 | 0.0076 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
584.9209 | 584.9202 | 0.0004 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
323.9192 | 323.9192 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
815.9480 | 815.9479 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
703.5977 | 703.5976 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 2 | mysql response wait client | IDLE | 0 |
321.6011 | 321.6010 | 0.0001 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
89.8041 | 89.8041 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
578.4517 | 578.4517 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
330.8125 | 330.8124 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
700.4624 | 700.4623 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 2 | mysql response wait client | IDLE | 0 |
93.3553 | 93.3552 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
585.4390 | 585.4388 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
327.6217 | 327.6216 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
716.2226 | 716.2225 | 0.0001 | 0.0000 | 0.0000 | 0.0000 | 2 | mysql response wait client | IDLE | 0 |
336.5416 | 336.5415 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1 | mysql response wait client | IDLE | 0 |
714.3588 | 714.3587 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 2 | mysql response wait client | IDLE | 0 |
1.7812 | 1.7811 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0021 | 0.0019 | 0.0001 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0014 | 0.0014 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0018 | 0.0017 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
0.0022 | 0.0021 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0 | | OTHER | 0 |
------------±------------±----------±---------------------±------------±---------------±------------±---------------------------±-----------±---------+

考虑到9点的时候有几个ddl被执行，会不会有所关联？ddl涉及到的表和慢sql查询的表不是同一张表。

mysql> SELECT query_sql, FROM_UNIXTIME(request_time / 1000000) AS time_readable, elapsed_time / 1000000 AS elapsed_sec, execute_time / 1000000 AS execute_sec, queue_time / 1000000 AS queue_sec, concurrency_wait_time / 1000000 AS concurrency_wait_sec, user_io_wait_time / 1000000 AS io_wait_sec, total_wait_time_micro / 1000000 AS total_wait_sec, total_waits, event, wait_class, ret_code FROM gv$ob_sql_audit where FROM_UNIXTIME(request_time / 1000000) > ‘2026-04-16 08:59:00’ and FROM_UNIXTIME(request_time / 1000000)< ‘2026-04-16 09:30:00’ and (query_sql like ‘alter%’ or query_sql like ‘truncate%’) order by request_time desc ;
±----------------------------------------------------------------------------------------------------------------±-------------------------±------------±------------±----------±---------------------±------------±---------------±------------±--------------------±-----------±---------+
| query_sql | time_readable | elapsed_sec | execute_sec | queue_sec | concurrency_wait_sec | io_wait_sec | total_wait_sec | total_waits | event | wait_class | ret_code |
±----------------------------------------------------------------------------------------------------------------±-------------------------±------------±------------±----------±---------------------±------------±---------------±------------±--------------------±-----------±---------+
| TRUNCATE TABLE xxdb.xxtable2 | 2026-04-16 09:00:07.6496 | 0.5589 | 0.5586 | 0.0000 | 0.0000 | 0.0000 | 0.4987 | 36 | sync rpc | NETWORK | 0 |
| TRUNCATE TABLE xxdb.xxtable1 | 2026-04-16 09:00:07.5128 | 0.4423 | 0.4422 | 0.0000 | 0.0000 | 0.0000 | 0.3693 | 29 | sync rpc | NETWORK | 0 |
| ALTER TABLE test.test_tbl1 ADD PARTITION (PARTITION P2026041709 VALUES LESS THAN (‘2026-04-17 10:00:00’)) | 2026-04-16 09:00:02.6376 | 0.7900 | 0.7892 | 0.0000 | 0.0000 | 0.0000 | 0.6514 | 1 | exec inner sql wait | OTHER | 0 |
| ALTER TABLE test.tbl2 DROP PARTITION P2026041602 | 2026-04-16 09:00:02.1633 | 0.4719 | 0.4539 | 0.0000 | 0.0000 | 0.0000 | 0.3779 | 395 | exec inner sql wait | OTHER | 0 |
| ALTER TABLE test.tbl2 ADD PARTITION (PARTITION P2026041612 VALUES LESS THAN (‘2026-04-16 13:00:00’)) | 2026-04-16 09:00:01.3846 | 0.7773 | 0.7766 | 0.0000 | 0.0000 | 0.0000 | 0.6411 | 1 | exec inner sql wait | OTHER | 0 |
| ALTER TABLE test.tbl1 DROP PARTITION P2026041508 | 2026-04-16 09:00:00.5301 | 0.8531 | 0.8356 | 0.0000 | 0.0000 | 0.0000 | 0.6926 | 413 | exec inner sql wait | OTHER | 0 |
| ALTER TABLE test.tbl1 ADD PARTITION (PARTITION P2026041612 VALUES LESS THAN (‘2026-04-16 13:00:00’)) | 2026-04-16 09:00:00.0203 | 0.5084 | 0.5079 | 0.0000 | 0.0000 | 0.0000 | 0.4192 | 1 | exec inner sql wait | OTHER | 0 |
±----------------------------------------------------------------------------------------------------------------±-------------------------±------------±------------±----------±---------------------±------------±---------------±------------±--------------------±-----------±---------+
7 rows in set (4.37 sec)

辞霜 · 2026 年4 月 16 日 10:48

SQL性能问题, 此处env中的trace_id对应gv$ob_sql_audit的trace_id

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
用obdiag收集一下信息吧。存在等待事件导致

吉利蛋 · 2026 年4 月 16 日 11:23

[ERROR] StepSQLHandler execute Exception: (11049, ‘Exceed query memory limit (mem_limit=644245050, mem_hold=755474432), please check whether the query_memory_limit_percentage configuration item is reasonable.’)
obdiag跑到一半报错了，是调大业务租户的query_memory_limit_percentage吗，SHOW PARAMETERS LIKE 'query_memory_limit_percentage’现在已经50%了，调到70？

吉利蛋 · 2026 年4 月 16 日 11:35

调到70以后又报错在db连接阶段了，我倒是没传–env database，user是填的root@sys，哪里要改么，sql_result.txt里面看已经有了不少集群信息了，27M大小

[ERROR] db connection information required: --env host=… --env port=… --env user=… --env password=… --env database=…
Gather scene results stored in this directory: /home/chansey/obdiag_gather_pack_20260416112705

吉利蛋 · 2026 年4 月 16 日 14:39

obdiag gather scene run --scene=observer.perf_sql --env host=xx.xx.xx.33 --env port=2881 --env user=root@bf1 --env password=xxx --env database=XXX_CBCA --env trace_id=YB42C0A85121-000646E8294FDBB2-0-0

跑是这么跑的，看sys连上了采了一堆配置文件下来，但是这个指定database不知道有什么意义，一直是报错
[ERROR] Database ‘XXX_CBCA’ does not exist. Please check --env database=.
[ERROR] init db connector, error: Database ‘XXX_CBCA’ does not exist. Please check --env database=., please check --env option
[ERROR] init option failed
[ERROR] init failed
配了一台ssh，日志也没找到，是不是时间问题，我看他默认只扫描半小时的，这已经是早上9点的事情了

辞霜 · 2026 年4 月 16 日 15:15

日志还存在么是不是已经被刷掉了。数据库是不是填错了，Database ‘XXX_CBCA’ does not exist

吉利蛋 · 2026 年4 月 16 日 15:23

开了ssh的那台机器, 今天早上9点的observer.log确认是在的，db名也肯定没填错…

辞霜 · 2026 年4 月 16 日 15:46

show databases看下

辞霜 · 2026 年4 月 16 日 16:08

你把这两个执行计划都提取一份发出来看下吧

吉利蛋 · 2026 年4 月 16 日 16:13

| Query Plan |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------ |
| |0 |SORT | |353 |11243 | |
| |1 |└─TABLE RANGE SCAN|o(UDX_USER_STORE_TRANS_NO)|1040 |9902 | |
| ======================================================================== |

Outputs & filters:
0 - output([o.ID], [o.ORDER_ID], [o.FILE_BATCH_NO], [o.USER_NO], [o.ORI_USER_TRANS_ID], [o.ORDER_CCY], [o.ORDER_AMT], [o.TRADE_AT], [o.STATE], [o.REMARKS],
[o.CREATE_AT], [o.WITHDRAW_ID], [o.AVAILABLE_ORDER_AMT], [o.SETTLE_AMT], [o.SETTLE_CCY], [o.STORE_NO], [o.PAYEE_NAME], [o.PAYEE_ID_NO], [o.PAYEE_ACC_NO],
[o.ORDER_TYPE], [o.STORE_URL], [o.BUY_NAME], [o.AUDIT_STATUS], [o.AUDIT_REMARKS], [o.AUDIT_AT], [o.UPDATE_AT], [o.INDUSTRY_TYPE], [o.WORDS_FLAG], [o.WORDS]), filter(nil), rowset=256
sort_keys([o.CREATE_AT, DESC])
1 - output([o.ID], [o.USER_NO], [o.ORDER_TYPE], [o.CREATE_AT], [o.REMARKS], [o.ORDER_ID], [o.FILE_BATCH_NO], [o.ORI_USER_TRANS_ID], [o.ORDER_CCY], [o.ORDER_AMT],
[o.TRADE_AT], [o.STATE], [o.WITHDRAW_ID], [o.AVAILABLE_ORDER_AMT], [o.SETTLE_AMT], [o.SETTLE_CCY], [o.STORE_NO], [o.PAYEE_NAME], [o.PAYEE_ID_NO], [o.PAYEE_ACC_NO],
[o.STORE_URL], [o.BUY_NAME], [o.AUDIT_STATUS], [o.AUDIT_REMARKS], [o.AUDIT_AT], [o.UPDATE_AT], [o.INDUSTRY_TYPE], [o.WORDS_FLAG], [o.WORDS]), filter([o.CREATE_AT
>= cast(‘2025-04-16 00:00:00’, TIMESTAMP(-1, -1))], [o.ORDER_TYPE IN (1, 2, 6)], [o.REMARKS IS NULL OR length(o.REMARKS) = 0], [o.SELLER_ID != ‘CD’], [o.SELLER_ID
!= ‘SETTLE-CD’], [o.CREATE_AT <= cast(‘2026-04-16 23:59:59’, TIMESTAMP(-1, -1))]), rowset=256
access([o.ID], [o.USER_NO], [o.ORDER_TYPE], [o.CREATE_AT], [o.SELLER_ID], [o.REMARKS], [o.ORDER_ID], [o.FILE_BATCH_NO], [o.ORI_USER_TRANS_ID], [o.ORDER_CCY],
[o.ORDER_AMT], [o.TRADE_AT], [o.STATE], [o.WITHDRAW_ID], [o.AVAILABLE_ORDER_AMT], [o.SETTLE_AMT], [o.SETTLE_CCY], [o.STORE_NO], [o.PAYEE_NAME], [o.PAYEE_ID_NO],
[o.PAYEE_ACC_NO], [o.STORE_URL], [o.BUY_NAME], [o.AUDIT_STATUS], [o.AUDIT_REMARKS], [o.AUDIT_AT], [o.UPDATE_AT], [o.INDUSTRY_TYPE], [o.WORDS_FLAG], [o.WORDS]), partitions(p0)
is_index_back=true, is_global_index=false, filter_before_indexback[false,false,false,false,false,false],
range_key([o.USER_NO], [o.STORE_NO], [o.USER_TRANS_ID], [o.shadow_pk_0]), range(5181240418003143698,MIN,MIN,MIN ; 5181240418003143698,MAX,MAX,MAX),

range_cond([o.USER_NO = 5181240418003143698])

±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

现在不慢了，我只能找到这个更优的当下的执行计划，没地方看之前慢的时候是什么情况。然后我试着绑outline也发现个问题是
CREATE OUTLINE force_index_dba_1 ON ‘11DEF3BCB4229A9A7289F801EF8588CB’ USING hint /+BEGIN_OUTLINE_DATA INDEX(@“SEL$1” “o”@“SEL$1” “UDX_USER_STORE_TRANS_NO”) OPTIMIZER_FEATURES_ENABLE(‘4.3.4.0’) END_OUTLINE_DATA/;
完全照搬gv$ob_plan_cache_plan_stat里的outline绑上去，ob会接受，再看gv$ob_plan_cache_plan_stat发现用了我的outline_id，但是我用一个简易的hint /*+INDEX(o UDX_USER_STORE_TRANS_NO) */ ，ob就不接受，outline_id还是一直-1

淇铭 · 2026 年4 月 16 日 17:43

意思是绑定outline没有生效是么？按照这样的信息查一下看看
SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE SQL_ID=‘11DEF3BCB4229A9A7289F801EF8588CB’\G

吉利蛋 · 2026 年4 月 16 日 19:09

嗯嗯，就是这里查到的outline id是-1，认定没生效，但是绑定的outline用ob_plan_cache_plan_stat里自动生成的完整的就能生效，有个具体的outlineid

AntTech_MSPSKN · 2026 年4 月 17 日 08:36

打卡打卡

Amber_www · 2026 年4 月 17 日 09:21

关于0000的讨论很有价值，特别是在04场景下，合理使用2026是关键。

淇铭 · 2026 年4 月 17 日 09:28

加上hint的计划能取一下么？

hint /*+INDEX(o UDX_USER_STORE_TRANS_NO) */

explain extended select /*+INDEX(o UDX_USER_STORE_TRANS_NO) */ from table 这样保存到文本里发一下看看

吉利蛋 · 2026 年4 月 17 日 09:57

mysql> desc extended SELECT /*+INDEX(o UDX_USER_STORE_TRANS_NO) */ o.ID AS id, o.ORDER_ID AS orderId, o.FILE_BATCH_NO AS fileBatchNo, o.USER_NNO AS userNo, o.ORI_USER_TRANS_ID AS userTransId, o.ORDER_CCY AS orderCcy, o.ORDER_AMT AS orderAmt, o.TRADE_AT AS tradeAt, o.STATE ASS state, o.REMARKS AS remarks, o.CREATE_AT AS createAt, o.WITHDRAW_ID AS withdrawId, o.AVAILABLE_ORDER_AMT AS availableOrderAmt, o.SEETTL PAAYEE_ o.AUDIT_REMARKS AS auditRemarks, o.AUDIT_AT AS auditAt, o.UPDATE_AT AS updateAt, o.INDUSTRY_TYPE AS industryType, o.WORDS_FLAG AS wordsFlaag, o.WORDS AS words FROM BAOFU_CBCA.T_EXTERNAL_ORDER o WHERE o.USER_NO = 5181240523003470098 AND o.ORDER_TYPE IN (1,2,6 ) AND o.CREATE_AT >= '2025-04-16 00: NDD (o.REMARKS IS NULL
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------ |
| |0 |TOP-N SORT | |40 |10021181 | |
| |1 |└─TABLE RANGE SCAN|o(UDX_USER_STORE_TRANS_NO)|1312616 |9958433 | |
| ======================================================================== |

Outputs & filters:
0 - output([o.ID(0x7f4e5aa3a7d0)], [o.ORDER_ID(0x7f4e5aa3ab20)], [o.FILE_BATCH_NO(0x7f4e5aa3ae70)], [o.USER_NO(0x7f4e5aa36060)], [o.ORI_USER_TRANS_ID(0x7f4e5aa3b380)],
[o.ORDER_CCY(0x7f4e5aa3b6d0)], [o.ORDER_AMT(0x7f4e5aa3ba20)], [o.TRADE_AT(0x7f4e5aa3bd70)], [o.STATE(0x7f4e5aa3c0c0)], [o.REMARKS(0x7f4e5aa39e50)], [o.CREATE_AT(0x7f4e5aa373e0)],
[o.WITHDRAW_ID(0x7f4e5aa3c790)], [o.AVAILABLE_ORDER_AMT(0x7f4e5aa3cae0)], [o.SETTLE_AMT(0x7f4e5aa3ce30)], [o.SETTLE_CCY(0x7f4e5aa3d180)], [o.STORE_NO(0x7f4e5aa3f3e0)],
[o.PAYEE_NAME(0x7f4e5aa3f730)], [o.PAYEE_ID_NO(0x7f4e5aa5fb20)], [o.PAYEE_ACC_NO(0x7f4e5aa5fe70)], [o.ORDER_TYPE(0x7f4e5aa36bf0)], [o.STORE_URL(0x7f4e5aa60380)],
[o.BUY_NAME(0x7f4e5aa606d0)], [o.AUDIT_STATUS(0x7f4e5aa60a20)], [o.AUDIT_REMARKS(0x7f4e5aa60d70)], [o.AUDIT_AT(0x7f4e5aa610c0)], [o.UPDATE_AT(0x7f4e5aa61410)],
[o.INDUSTRY_TYPE(0x7f4e5aa61760)], [o.WORDS_FLAG(0x7f4e5aa61ab0)], [o.WORDS(0x7f4e5aa61e00)]), filter(nil), rowset=256
sort_keys([o.CREATE_AT(0x7f4e5aa373e0), DESC]), topn(40(0x7f4e5aa62160))
1 - output([o.ID(0x7f4e5aa3a7d0)], [o.USER_NO(0x7f4e5aa36060)], [o.ORDER_TYPE(0x7f4e5aa36bf0)], [o.CREATE_AT(0x7f4e5aa373e0)], [o.REMARKS(0x7f4e5aa39e50)],
[o.ORDER_ID(0x7f4e5aa3ab20)], [o.FILE_BATCH_NO(0x7f4e5aa3ae70)], [o.ORI_USER_TRANS_ID(0x7f4e5aa3b380)], [o.ORDER_CCY(0x7f4e5aa3b6d0)], [o.ORDER_AMT(0x7f4e5aa3ba20)],
[o.TRADE_AT(0x7f4e5aa3bd70)], [o.STATE(0x7f4e5aa3c0c0)], [o.WITHDRAW_ID(0x7f4e5aa3c790)], [o.AVAILABLE_ORDER_AMT(0x7f4e5aa3cae0)], [o.SETTLE_AMT(0x7f4e5aa3ce30)],
[o.SETTLE_CCY(0x7f4e5aa3d180)], [o.STORE_NO(0x7f4e5aa3f3e0)], [o.PAYEE_NAME(0x7f4e5aa3f730)], [o.PAYEE_ID_NO(0x7f4e5aa5fb20)], [o.PAYEE_ACC_NO(0x7f4e5aa5fe70)],
[o.STORE_URL(0x7f4e5aa60380)], [o.BUY_NAME(0x7f4e5aa606d0)], [o.AUDIT_STATUS(0x7f4e5aa60a20)], [o.AUDIT_REMARKS(0x7f4e5aa60d70)], [o.AUDIT_AT(0x7f4e5aa610c0)],
[o.UPDATE_AT(0x7f4e5aa61410)], [o.INDUSTRY_TYPE(0x7f4e5aa61760)], [o.WORDS_FLAG(0x7f4e5aa61ab0)], [o.WORDS(0x7f4e5aa61e00)]), filter([o.CREATE_AT(0x7f4e5aa373e0)
>= cast(‘2025-04-16 00:00:00’(0x7f4e5aa37120), TIMESTAMP(-1, -1))(0x7f4e5aa376c0)(0x7f4e5aa37270)], [o.ORDER_TYPE(0x7f4e5aa36bf0) IN (1(0x7f4e5aa36790),
2(0x7f4e5aa368e0), 6(0x7f4e5aa36a30))(0x7f4e5aab4d30)(0x7f4e5aa36360)], [o.REMARKS(0x7f4e5aa39e50) IS NULL(0x7f4e5aa39600) OR length(o.REMARKS(0x7f4e5aa39e50))(0x7f4e5aa39750)
= 0(0x7f4e5aa39b40)(0x7f4e5aa39c90)(0x7f4e5aa391b0)], [o.SELLER_ID(0x7f4e5aa388d0) != ‘CD’(0x7f4e5aa38610)(0x7f4e5aa38760)], [o.SELLER_ID(0x7f4e5aa388d0)
!= ‘SETTLE-CD’(0x7f4e5aa38d80)(0x7f4e5aa38ed0)], [o.CREATE_AT(0x7f4e5aa373e0) <= cast(‘2026-04-16 23:59:59’(0x7f4e5aa37c60), TIMESTAMP(-1, -1))(0x7f4e5aa38070)(0x7f4e5aa37db0)],
[TOPN_FILTER(o.CREATE_AT(0x7f4e5aa373e0))(0x7f4e5ab8bec0)]), rowset=256
access([o.ID(0x7f4e5aa3a7d0)], [o.USER_NO(0x7f4e5aa36060)], [o.ORDER_TYPE(0x7f4e5aa36bf0)], [o.CREATE_AT(0x7f4e5aa373e0)], [o.SELLER_ID(0x7f4e5aa388d0)],
[o.REMARKS(0x7f4e5aa39e50)], [o.ORDER_ID(0x7f4e5aa3ab20)], [o.FILE_BATCH_NO(0x7f4e5aa3ae70)], [o.ORI_USER_TRANS_ID(0x7f4e5aa3b380)], [o.ORDER_CCY(0x7f4e5aa3b6d0)],
[o.ORDER_AMT(0x7f4e5aa3ba20)], [o.TRADE_AT(0x7f4e5aa3bd70)], [o.STATE(0x7f4e5aa3c0c0)], [o.WITHDRAW_ID(0x7f4e5aa3c790)], [o.AVAILABLE_ORDER_AMT(0x7f4e5aa3cae0)],
[o.SETTLE_AMT(0x7f4e5aa3ce30)], [o.SETTLE_CCY(0x7f4e5aa3d180)], [o.STORE_NO(0x7f4e5aa3f3e0)], [o.PAYEE_NAME(0x7f4e5aa3f730)], [o.PAYEE_ID_NO(0x7f4e5aa5fb20)],
[o.PAYEE_ACC_NO(0x7f4e5aa5fe70)], [o.STORE_URL(0x7f4e5aa60380)], [o.BUY_NAME(0x7f4e5aa606d0)], [o.AUDIT_STATUS(0x7f4e5aa60a20)], [o.AUDIT_REMARKS(0x7f4e5aa60d70)],
[o.AUDIT_AT(0x7f4e5aa610c0)], [o.UPDATE_AT(0x7f4e5aa61410)], [o.INDUSTRY_TYPE(0x7f4e5aa61760)], [o.WORDS_FLAG(0x7f4e5aa61ab0)], [o.WORDS(0x7f4e5aa61e00)]), partitions(p0)
is_index_back=true, is_global_index=false, filter_before_indexback[false,false,false,false,false,false,false],
range_key([o.USER_NO(0x7f4e5aa36060)], [o.STORE_NO(0x7f4e5aa3f3e0)], [o.USER_TRANS_ID(0x7f4e5ab54e60)], [o.shadow_pk_0(0x7f4e5ab54ff0)]), range(5181240523003470098,
MIN,MIN,MIN ; 5181240523003470098,MAX,MAX,MAX),
range_cond([o.USER_NO(0x7f4e5aa36060) = 5181240523003470098(0x7f4e5ab9bc20)(0x7f4e5ab9b9e0)])
Used Hint:
-------------------------------------
/*+

INDEX(“o” “udx_user_store_trans_no”)
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@“SEL$1” “o”@“SEL$1” “UDX_USER_STORE_TRANS_NO”)
OPTIMIZER_FEATURES_ENABLE(‘4.3.4.0’)
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
o:
table_rows:198031233
physical_range_rows:1859428
logical_range_rows:1859428
index_back_rows:1859428
output_rows:1312615
table_dop:1
dop_method:Table DOP
avaiable_index_name:[UDX_USER_STORE_TRANS_NO, IDX_EX_ORDER_CREATE_AT, IDX_FILE_USER_ORDER, IDX_ORDER_ID, IDX_ORI_TRANS_ID, IDX_TRADE_AT, IDX_USER_TRANS_ID, IDX_WITHDRAW_ID, idx_teo_audit_status_order_type, IDX_USER_TRADE_AT, T_EXTERNAL_ORDER]
pruned_index_name:[IDX_EX_ORDER_CREATE_AT, IDX_FILE_USER_ORDER, IDX_ORDER_ID, IDX_ORI_TRANS_ID, IDX_TRADE_AT, IDX_USER_TRANS_ID, IDX_WITHDRAW_ID, idx_teo_audit_status_order_type, IDX_USER_TRADE_AT, T_EXTERNAL_ORDER]
stats info:[version=2026-04-15 22:27:00.541356, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
Plan Type:
LOCAL
Parameters:
:0 => 5181240523003470098
:1 => 1
:2 => 2
:3 => 6
:4 => ‘2025-04-16 00:00:00’
:5 => ‘2026-04-16 23:59:59’
:6 => ‘CD’
:7 => ‘SETTLE-CD’
:8 => 0
:9 => 40
Note:
Degree of Parallelisim is 1 because of table property

±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
84 rows in set (0.01 sec)

其实问题就是现在默认的outline里用的索引，和我手动绑定outline的时候指定的索引是一样的，但是这种情况下我绑定的没生效（outline_id没有具体的值），所以就很疑惑，万一之后线上执行计划走错了要强制绑索引担心也出现绑了不生效的情况

淇铭 · 2026 年4 月 17 日 10:08

SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE SQL_ID=‘11DEF3BCB4229A9A7289F801EF8588CB’\G;这个信息你查一下我看看可以吧

把具体创建的outline 发一下我看看是不是写的有问题

吉利蛋 · 2026 年4 月 17 日 10:14

mysql> SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE SQL_ID=‘11DEF3BCB4229A9A7289F801EF8588CB’\G
*************************** 1. row ***************************
SQL_ID: 11DEF3BCB4229A9A7289F801EF8588CB
PLAN_ID: 252661
STATEMENT: SELECT
o.ID AS id,
o.ORDER_ID AS orderId,
o.FILE_BATCH_NO AS fileBatchNo,
o.USER_NO AS userNo,
o.ORI_USER_TRANS_ID AS userTransId,
o.ORDER_CCY AS orderCcy,
o.ORDER_AMT AS orderAmt,
o.TRADE_AT AS tradeAt,
o.STATE AS state,
o.REMARKS AS remarks,
o.CREATE_AT AS createAt,
o.WITHDRAW_ID AS withdrawId,
o.AVAILABLE_ORDER_AMT AS availableOrderAmt,
o.SETTLE_AMT AS settleAmt,
o.SETTLE_CCY AS settleCcy,
o.STORE_NO AS storeNo,
o.PAYEE_NAME AS payeeName,
o.PAYEE_ID_NO AS payeeIdNo,
o.PAYEE_ACC_NO AS payeeAccNo,
o.ORDER_TYPE AS orderType,
o.STORE_URL AS storeUrl,
o.BUY_NAME AS buyerName,
o.AUDIT_STATUS AS auditStatus,
o.AUDIT_REMARKS AS auditRemarks,
o.AUDIT_AT AS auditAt,
o.UPDATE_AT AS updateAt,
o.INDUSTRY_TYPE AS industryType,
o.WORDS_FLAG AS wordsFlag,
o.WORDS AS words
FROM
BAOFU_CBCA.T_EXTERNAL_ORDER o
WHERE o.USER_NO = ?

        AND o.ORDER_TYPE IN (
          
            ?
         , 
            ?
         , 
            ?
         
        )
     
     
         
                AND o.CREATE_AT >= ?
             
     
     
         
                AND o.CREATE_AT <= ?
             
     
     
    AND o.SELLER_ID!=?
    AND o.SELLER_ID!=?
    AND (o.REMARKS IS NULL or length(o.REMARKS)=?)
     
        ORDER BY o.CREATE_AT DESC LIMIT ?

OUTLINE_ID: 550068
OUTLINE_DATA: /+BEGIN_OUTLINE_DATA INDEX(@“SEL$1” “o”@“SEL$1” “UDX_USER_STORE_TRANS_NO”) OPTIMIZER_FEATURES_ENABLE(‘4.3.4.0’) END_OUTLINE_DATA/

现在这个是OK的，我outline抄的自动生成的格式，带begin和end的，就能生效，我本来自己绑的是：
CREATE OUTLINE disable_parallel_dba_1 ON ‘11DEF3BCB4229A9A7289F801EF8588CB’ USING hint /*+ INDEX(o UDX_USER_STORE_TRANS_NO) */; 这个就不生效

淇铭 · 2026 年4 月 17 日 10:27

CREATE OUTLINE otl_idx_c2
ON SELECT/+ index(t1 idx_c2)/ * FROM t1 WHERE c2 = 1; 按照这样能生效么？不用sql_id的方式

吉利蛋 · 2026 年4 月 17 日 10:30

这种感觉不好用原始sql很长还有大量的空格和换行，只想用sql id保险一点