Timeout超时问题

程序去实现跟直接执行SQL语句压根不一样的呀

怎么不一样的,程序是java调用jdbc去执行的一条SQL查询报表,应该没有什么额外花销吧,而且这个是数据库底层的报错超时

感谢解答OceanBase问题！对于query场景，我的经验是重点关注timeout配置。

可以看看你们jdbc的有些参数怎么设置的 可以发一下链接串
java端是否设置了更短的时间
statement.setQueryTimeout(0);
socketTimeout 是否设置的合理

支持一下

无效回复，刷积分，会给禁言
看看社区治理公告

jdbc的参数如下,然后使用了默认的druid连接池,连接池用默认参数,会不会跟我用ob的驱动有关系
url: “jdbc:oceanbase://xxx?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&rewriteBatchedStatements=true”
driver-class-name: “com.oceanbase.jdbc.Driver”

ob驱动是哪个版本的

<oceanbase.version>2.4.16</oceanbase.version>

        <dependency>
            <groupId>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>${oceanbase.version}</version>
        </dependency>

能看一下 你的语句在java端怎么添加的hint么？ 从报错的信息 来看确实数据库服务端超时了
数据库查一下这个语句request_time的时间
SELECT request_time,
elapsed_time / 1000000 AS elapsed_sec,
ret_code,
sql_text,
query_sql
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE sql_text LIKE ‘%你的表名%’
AND ret_code = -4012
ORDER BY request_time DESC
LIMIT 5;

数据库查一下这个语句request_time的时间
SELECT request_time,
elapsed_time / 1000000 AS elapsed_sec,
ret_code,
sql_text,
query_sql
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE sql_text LIKE ‘%你的表名%’
AND ret_code = -4012
ORDER BY request_time DESC
LIMIT 5; 这个信息 也查一下

你这个语句 hint是作用子查询上了吧 你把这个hint挪到最外层 或者 在连接层统一调大 session 超时
spring:
datasource:
druid:
connection-init-sqls: SET ob_query_timeout = 300000000

根据我发的语句 查一下完整过的语句 看看

你把整个query_sql 发一下 看看是否是你执行的语句 从上面的请求时间来看 刚好卡在119秒多

看着不是 JDBC/Druid 的问题，也不是 hint 数值写错，而是外包 SQL 后，内层 QUERY_TIMEOUT 不生效，实际仍被 session 的 120 秒杀掉。
在数据源连接上 SET ob_query_timeout = 300000000 应该就可以了。数据库端的尽量也调大

原版SQL如下,我后续去研究一下session 超时的参数再看看

with em as (
select
           id
				,simple_name
				,`code`
				,org_id
				,province
				,user_id
				,店长, 
				店长联系方式, 
				大店长, 
				大店长联系方式, 
				dud_id,
				督导, 
				督导联系方式 ,
				运营负责人
				,`value` 
				,openingDays
 from (
select em.id
				,em.simple_name
				,em.`code`
				,em.org_id
				,em.province
				,sbu.id as user_id
				,sbu.name as 店长, 
				sbu.phone as 店长联系方式, 
				sbu1.name as 大店长, 
				sbu1.phone as 大店长联系方式, 
		CAST(sbu2.id as signed) as dud_id,
				sbu2.name as 督导, 
				sbu2.phone as 督导联系方式 ,
				if(principal_json!='',JSON_UNQUOTE(JSON_EXTRACT(principal_json,JSON_UNQUOTE(REPLACE(JSON_SEARCH(principal_json,'ONE','运营负责人'),'regionName','userId')))),null) 运营负责人
				,sbsa.`value` 
				,DATEDIFF(curdate(),tz_value)+1 openingDays
				from sys_base_shop  em 
				left join sys_base_org sbo on em.org_id=sbo.id and sbo.dept_org_type = 1
				left join sys_base_user sbu on sbo.director_id = sbu.id
				left join sys_base_org sbo1 on sbo.parent_id = sbo1.id and sbo1.dept_org_type = 2
				left join sys_base_user sbu1 on sbo1.director_id = sbu1.id
				left join sys_base_org sbo2 on sbo1.parent_id = sbo2.id and sbo2.dept_org_type = 3
				left join sys_base_user sbu2 on sbo2.director_id = sbu2.id
				left join (	select 
						market_id
					,date(max(if(target_id=1000024 ,value,null))) as tz_value  -- 开业时间
					,max(if(target_id=20 ,value,null)) as value -- 店铺类型

					from sys_base_shop_config_relation 
					where  del_flag=0
					and category='ATTR'
					group by market_id
									) sbsa on em.id = sbsa.market_id 
									) em 
						 
<where>
    <if test="marketIds != null and marketIds.size() > 0">
        AND em.id IN 
        <foreach collection="marketIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </if>
      <if test="favoriteMarketIds != null and favoriteMarketIds.size() > 0">
        AND em.id IN 
        <foreach collection="favoriteMarketIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </if>
		<if test="dataScopeDeptId != null and dataScopeDeptId.size() > 0">
        AND em.org_id IN 
        <foreach collection="dataScopeDeptId" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </if>
		<if test="operationSpecialistIds != null and operationSpecialistIds.size() > 0">
									AND 运营负责人 IN 
									<foreach collection="operationSpecialistIds" item="id" open="(" separator="," close=")">
											#{id}
									</foreach>
							</if>
							<if test="supervisorIds != null and supervisorIds.size() > 0">
									AND dud_id IN 
									<foreach collection="supervisorIds" item="id" open="(" separator="," close=")">
											#{id}
									</foreach>
							</if>
</where>

)
,bq as (
select em.id as mark_id
,sum(sale_amount) as sale_amount
,sum(effective_sale_count) as yx_cnt
,sum(effective_sale_num) as yx_num
,sum(effective_sale_amount) as yx_amt
,round(sum(effective_sale_num)/sum(effective_sale_count),2) as ldl
,round(sum(effective_sale_amount)/sum(effective_sale_count),2)  as kdj
,round(sum(effective_sale_amount)/sum(effective_sale_num),2)  as jdj
from stat_dws_market_sale sa 
inner join em on sa.market_id=em.id

where calculate_date between date( #{bq_date}) and date( #{bq_end_date})
group by em.id
)

,db as (
select em.id as mark_id
,sum(sale_amount) as sale_amount
,sum(effective_sale_count) as yx_cnt
,sum(effective_sale_num) as yx_num
,sum(effective_sale_amount) as yx_amt
,round(sum(effective_sale_num)/sum(effective_sale_count),2) as ldl
,round(sum(effective_sale_amount)/sum(effective_sale_count),2)  as kdj
,round(sum(effective_sale_amount)/sum(effective_sale_num),2)  as jdj
from stat_dws_market_sale sa 
inner join em on sa.market_id=em.id

where calculate_date between date( #{db_date}) and date( #{db_end_date})
group by em.id
)
,bq_mb as (
select em.id as mark_id
,sum(mb.ordinary) as jc_mb
,sum(mb.challenge) as tz_mb
from oa_market_sales_day_object_target mb
inner join em on mb.market_id=em.id
where mb.`date` between date( #{bq_date}) and date( #{bq_end_date})
and mb.del_flag=0
group by 
em.id
)
,db_mb as (
select em.id as mark_id
,sum(mb.ordinary) as db_jc_mb
,sum(mb.challenge) as db_tz_mb
from oa_market_sales_day_object_target mb
inner join em on mb.market_id=em.id
where mb.`date` between date( #{db_date}) and date( #{db_end_date})
and mb.del_flag=0
group by 
em.id
)
select  /*+ QUERY_TIMEOUT(300000000) */ 
null as dataScopeDeptId,
null as markName
,null as markCode
,null as storeManager
,null as markProvince
,sum(ifnull(sale_amount,0)) as bqSaleAmount
,sum(ifnull(yx_cnt,0)) as bqEffectiveSaleCount
,sum(ifnull(yx_num,0)) as bqEffectiveSaleNum
,ifnull(round(sum(yx_num)/sum(yx_cnt),2),0) as bqAttachmentRate
,ifnull(round(sum(yx_amt)/sum(yx_cnt),2),0)  as bqAverageOrderValue
,ifnull(round(sum(yx_amt)/sum(yx_num),2),0)  as bqUnitPrice
,ifnull(concat(cast(round(if(sum(jc_mb)=0,0,sum(sale_amount) / sum(jc_mb))*100,2) as char),'%'),'0.00%') as bqBasicCompletionRate
,ifnull(concat(cast(round(if(sum(tz_mb)=0,0,sum(if(tz_mb !=0,sale_amount,0)) / sum(tz_mb))*100,2) as char),'%'),'0.00%') as bqChallengeCompletionRate
,sum(ifnull(db_sale_amount,0)) as dbSaleAmount
,sum(ifnull(db_yx_cnt,0)) as dbEffectiveSaleCount
,sum(ifnull(db_yx_num,0)) as dbEffectiveSaleNum
,ifnull(round(sum(db_yx_num)/sum(db_yx_cnt),2),0) as dbAttachmentRate
,ifnull(round(sum(db_yx_amt)/sum(db_yx_cnt),2),0)  as dbAverageOrderValue
,ifnull(round(sum(db_yx_amt)/sum(db_yx_num),2),0)  as dbUnitPrice
,ifnull(concat(cast(round(if(sum(db_jc_mb)=0,0,sum(db_sale_amount) / sum(db_jc_mb))*100,2) as char),'%'),'0.00%') as dbBasicCompletionRate
,ifnull(concat(cast(round(if(sum(db_tz_mb)=0,0,sum(if(db_tz_mb !=0,db_sale_amount,0)) / sum(db_tz_mb))*100,2) as char),'%'),'0.00%') as dbChallengeCompletionRate
,ifnull(concat(cast(round((sum(sale_amount)-sum(db_sale_amount))/sum(db_sale_amount)*100,2) as char),'%'),'0.00%')  as performanceRatioRatio
,ifnull(concat(cast(round((sum(yx_cnt)-sum(db_yx_cnt))/sum(db_yx_cnt)*100,2) as char),'%'),'0.00%')  as singleComparisonValue
,ifnull(concat(cast(round((sum(yx_num)-sum(db_yx_num))/sum(db_yx_num)*100,2) as char),'%'),'0.00%')  as numberComparisonValue
-- ,ifnull(concat(cast(round((sum(ldl)-sum(db_ldl))/sum(db_ldl)*100,2) as char),'%'),'0.00%')  as RelativeRatioRatio
-- ,ifnull(concat(cast(round((sum(kdj)-sum(db_kdj))/sum(db_kdj)*100,2) as char),'%'),'0.00%')   as customerPriceComparisonValue
-- ,ifnull(concat(cast(round((sum(jdj)-sum(db_jdj))/sum(db_jdj)*100,2) as char),'%'),'0.00%')   as unitPriceComparisonValue
,ifnull(concat(cast(round((round(sum(yx_num)/sum(yx_cnt),2)-round(sum(db_yx_num)/sum(db_yx_cnt),2))/round(sum(db_yx_num)/sum(db_yx_cnt),2)*100,2) as char),'%'),'0.00%')      as RelativeRatioRatio
,ifnull(concat(cast(round((round(sum(yx_amt)/sum(yx_cnt),2)-round(sum(db_yx_amt)/sum(db_yx_cnt),2))/round(sum(db_yx_amt)/sum(db_yx_cnt),2)*100,2) as char),'%'),'0.00%')      as customerPriceComparisonValue
,ifnull(concat(cast(round((round(sum(yx_amt)/sum(yx_num),2)-round(sum(db_yx_amt)/sum(db_yx_num),2))/round(sum(db_yx_amt)/sum(db_yx_num),2)*100,2) as char),'%'),'0.00%')      as unitPriceComparisonValue
,ifnull(concat(cast(round((round(sum(sale_amount) / sum(jc_mb)*100,2)-round(sum(db_sale_amount) / sum(db_jc_mb)*100,2))/round(sum(db_sale_amount) / sum(db_jc_mb)*100,2)*100,2) as char),'%'),'0.00%')  as basicCompletionRateComparisonValue
,ifnull(concat(cast(round((round(sum(if(db_tz_mb !=0,sale_amount,0)) / sum(tz_mb)*100,2)-round(sum(if(db_tz_mb !=0,db_sale_amount,0)) / sum(db_tz_mb)*100,2))/round(sum(if(db_tz_mb !=0,db_sale_amount,0)) / sum(db_tz_mb)*100,2)*100,2) as char),'%'),'0.00%')  as challengeCompletionRateComparisonValue
,sum(ifnull(sale_amount,0))-sum(ifnull(db_sale_amount,0)) as differenceSaleAmount
,sum(ifnull(yx_cnt,0))-sum(ifnull(db_yx_cnt,0)) as differenceEffectiveSaleCount
,sum(ifnull(yx_num,0))-sum(ifnull(db_yx_num,0)) as differenceEffectiveSaleNum
-- ,sum(ifnull(ldl,0))-sum(ifnull(db_ldl,0)) as differenceAttachmentRate
-- ,sum(ifnull(kdj,0))-sum(ifnull(db_kdj,0))  as differenceAverageOrderValue
-- ,sum(ifnull(jdj,0))-sum(ifnull(db_jdj,0))  as differenceUnitPrice

,ifnull(round(sum(yx_num)/sum(yx_cnt),2)-round(sum(db_yx_num)/sum(db_yx_cnt),2),0)as differenceAttachmentRate
,ifnull(round(sum(yx_amt)/sum(yx_cnt),2)-round(sum(db_yx_amt)/sum(db_yx_cnt),2),0) as differenceAverageOrderValue
,ifnull(round(sum(yx_amt)/sum(yx_num),2)-round(sum(db_yx_amt)/sum(db_yx_num),2),0) as differenceUnitPrice

,round(sum(ifnull(sale_amount,0)) / sum(ifnull(jc_mb,0))*100,2)-round(sum(ifnull(db_sale_amount,0)) / sum(ifnull(db_jc_mb,0))*100,2) as differenceBasicCompletionRate
,round(sum(if(db_tz_mb !=0,ifnull(sale_amount,0),0)) / sum(ifnull(tz_mb,0))*100,2)-round(sum(if(db_tz_mb !=0,ifnull(db_sale_amount,0),0)) / sum(ifnull(db_tz_mb,0))*100,2) as differenceChallengeCompletionRate
from (
select 
mark_id as mark_id
,sum(sale_amount) as sale_amount
,sum(yx_cnt) as yx_cnt
,sum(yx_num) as yx_num
,sum(yx_amt) as yx_amt
,sum(ldl) as ldl
,sum(kdj)  as kdj
,sum(jdj)  as jdj
,sum(db_sale_amount) as db_sale_amount
,sum(db_yx_cnt) as db_yx_cnt
,sum(db_yx_num) as db_yx_num
,sum(db_yx_amt) as db_yx_amt
,sum(db_ldl) as db_ldl
,sum(db_kdj) as db_kdj
,sum(db_jdj) as db_jdj
,sum(jc_mb) as jc_mb
,sum(tz_mb) as tz_mb
,sum(db_jc_mb) as db_jc_mb
,sum(db_tz_mb) as db_tz_mb

from (
select mark_id as mark_id
,sum(sale_amount) as sale_amount
,sum(yx_cnt) as yx_cnt
,sum(yx_num) as yx_num
,sum(yx_amt) as yx_amt
,sum(ldl) as ldl
,sum(kdj)  as kdj
,sum(jdj)  as jdj
,0 as db_sale_amount
,0 as db_yx_cnt
,0 as db_yx_num
,0 as db_yx_amt
,0 as db_ldl
,0 as db_kdj
,0 as db_jdj
,0 as jc_mb
,0 as tz_mb
,0 as db_jc_mb
,0 as db_tz_mb
from bq
group by mark_id
union all 
select 
mark_id as mark_id
,0 as sale_amount
,0 as yx_cnt
,0 as yx_num
,0 as yx_amt
,0 as ldl
,0 as kdj
,0 as jdj
,sum(sale_amount) as db_sale_amount
,sum(yx_cnt) as db_yx_cnt
,sum(yx_num) as db_yx_num
,sum(yx_amt) as db_yx_amt
,sum(ldl) as db_ldl
,sum(kdj) as db_kdj
,sum(jdj) as db_jdj
,0 as jc_mb
,0 as tz_mb
,0 as db_jc_mb
,0 as db_tz_mb
from db
group by mark_id
union all 
select 
mark_id as mark_id
,0 as sale_amount
,0 as yx_cnt
,0 as yx_num
,0 as yx_amt
,0 as ldl
,0 as kdj
,0 as jdj
,0 as db_sale_amount
,0 as db_yx_cnt
,0 as db_yx_num
,0 as db_yx_amt
,0 as db_ldl
,0 as db_kdj
,0 as db_jdj
,sum(jc_mb) as jc_mb
,sum(tz_mb) as tz_mb
,0 as db_jc_mb
,0 as db_tz_mb
from bq_mb
group by mark_id
union all 
select 
mark_id as mark_id
,0 as sale_amount
,0 as yx_cnt
,0 as yx_num
,0 as yx_amt
,0 as ldl
,0 as kdj
,0 as jdj
,0 as db_sale_amount
,0 as db_yx_cnt
,0 as db_yx_num
,0 as db_yx_amt
,0 as db_ldl
,0 as db_kdj
,0 as db_jdj
,0 as jc_mb
,0 as tz_mb
,sum(db_jc_mb) as db_jc_mb
,sum(db_tz_mb) as db_tz_mb
from db_mb
group by mark_id
) t group by mark_id
) e

inner join em on e.mark_id=em.id

不能复现的问题不是问题。

在程序百分百复现超时的,现在只能加hint参数扩大超时,但是实际放navicat只需要查询10秒

大佬,我好像知道不一样的了,我本地用navicat是通过2883端口去访问的obproxy的,集群有3个节点,然后这个服务是在局域网里面通过2881直连其中的一台,然后查看ocp上面这个超时的节点的日志有很多warn的,也就是我通过navicat连2883的查询十秒就能返回数据,应用服务连2881走局域网的查询直接触发了2分钟超时

[2026-08-07 17:53:58.023268] WARN [RPC_OBRPC] decode (ob_rpc_net_handler.cpp:214) [34231][RpcIO][T0][Y0-0000000000000000-0-0] [lt=56][errcode=0] The RPC packet delay is large. [suggestion] check clockdiff and tcp retransmission rate first, it maybe cost by clock skew or network delay. Further more, it may be caused by hardware failure or software failure of the machine
[2026-08-07 17:54:09.856616] WARN [RPC_OBRPC] decode (ob_rpc_net_handler.cpp:214) [34226][RpcIO][T0][Y0-0000000000000000-0-0] [lt=58][errcode=0] The RPC packet delay is large. [suggestion] check clockdiff and tcp retransmission rate first, it maybe cost by clock skew or network delay. Further more, it may be caused by hardware failure or software failure of the machine
[2026-08-07 17:56:10.574815] WARN [RPC_OBRPC] decode (ob_rpc_net_handler.cpp:214) [34229][RpcIO][T0][Y0-0000000000000000-0-0] [lt=56][errcode=0] The RPC packet delay is large. [suggestion] check clockdiff and tcp retransmission rate first, it maybe cost by clock skew or network delay. Further more, it may be caused by hardware failure or software failure of the machine
[2026-08-07 17:58:03.213542] WARN [RPC_OBRPC] decode (ob_rpc_net_handler.cpp:214) [34227][RpcIO][T0][Y0-0000000000000000-0-0] [lt=57][errcode=0] The RPC packet delay is large. [suggestion] check clockdiff and tcp retransmission rate first, it maybe cost by clock skew or network delay. Further more, it may be caused by hardware failure or software failure of the machine
[2026-08-07 17:58:37.823457] WARN [RPC_OBRPC] decode (ob_rpc_net_handler.cpp:214) [34228][RpcIO][T0][Y0-0000000000000000-0-0] [lt=55][errcode=0] The RPC packet delay is large. [suggestion] check clockdiff and tcp retransmission rate first, it maybe cost by clock skew or network delay. Further more, it may be caused by hardware failure or software failure of the machine

判断是“烂计划”还是“排队”：

1. EXPLAIN EXTENDED select count(*) from (原SQL) x 的输出

2. gv$sql_audit 里那条 SQL 的 execute_time / queue_time / elapsed_time / plan_id

3. 应用 JDBC URL（脱敏 IP 密码）和连接池类型（Druid/Hikari）+ 走 2881 还是 2883