Oracle generates schema and table stats to help its query optimizer do its job. These statistics need to be regenerated periodically, particularly after inserting a lot of rows into fresh tables, or after deleting a large number of rows. See this Oracle gather statistics document for more details.
Gathering schema statistics can take some time particularly on large tables and usually this is performed nightly via a scheduled job setup by your DBA. However, it can be useful to trigger this manually from your Java EE web application. Here are two approaches for doing this using Java EE 6, JPA 2.0 and Eclipselink.
The first approach simply uses a JPA native query:
//JPA Native query approach
Query q = entityManager.createNativeQuery("BEGIN DBMS_STATS.gather_schema_stats(ownname=>?); END;");
q.setParameter(1, "FOO"); //FOO is the name of the schema you want to gather stats on
q.executeUpdate();
The second approach uses native Eclipselink PLSQL support. It's overkill but an interesting exercise none the less:
PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
call.setProcedureName("DBMS_STATS.gather_schema_stats");
call.addNamedArgument("ownname", JDBCTypes.VARCHAR_TYPE, 8);
DataReadQuery q = new DataReadQuery();
q.addArgument("ownname");
q.setCall(call);
List qArgs = new ArrayList();
qArgs.add("FOO"); //Name of our schema
//Obtain the native Eclipselink entity manager and session to execute the stored procedure call in
JpaEntityManager jpaEm = JpaHelper.getEntityManager(entityManager);
Session s = jpaEm.getActiveSession();
s.executeQuery(query, queryArgs);
Both of these samples are part of a method which is on a EJB 3.1 stateless session bean. In this case we leverage the EJB 3.1 no interface local bean construct to simplify things.
As mentioned, running gather stats can take a long time and it does not provide incremental status updates of its progress. To accommodate this I have created a light weight asynchronous task framework using EJB 3.1's @Asynchronous functionality, JAX-RS, and CDI deployed to Glassfish 3.1. Using this framework I am able to make a call to my web application using ReST which will kick off an asynchronous generate stats task. I can then poll the web application via ReST to find out when the task has completed. I'll post details of this in future articles.
Recent Comments