Oracle 19c下SpringBoot JdbcTemplate带参数查询性能异常缓慢求助

在SpringBoot中使用JdbcTemplate连接Oracle 19c时遇到显著性能差异：

• 直接在数据库控制台执行静态SQL，耗时约300ms：

SELECT
    CLIENT_EXTRA_INFO.CLIENT_NUMBER,
    CLIENT_EXTRA_INFO.FULL_NAME
FROM
     CONTRACT
        JOIN CLIENT_EXTRA_INFO on (CONTRACT.CLIENTID = CLIENT_EXTRA_INFO.ID)
WHERE
    CLIENT_EXTRA_INFO.MBPHONE = '0343423223'
  and CONTRACT.STATUS = 'ACTIVE'
  and CONTRACT.FLAG IN ('2', '5') 
FETCH FIRST 10 ROWS ONLY;

• 但通过JDBC使用参数化查询时，响应时间长达约7分钟，相关Java代码如下：

@Override
public ResponsePagingDTO<RetailCustomerDTO> getDuplicateRetailCustomerWithPhoneNumber(DuplicatePhoneNumberRequest request) {
    MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
    mapSqlParameterSource.addValue("phone", request.getPhoneNumber());
    mapSqlParameterSource.addValue("row", request.getSize());
    String sql ="SELECT" +
        "    CLIENT_EXTRA_INFO.CLIENT_NUMBER," +
        "    CLIENT_EXTRA_INFO.FULL_NAME" +
        "FROM" +
        "     CONTRACT" +
        "        JOIN CLIENT_EXTRA_INFO on (CONTRACT.CLIENTID = CLIENT_EXTRA_INFO.ID)" +
        "WHERE" +
        "    CLIENT_EXTRA_INFO.MBPHONE = :phone" +
        "  and CONTRACT.STATUS = 'ACTIVE'" +
        "  and CONTRACT.FLAG IN ('2', '5') FETCH FIRST :row ROWS ONLY";

    ResponsePagingDTO<RetailCustomerDTO> responsePagingDTO = new ResponsePagingDTO<>();

    List<RetailCustomerDTO> retailCustomerDTOS = new ArrayList<>();
    pulseOpsTemplateJdbc.query(sql, mapSqlParameterSource, (result -> {
        RetailCustomerDTO retailCustomer = new RetailCustomerDTO();
        retailCustomer.setClientNumber(result.getString(ClientConstant.CLIENT_NUM));
        retailCustomer.setFullName(result.getString(ClientConstant.FULL_NAME));
        retailCustomer.setPhoneNumber(request.getPhoneNumber());
        retailCustomerDTOS.add(retailCustomer);
    }));
    responsePagingDTO.setData(retailCustomerDTOS);
    return responsePagingDTO;
}

数据库总数据量约8000万条，WHERE子句涉及的列均已建立索引，尝试多种方案未改善性能，寻求解决方案。

1. 强制参数类型匹配 #

Oracle绑定变量若与列类型不匹配，会导致索引失效。确认MBPHONE列类型（如VARCHAR2），显式指定参数类型：

mapSqlParameterSource.addValue("phone", request.getPhoneNumber(), Types.VARCHAR);

避免因手机号是数字字符串却以数字类型传递，引发隐式转换导致索引无法使用。

2. 添加索引提示强制走预期索引 #

若Oracle优化器选择了错误执行计划，在SQL中添加索引提示：

SELECT /*+ INDEX(CLIENT_EXTRA_INFO IDX_MBPHONE) */
    CLIENT_EXTRA_INFO.CLIENT_NUMBER,
    CLIENT_EXTRA_INFO.FULL_NAME
FROM
     CONTRACT
        JOIN CLIENT_EXTRA_INFO on (CONTRACT.CLIENTID = CLIENT_EXTRA_INFO.ID)
WHERE
    CLIENT_EXTRA_INFO.MBPHONE = :phone
  and CONTRACT.STATUS = 'ACTIVE'
  and CONTRACT.FLAG IN ('2', '5') 
FETCH FIRST :row ROWS ONLY;

将IDX_MBPHONE替换为MBPHONE列实际的索引名称。

3. 处理绑定变量窥探问题 #

Oracle的绑定变量窥探可能导致通用执行计划不适合当前参数，可尝试：

• 临时禁用会话级绑定变量窥探（需数据库权限）：
  在执行查询前执行ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE;
• 添加动态采样提示，让优化器针对当前参数生成计划：

  SELECT /*+ DYNAMIC_SAMPLING(CLIENT_EXTRA_INFO 4) */
  -- 后续SQL内容不变
  

4. 替换FETCH FIRST为ROWNUM限制 #

部分Oracle版本对参数化的FETCH FIRST支持不佳，改用嵌套查询+ROWNUM：

SELECT * FROM (
    SELECT
        CLIENT_EXTRA_INFO.CLIENT_NUMBER,
        CLIENT_EXTRA_INFO.FULL_NAME
    FROM
         CONTRACT
            JOIN CLIENT_EXTRA_INFO on (CONTRACT.CLIENTID = CLIENT_EXTRA_INFO.ID)
    WHERE
        CLIENT_EXTRA_INFO.MBPHONE = :phone
      and CONTRACT.STATUS = 'ACTIVE'
      and CONTRACT.FLAG IN ('2', '5')
) WHERE ROWNUM <= :row;

5. 对比执行计划找差异 #

分别获取静态SQL和参数化SQL的执行计划，定位问题：

• 控制台执行静态SQL的计划：

  EXPLAIN PLAN FOR [你的静态SQL];
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

• 查找JDBC执行的SQL计划：
  通过V$SQL找到对应SQL_ID后执行：

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('你的SQL_ID', 0, 'ALLSTATS LAST'));
  

重点查看是否使用了预期索引、连接方式（嵌套循环/哈希连接）是否合理。

6. 验证索引有效性 #

确认索引未失效且列匹配：

-- 检查索引状态
SELECT index_name, status FROM user_indexes WHERE table_name = 'CLIENT_EXTRA_INFO';
-- 检查索引列
SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'CLIENT_EXTRA_INFO';

若存在索引碎片，重建索引：

ALTER INDEX IDX_MBPHONE REBUILD;

内容的提问来源于stack exchange，提问作者Hồ Vô