Oracle 12c结合R2DBC查询耗时过长问题排查求助

DevOps 小满

2026-8-18

Spring WebFlux + Oracle 12c R2DBC 查询性能问题排查

我正在使用Spring WebFlux开发API，数据库采用Oracle 12c。当通过R2DBC执行查询时，即便表中仅1条记录，单次查询耗时仍达300-1200ms。请问我哪里操作有误？为何查询耗时如此久？

【跟踪截图】

1. Repository代码

使用协程响应式等待结果。

@Repository
class ClientRepository(
    private val r2dbcEntityTemplate: R2dbcEntityTemplate
) : BaseRepository<ClientEntity>(r2dbcEntityTemplate) {

    suspend fun getSEQ() = getSEQ("CLIENT_SEQ")

    suspend fun findByClientId(
        clientId: String
    ) = run {
        logger.debug("+findByClientId : {}", clientId)
        val now = LocalDate.now()
        var aa = r2dbcEntityTemplate.select(
            Query.query(
                Criteria.where(ClientEntity::clientId.name).`is`(clientId)
                    .and(Criteria.where(ClientEntity::isDeleted.name).`is`(Flag.N))
                    .and(Criteria.where(ClientEntity::startDate.name).lessThanOrEquals(now))
                    .and(
                        Criteria.where(ClientEntity::endDate.name).isNull
                            .or(ClientEntity::endDate.name).greaterThanOrEquals(now)
                    )
            ),
            ClientEntity::class.java
        )
        logger.debug("done query")
        var bb = aa.awaitFirstOrNull()
        logger.debug("done awaitFirstOrNull")
        logger.debug("-findByClientId : {}", clientId)
        bb
    }
}

2. R2DBC配置

使用io.r2dbc:r2dbc-pool:0.9.2.RELEASE作为连接池。

@Configuration
@EnableR2dbcRepositories
@EnableTransactionManagement
class R2DBCConfig(
    val myConfigProperty: MyConfigProperty
) : AbstractR2dbcConfiguration() {

    @Bean
    override fun connectionFactory(): ConnectionFactory {
        val descriptor = "(DESCRIPTION =\n" +
                "    (ADDRESS = (PROTOCOL = TCP)(HOST = ${myConfigProperty.database.host})(PORT = ${myConfigProperty.database.port}))\n" +
                "    (CONNECT_DATA =\n" +
                "      (SERVER = DEDICATED)\n" +
                "      (SERVICE_NAME = ${myConfigProperty.database.serviceName})\n" +
                "    )\n" +
                "  )"

        val connectionOption = ConnectionFactoryOptions.builder()
            .option(ConnectionFactoryOptions.DRIVER, "oracle")
            .option(OracleR2dbcOptions.DESCRIPTOR, descriptor)
            .option(ConnectionFactoryOptions.USER, myConfigProperty.database.username)
            .option(ConnectionFactoryOptions.PASSWORD, myConfigProperty.database.password)
            .build()

        val connectionPoolConfig = ConnectionPoolConfiguration
            .builder(ConnectionFactories.get(connectionOption))
            .initialSize(myConfigProperty.database.poolInitialSize)
            .maxSize(myConfigProperty.database.pooMaxSize)
            .maxIdleTime(Duration.ofSeconds(5))
            .validationQuery(myConfigProperty.database.validationQuery)
            .build()

        return ConnectionPool(connectionPoolConfig)
    }

    @Bean
    fun r2dbcEntityTemplate(
        connectionFactory: ConnectionFactory
    ) = R2dbcEntityTemplate(connectionFactory)

    @Bean
    fun transactionManager(connectionFactory: ConnectionFactory): TransactionalOperator {
        return TransactionalOperator.create(R2dbcTransactionManager(connectionFactory))
    }

}

3. build.gradle.kts

项目所有依赖配置

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
    application
    id("org.springframework.boot") version "2.7.3"
    id("io.spring.dependency-management") version "1.0.13.RELEASE"
    id("nebula.release") version "16.1.0"
    kotlin("jvm") version "1.6.21"
    kotlin("plugin.spring") version "1.6.21"
}

application {
    applicationDefaultJvmArgs = listOf("-Duser.timezone=UTC+7", "-Dfile.encoding=UTF-8")
}


group = "com.demo"
//version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11

repositories {
    maven(
        uri("https://repo1.maven.org/maven2/")
    )
    maven(
        uri("https://repo.spring.io/plugins-release/")
    )
    maven(
        uri("https://artifactory.wetransform.to/artifactory/libs-release/")
    )
    maven(
        uri("https://repo.spring.io/libs-milestone"),
    )
    mavenCentral()
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-actuator")
    implementation("org.springframework.boot:spring-boot-starter-webflux")
    implementation("org.springframework.boot:spring-boot-starter-security")
    annotationProcessor("org.springframework.boot:spring-boot-configuration-processor")
//    implementation("org.springframework.boot:spring-boot-starter-cache")

    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("com.fasterxml.jackson.datatype:jackson-datatype-jsr310:2.13.4")
    implementation("io.projectreactor.kotlin:reactor-kotlin-extensions")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-reactor")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-slf4j")

    implementation("org.springframework.boot:spring-boot-starter-data-r2dbc:2.7.3")
//    implementation("com.oracle.database.jdbc:ojdbc11:21.7.0.0")
    implementation("io.r2dbc:r2dbc-pool:0.9.2.RELEASE")
    implementation("com.oracle.database.r2dbc:oracle-r2dbc:0.4.0")

    implementation("org.bouncycastle:bcpkix-jdk15on:1.70")
    implementation("io.jsonwebtoken:jjwt-api:0.11.5")
    runtimeOnly("io.jsonwebtoken:jjwt-impl:0.11.5")
    runtimeOnly("io.jsonwebtoken:jjwt-jackson:0.11.5")


    implementation("org.ehcache:ehcache:3.10.1")

    implementation("javax.xml:jaxrpc-api:1.1")
    implementation("javax.xml.soap:javax.xml.soap-api:1.4.0")
    implementation("org.apache.axis:axis:1.4")
    implementation("commons-discovery:commons-discovery:0.5")
    implementation("javax.wsdl:com.springsource.javax.wsdl:1.6.1")
    implementation(files("libs/webseal.client-1.0.jar"))

    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("io.projectreactor:reactor-test")
    testImplementation("junit:junit:4.13.2")

    implementation("com.google.zxing:core:3.5.0")
    implementation("org.json:json:20220320")
    implementation("joda-time:joda-time:2.11.1")

}

dependencyManagement {
    imports {
        mavenBom("io.r2dbc:r2dbc-bom:Borca-SR2")
        mavenBom("org.springframework.cloud:spring-cloud-dependencies:2021.0.4")
    }
}

tasks.withType<KotlinCompile> {
    kotlinOptions {
        freeCompilerArgs = listOf("-Xjsr305=strict")
        jvmTarget = "11"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

4. application.yaml

数据库配置

spring:
  webflux:
    base-path: /my-context

server:
  port: 8080

my-config:
  api-profile : DEMO
  database:
    host: 192.168.46.131
    port: 1751
    username: usr_sit
    password: Qwertysd
    service-name: DCNET1DB
    pool-initial-size: 10
    poo-max-size: 20
    pool-max-idle-time: 1m
    validation-query: "SELECT 1 from dual"

可能的原因及优化方案

1. 数据库索引缺失

查询条件包含clientId、isDeleted、startDate、endDate，若表中无对应复合索引，Oracle会走全表扫描，即便只有1条记录也可能因统计信息或表结构问题导致慢查询。

创建复合索引：CREATE INDEX idx_client_id_status_date ON CLIENT (CLIENT_ID, IS_DELETED, START_DATE, END_DATE);
执行EXPLAIN PLAN查看查询执行计划，确认是否命中索引。

2. R2DBC连接池配置不一致

代码中maxIdleTime设为5秒，与yaml配置的pool-max-idle-time: 1m不匹配，导致连接频繁被回收重建，每次查询都需重新建立连接，增加耗时。修正代码：maxIdleTime(Duration.ofMinutes(1))。

开启R2DBC连接池DEBUG日志，观察连接获取、释放频率，确认池化是否正常工作。

3. Oracle R2DBC驱动版本兼容性

当前使用的oracle-r2dbc:0.4.0虽兼容Spring Boot 2.7.3对应的R2DBC Borca-SR2，但建议升级到最新稳定版（如0.4.1），修复已知性能问题。

4. 日期类型转换开销

LocalDate.now()生成的日期绑定到Oracle时可能存在类型转换开销，或Oracle对日期范围查询的处理未优化。

确保实体类日期字段与数据库类型完全匹配；
业务允许的话，直接使用Oracle的SYSDATE函数替代客户端生成的日期，减少绑定转换步骤。

5. 网络延迟问题

检查应用服务器到Oracle数据库的网络连通性，用ping、traceroute测试延迟和丢包情况，远程数据库的网络问题可能是主要耗时原因。

内容的提问来源于stack exchange，提问作者poknovem

Oracle 12c结合R2DBC查询耗时过长问题排查求助

1. Repository代码 #

2. R2DBC配置 #

3. build.gradle.kts #

4. application.yaml #

可能的原因及优化方案 #