Java7+Spring2多线程查询Oracle大表线程响应逐次变慢的性能问题排查

基础环境配置 #

• 技术栈版本：Oracle数据库、Spring 2框架、Java 7
• 连接池配置：采用与线程数等量连接的PoolDataSource，驱动类为oracle.jdbc.pool.OracleDataSource，用于对单一大表开展并行分析
• 连接池状态校验：已通过日志输出poolDataSource.getStatistics()确认，任意时刻连接池均具备充足可用连接，不存在连接获取阻塞问题

核心业务实现 #

业务层逻辑 #

固定线程池的核心业务代码如下：

ExecutorService executorService = Executors.newFixedThreadPool(nbThreads);
List<Foo> foo = new ArrayList<>();
List<Callable<List<Foo>>> callables = new ArrayList<>();
int offset = 1;
while(offSetMaxReached) {
    callables.add(new Callable<List<Foo>> {
        @Override
        public List<Foo> call() throws SQLException, InterruptedException {
            return dao.doTheJob(...);
        }
    });
    offset += 10000;
}
for(Future<List<Foo>> fooFuture : executorService.invokeAll(callables)) {
    geometrieIncorrectes.addAll(fooFuture.get());
}
executorService.shutdown();
executorService.awaitTermination(1, TimeUnit.DAYS);

数据层实现 #

• 每个并行任务从连接池获取独立连接
• 采用Spring JdbcTemplate的query方法执行查询
• 通过PreparedStatementSetter绑定查询参数，通过RowCallBackHandler处理结果集
• 所有任务的核心结果处理逻辑完全一致，统一执行的查询SQL为：SELECT A, B, C FROM MY.BIGTABLE OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

异常表现 #

• 理论预期：所有并行线程处理的数据量完全相同、执行逻辑完全一致，各任务执行耗时应当相近
• 实际观测：每个线程的完成时长逐次升高。以4个线程并行启动的场景为例，各线程处理结果集首行数据的时间分别为1.5s、9s、18s、35s，后续启动线程的处理延迟会进一步升高

问题根因与优化方案 #

核心根源与所查询表的分页机制相关：使用OFFSET x ROWS FETCH NEXT y ROWS ONLY语法时，Oracle需要从第一行开始遍历到对应偏移位置才能返回目标数据，因此访问小偏移量的速度远高于访问大偏移量的速度

• 最终优化效果：通过临时表配合合理设计的索引，获得了稳定的理想响应速度

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