Running statistics on Oracle tables from Java program.

Oracle database query optimizer relies heavily on statistics. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Normally, having correct and up-to-date statistics falls under DBA responsibilities (Oracle recommends automatic statistics gathering). However, there are cases when it might be necessary to update statistics manually, and moreover, at runtime directly from a Java program. For example, when data had been reloaded into the tables and now need to be accessed from a Java batch program while database instance is not configured for automatic statistics update. Below is an example of Java code that implements a method to gather statistics for a particular table. The code is based on the JdbcDaoSupport class from Spring framework but it is a simple convenience and author's preference - there is nothing that would prevent from porting this code to the standard plain JDBC implementation.

/*
 * Copyright 2012 VT Enterprise Software Consulting Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.vtesc.examples.db;

import java.sql.Types;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

/**
 * Demonstrates invocation of dbms_stats.gather_table_stats procedure
 * to gather an Oracle table statistics.
 * @author Vitali Tchalov
 */
public class JavaOracleStats extends JdbcDaoSupport {
 /**
  * Updates statistics for table in schema using
  * Oracle dbms_stats.gather_table_stats procedure.
  * 
  * @param schema
  * @param table
  */
 @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
 public void gatherTableStats(String schema, String table) {
  JdbcTemplate jdbcTemplate = new JdbcTemplate(getJdbcTemplate()
    .getDataSource());
  jdbcTemplate.setResultsMapCaseInsensitive(true);

  SimpleJdbcCall gatherStats = new SimpleJdbcCall(jdbcTemplate)
    .withProcedureName("dbms_stats.gather_table_stats")
    .withoutProcedureColumnMetaDataAccess()
    .useInParameterNames("ownname", "tabname")
    .declareParameters(new SqlParameter("ownname", Types.VARCHAR),
     new SqlParameter("tabname", Types.VARCHAR));
  SqlParameterSource in = new MapSqlParameterSource().addValue("ownname",
    schema).addValue("tabname", table);

  gatherStats.execute(in);
 }
}

This is a fully tested and usable code. But I plan to package this example along with a JUnit test, Spring context file and other artefacts and will publish when ready.