无风三尺浪 2024-06-27 15:22 采纳率: 0%
浏览 6
已结题

oracle查询Socket read timed out错误

Oracle查询超时问题

Oracle在查询超过6秒的sql都会报Socket read timed out。我也根据网上的一些资料，在oracle url后拼接了oracle.net.CONNECT_TIMEOUT和oracle.jdbc.ReadTimeout。把超时时间放大到9分钟。但是还是无济于事。该超时还是超时，超时的sql我在navicat中执行是需要十几秒的。。

下面是我的具体配置和错误信息

1、oracel配置

spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      druid:
        idleConnectionTestPeriod: 60
        max-idle: 50
        initial-size: 5
        min-idle: 50
        maxActive: 100
        maxWait: 900000
        timeBetweenEvictionRunsMillis: 900000
        minEvictableIdleTimeMillis: 900000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 50
        filters: stat,slf4j
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
      datasource:
          # 主库数据源
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
          username: user
          password: 'password'
        oracledb:
          driver-class-name: oracle.jdbc.OracleDriver
          url: jdbc:oracle:thin:@101.10.10.10:1541/UAT?oracle.jdbc.ReadTimeout=900000&oracle.net.CONNECT_TIMEOUT=900000&oracle.jdbc.connection_timeout=900000
          username: user
          password: 'password'

2、异常信息

2024-06-27 15:07:04.681  INFO 4064 --- [  Tenant_Pool-1] oracle.jdbc                              : traceId=5366575D, anoEnabled=true. 
2024-06-27 15:07:04.681  INFO 4064 --- [  Tenant_Pool-1] oracle.jdbc                              : Got Resend, SessionTraceId = 5366575D
2024-06-27 15:07:04.681  INFO 4064 --- [  Tenant_Pool-1] oracle.jdbc                              : Connection established. Cleared conn strategy stack
2024-06-27 15:07:04.681  INFO 4064 --- [  Tenant_Pool-1] oracle.jdbc                              : Sending break marker, SessionTraceId = 5366575D
2024-06-27 15:07:04.681  INFO 4064 --- [  Tenant_Pool-1] oracle.jdbc                              : returning void
2024-06-27 15:07:04.682  INFO 4064 --- [  Tenant_Pool-1] oracle.jdbc                              : null

java.sql.SQLRecoverableException: ORA-18730: IO 中断错误。: Socket read timed out
https://docs.oracle.com/error-help/db/ora-18730/
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1120)
    at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1286)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1211)
    at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1579)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1429)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3930)
    at oracle.jdbc.driver.OraclePreparedStatement.doExecute(OraclePreparedStatement.java:4162)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4148)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1011)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
    at sun.reflect.GeneratedMethodAccessor33.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy143.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
    at com.sun.proxy.$Proxy141.query(Unknown Source)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
    at com.sun.proxy.$Proxy140.query(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    at com.sun.proxy.$Proxy138.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
    at com.sun.proxy.$Proxy151.select(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
    at com.chsong.job.aop.DataSourceHandler.switchOracleDB(DataSourceHandler.java:42)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
    at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy152.select(Unknown Source)
    at com.chsong.job.service.impl.MesDsServiceImpl.lambda$erpToMes$0(MesDsServiceImpl.java:55)
    at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: oracle.net.nt.TimeoutInterruptHandler$IOReadTimeoutException: Socket read timed out
    at oracle.net.nt.TimeoutSocketChannel.newTimeoutException(TimeoutSocketChannel.java:1006)
    at oracle.net.nt.TimeoutSocketChannel.doBlockedRead(TimeoutSocketChannel.java:607)
    at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:536)
    at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:1224)
    at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:272)
    at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:205)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:148)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:121)
    at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:111)
    at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:930)
    at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:464)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:946)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:409)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:483)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:261)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1106)
    ... 75 common frames omitted
Caused by: java.net.SocketTimeoutException: null
    at sun.nio.ch.SocketAdaptor$SocketInputStream.read(SocketAdaptor.java:211)
    at sun.nio.ch.ChannelInputStream.read(ChannelInputStream.java:103)
    at oracle.net.nt.TimeoutSocketChannel.doBlockedRead(TimeoutSocketChannel.java:600)
    ... 89 common frames omitted
  • 写回答

25条回答 默认 最新

  • 专家-赤兔[在线] 全栈领域优质创作者 2024-06-27 15:43
    关注
    获得0.30元问题酬金

    引自免费微信小程序：皆我百晓生

    从你提供的信息来看，你在尝试解决Oracle数据库查询超时的问题，已经通过在连接字符串中设置了oracle.jdbc.ReadTimeout, oracle.net.CONNECT_TIMEOUT, 和 oracle.jdbc.connection_timeout 参数来增加超时时间，但问题依旧存在。这里有几个可能的原因和解决步骤可以尝试：

    1. 检查数据库服务器设置

    确保数据库服务器端也允许较长的会话超时时间。在Oracle数据库中，可以通过修改SQLNET.INBOUND_CONNECT_TIMEOUT和SQLNET.SEND_TIMEOUT参数来调整。这些参数通常位于数据库服务器的$ORACLE_HOME/network/admin/sqlnet.ora文件中。例如：

    SQLNET.INBOUND_CONNECT_TIMEOUT = 300
SQLNET.SEND_TIMEOUT = 900

    这里的单位是秒，上面的设置意味着连接建立超时为5分钟，数据包发送超时为15分钟。

    2. 数据源配置检查

    尽管你已经在URL后面添加了超时参数，但Druid数据源本身也有超时设置。确保Druid的数据源配置中没有其他导致提前超时的设置。比如maxWait参数应该与你期望的超时时间相匹配，你已设置为900000毫秒（15分钟），这看起来是正确的。

    3. 防火墙和网络问题

    确认没有网络设备（如防火墙、路由器）在6秒后中断连接。有时网络设备的安全策略可能会限制长时间的不活动连接。

    4. JDBC驱动版本

    确认你使用的Oracle JDBC驱动版本是否支持你所设置的超时参数。某些较旧的驱动可能不完全支持所有超时配置。推荐使用最新稳定版的驱动。

    5. 应用程序服务器设置

    检查应用程序服务器（如Tomcat、WebLogic等）是否有自己的连接池或者网络超时设置，这些也可能影响到数据库查询操作。

    6. 监控和诊断

    使用数据库的监控工具（如Oracle Enterprise Manager）来监控查询执行情况，查看是否有其他因素（如锁争用、资源不足）导致查询变慢。

    7. 优化SQL查询

    如果查询确实需要十几秒才能完成，考虑优化SQL语句或者数据库表的索引结构。

    示例代码调整（如果适用）

    如果你的配置是在Spring Boot项目中，确保Druid配置正确反映超时设置。你的配置看起来已经包含了大部分必要的设置，但为了确保，这里是一个简化版的配置示例，注意这里只展示与超时相关的部分：

    spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      connect-timeout: 30000 # 连接超时时间，单位毫秒
      validation-query: SELECT 1 FROM DUAL # 验证查询
      test-on-borrow: true # 借用连接时验证
      max-wait: 900000 # 获取连接等待的最大时间
      # 注意：Druid的readTimeout等参数需在连接池属性中单独配置，如果支持的话
      # 可能需要查阅Druid的最新文档，因为这些参数可能不在标准配置项中

    结论

    由于你已经尝试了基本的超时配置调整，问题可能出在上述提到的某个非直接配置的地方。逐一排查这些潜在原因，应该能够定位并解决问题。如果问题仍然存在，建议深入分析网络传输日志和数据库服务器日志，以获取更详细的故障信息。

    评论 编辑记录

报告相同问题？

问题事件

  • 系统已结题 7月5日
  • 赞助了问题酬金15元 6月27日
  • 创建了问题 6月27日

悬赏问题

  • ¥15 readimage函数怎么读取变量图片地址
  • ¥50 网课里面实习定位打卡
  • ¥50 Delphi 非客户区窗口阴影？
  • ¥15 cv2 morphologyEx函数报错
  • ¥15 有没有知道鸿蒙OS高级开发者新题答案的
  • ¥15 有没有人能帮我一下android
  • ¥20 做一个干部信息管理系统 软件
  • ¥15 通过4G模块EC600N向阿里云物联网平台物模型上面发送字符串，现在发送int数据是成功的，发送字符串就是不成功
  • ¥15 IDA反编译，代码识别失败
  • ¥70 matlab代码修改