We are working on a Rails application with Oracle 11g with JDBC adapter. In our production environment we found every time we execute a select SQL there is another SQL fire up for collecting meta data from database. This is running slow in our oracle server and occupying resources.
The SQL looks like following:
SELECT NULL AS table_cat,
o.owner AS table_schem,
o.object_name AS table_name,
o.object_type AS table_type,
NULL AS remarks FROM all_objects o
WHERE o.owner LIKE :1 ESCAPE '/' AND
o.object_name LIKE :2 ESCAPE '/' AND
o.object_type IN ('xxx', 'TABLE')
ORDER BY table_type, table_schem, table_name
The JDBC driver we are using is the latest ojdc6.jar
Hey @wpc, what version are you using - does the same happen from master / 1.3.0.rc1 ?
By looking at the SQL it's not obvious but could it be the necessary bits for obtaining column info for the result set ?
delay connection.getMetaData esp. with queries, changes some signatures
esp. caseConvertIdentifierForJdbc/Rails to accept a connection (see #453)
avoid connection.getMetaData on Oracle with exec_query (fixes #453)
Oracle seems to be doing some SQL on getMetaData ... this seems to improve performance singnificantly: test run times :
before: 185-190s / 180 PS
after: 130-150s / 125 PS
this is quite surprising but true - we can avoid connection.getMetaData (we only need it to get info on stored upper-case/lower-case identifiers) which with Oracle's JDBC are methods that simply return true/false values yet the driver does perform "something", as you mention, since this speeded-up tests: from 185-190s / 180 PS to 130-150s / 125 PS
Thank you very much for fixing this problem. We will upgrade and have a try.
I face the exact same problem in my java application. Do you know whether this fix is available in jdbc driver(ojdbc.jar)? If yes, which version?
Thanks in advance for the help.