没事 你试一下 我看看能否生效 你创建和查询都是直连的在一个节点么？还是odp连接的？你们是1-1-1的 还是单节点的
按照这样的方式
SET ob_enable_show_trace=‘ON’;
注意：需要在同一个会话中执行
obclient [test]> select count() from test2; --你实际执行的语句
±---------+
| count() |
±---------+
| 0 |
±---------+
1 row in set (0.003 sec)
obclient [test]> select last_trace_id();
±----------------------------------+
| last_trace_id() |
±----------------------------------+
| YB420BA1CC68-000615A0A8EA6511-0-0 |
±----------------------------------+
1 row in set (0.002 sec)
obclient [test]> select * from oceanbase.gv$ob_sql_audit where trace_id=‘YB420BA1CC68-000615A0A8EA6511-0-0’; – 执行 SQL 语句，获取 trace_id ，此种方式只能返回 trace_id ，需要再通过gv$ob_sql_audit视图查到需要去哪个SVR_IP地址过滤日志。
[root@x.x.x.x ~]$ grep “YB420BA1CC68-000615A0A8EA6511-0-0” rootservice.log
[root@x.x.x.x ~]$ grep “YB420BA1CC68-000615A0A8EA6511-0-0” observer.log
1 个赞
1 个赞
按SQL绑好outline，执行了一下sql，拿到traceid，没找到日志
mysql> select last_trace_id();
±----------------------------------+
| last_trace_id() |
±----------------------------------+
| YB42C0A85121-000646E6AD3F3408-0-0 |
±----------------------------------+
[root@cdh81-33 log]# grep “YB42C0A85121-000646E6AD3F3408-0-0” rootservice.log
[root@cdh81-33 log]# grep “YB42C0A85121-000646E6AD3F3408-0-0” observer.log
[root@cdh81-33 log]#
不过确认了按SQL绑简单hint，是能生效outline的，看到新生成的outline id了，也在gv$ob_plan_cache_plan_stat里确认到新outline id了。那现在问题就是用sql_id+模糊hint绑的不行，详细sql+模糊hint可以（这种情况下在DBA_OB_OUTLINES里看到的模糊hint变成了一个详细hint）
你们的日志级别 是默认的么？wdiag么
SHOW PARAMETERS LIKE ‘%syslog_level%’;
嗯嗯，WDIAG
select * from oceanbase.DBA_OB_OUTLINES where outline_name =’’; 根据你创建的outlinename查一下 信息 看看
mysql> select * from oceanbase.DBA_OB_OUTLINES\G
*************************** 1. row ***************************
CREATE_TIME: 2026-04-16 15:55:53.679669
MODIFY_TIME: 2026-04-16 15:55:53.679669
TENANT_ID: 1008
DATABASE_ID: 500520
OUTLINE_ID: 550068
DATABASE_NAME: XXX
OUTLINE_NAME: force_index_dba_1
VISIBLE_SIGNATURE:
SQL_TEXT:
OUTLINE_TARGET:
OUTLINE_SQL:
SQL_ID: 11DEF3BCB4229A9A7289F801EF8588CB
OUTLINE_CONTENT: /+BEGIN_OUTLINE_DATA INDEX(@“SEL$1” “o”@“SEL$1” “UDX_USER_STORE_TRANS_NO”) OPTIMIZER_FEATURES_ENABLE(‘4.3.4.0’) END_OUTLINE_DATA/
*************************** 2. row ***************************
CREATE_TIME: 2026-04-17 10:42:55.584763
MODIFY_TIME: 2026-04-17 10:42:55.584763
TENANT_ID: 1008
DATABASE_ID: 500520
OUTLINE_ID: 550163
DATABASE_NAME: XXX
OUTLINE_NAME: force_index_dba_2
VISIBLE_SIGNATURE: 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 XXX.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 ?
SQL_TEXT: 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_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 XXX.T_EXTERNAL_ORDER o WHERE o.USER_NO = 5181240418003143698 AND o.ORDER_TYPE IN (1,2,6 ) AND o.CREATE_AT >= ‘2025-04-16 00:00:00’ AND o.CREATE_AT <= ‘2026-04-16 23:59:59’ AND o.SELLER_ID!=‘CD’ AND o.SELLER_ID!=‘SETTLE-CD’ AND (o.REMARKS IS NULL or length(o.REMARKS)=0) ORDER BY o.CREATE_AT DESC LIMIT 40
OUTLINE_TARGET:
OUTLINE_SQL: SELECT /+BEGIN_OUTLINE_DATA INDEX(@“SEL$1” “o”@“SEL$1” “UDX_USER_STORE_TRANS_NO”) OPTIMIZER_FEATURES_ENABLE(‘4.3.4.0’) END_OUTLINE_DATA/ 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 XXX.T_EXTERNAL_ORDER o WHERE o.USER_NO = 5181240418003143698 AND o.ORDER_TYPE IN (1,2,6 ) AND o.CREATE_AT >= ‘2025-04-16 00:00:00’ AND o.CREATE_AT <= ‘2026-04-16 23:59:59’ AND o.SELLER_ID!=‘CD’ AND o.SELLER_ID!=‘SETTLE-CD’ AND (o.REMARKS IS NULL or length(o.REMARKS)=0) ORDER BY o.CREATE_AT DESC LIMIT 40
SQL_ID:
OUTLINE_CONTENT: /+BEGIN_OUTLINE_DATA INDEX(@“SEL$1” “o”@“SEL$1” “UDX_USER_STORE_TRANS_NO”) OPTIMIZER_FEATURES_ENABLE(‘4.3.4.0’) END_OUTLINE_DATA/
现在还剩这2个，之前测试的简单hint+sql_id不生效的被我删了，信息在上面截图里有，打了个叉的
是因为sql_id 不一样导致的
你创建的sql_id是这个 11DEF3BCB4229A9A7289F801EF8588CB outline的sql_id看着不一样
以后可以这样查一下 看看DBA_OB_OUTLINES的sql_id和OB_PLAN_CACHE_PLAN_STAT是否一致
select
a.sql_id,
a.outline_name,
a.outline_id,
b.outline_data,
b.QUERY_SQL from
oceanbase.DBA_OB_OUTLINES a,
oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT b
where
a.sql_id = b.sql_id
and a.sql_id = ‘4A0528CE227AE2371D316F3C1AAB9A4E’
and outline_name = ‘otl_4A0528CE227AE2371D316F3C1AAB9A4E’
这个我清楚的，88CB这个sql_id是线上真实sql，包含了大量空格和换行，3693这个sql_id是我自己实验用的把空格换行都去掉的测试sql
sql_id是文本md5加密生成的 如果去掉空格什么的 会导致文本不一样 所以sql_id不一样 也是正常的 你可以看看sql_id怎么计算的
https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000217866?back=kb
这个我清楚，我的意思是我没搞错sqlid, 不是您之前说的sqlid搞错了才导致outline没生效，3693这个sqlid就是我DBA_OB_OUTLINES里的第二行的那个用详细sql的sql id，我个人测试用的，但是3693+简单hint的组合，outline没有生效
但是我看你上面执行的一直是这个sql_id呀 那你重新创建一遍 要不然都搞混了 不知道你具体用的哪个sql_id创建的了 如果是测试 就在测试环境测试
好的，这命令是我复制来的，我再重新来一遍
怪了，这次又可以了 
应该是你搞错了 来回切换环境 不注意导致的
1 个赞
之前还碰到过一次也是死活绑不上，那次的sql开发本来就自带了个hint，然后我想绑outline的同时不影响sql自带的hint，然后用了format outline：
CREATE FORMAT OUTLINE disable_parallel_dba_1 ON ‘CA87A7020C2392E604B07C1A25D87D43’ USING HINT /*+ PARALLEL(1) */;
这种语法有问题吗
你是带着hint生成的sql_id么？语法看着没有问题呀
好吧，下次再碰到绑不上的再发帖问问，感谢
赞一个！