I believe this query is coming from the Oracle JDBC driver to implement a Hibernate request to retrieve database object info through DatabaseMetaData.
This query shouldn't be too expensive, or at least isn't on a system I have handy. What's your count of all_objects and more importantly, what do you see in the rows/bytes total for the explain plan?
Is the sys schema in your 10g database analyze with updated stats? Have you collected stats on the fixed tables in the sys schema. Queries on all_objects shouldn't be that taxing to a system. If you run the query via autotrace/tkprof what/where is the major of the resources be spent at.
It's indeed coming from Hibernate and specifically org.hibernate.tool.hbm2ddl.TableMetadata. It's under each been used to validate the schema (table and column mapping). Apparently it's unnecessarily been executed on every spawned request or session instead of only once during application's startup. Are you for example not unnecessarily calling the Hibernate Configurator on every request or session?
As pointed out by @BalusC, this query is performed during schema validation. But validation is usually done once for all when creating the SessionFactory (if activated). Do you call the following method explicitely: Configuration#validateSchema(Dialect, DatabaseMetadata)?
Now, is that correct? It's there a way for me to configure the OC4J for it to instantiate filters only once?
Your implementation of the Open Session In View looks fine (and is very close to the one suggested in this page). And according to the Servlet specification only one instance per <filter> declaration in the deployment descriptor is instantiated per Java Virtual Machine (JVMTM) of the container. Since it is very unlikely that this isn't the case with OC4J, I'm tempted to say that there must something else.
Can you put some logging in the filter? What about making the SessionFactory static (in a good old HibernateUtil class)?
We have this same issue using Oracle 10g and hibernate. In an 11 hour period this query was run 58,590 times and was 11% of the DB's CPU time. We create one SessionFactory per session, so we are not creating a SessionFactory for every request. Any ideas about this?
This is coming from the default C3PO test query. Supply a simpler query in your configuration. Something like, select 'X' from dual.
All right, after months of looking at the thing, it turns out that the problem wasn't my web application. The problem was the other Oracle Forms applications that use the same instance (different user) of the database.
What was happening was that the Oracle Forms applications were locking records on the database, therefore making pretty much all of the work of the database extremely slow (including my beloved Hibernate query).
The reason of the locks was that none of the foreign keys of the Oracle Forms apps were indexed. So as my boss explained it to me (he discovered the reason) when a user is editing the master record of a master-detail relationship in a Oracle Form application, the database locks the entire detail table if there is no index for its foreign key. That is because the way the Oracle Forms works, that it updates all af the fields of the master record, including the ones of the primary key, wich are the fields that the foreign key references.
In short, please NEVER leave your foreign keys without indexes. We suffered a lot with this.
Thanks to all of you who took the time to help.