Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

connection.getMetaData() called every time on executing a SELECT query on Oracle #453

Closed
wpc opened this issue Aug 23, 2013 · 4 comments
Closed
Assignees
Labels

Comments

@wpc
Copy link

wpc commented Aug 23, 2013

HI, guys

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

Thanks

@kares
Copy link
Member

kares commented Aug 24, 2013

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 ?

@ghost ghost assigned kares Aug 26, 2013
kares added a commit to kares/activerecord-jdbc-adapter that referenced this issue Aug 28, 2013
esp. caseConvertIdentifierForJdbc/Rails to accept a connection (see jruby#453)
kares added a commit to kares/activerecord-jdbc-adapter that referenced this issue Aug 28, 2013
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
@kares
Copy link
Member

kares commented Aug 29, 2013

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

kares added a commit to kares/activerecord-jdbc-adapter that referenced this issue Aug 29, 2013
esp. caseConvertIdentifierForJdbc/Rails to accept a connection (see jruby#453)
@kares kares closed this as completed in 48c4e93 Aug 29, 2013
@wpc
Copy link
Author

wpc commented Sep 13, 2013

Thank you very much for fixing this problem. We will upgrade and have a try.

@shine-sreedharan
Copy link

Hi,

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

3 participants