You are not logged in.

#1 2016-08-30 15:33:48

sahbi
Member
Registered: 2016-08-30
Posts: 3

oracle-xe metadata jdbc query too slow

Hi all,

I recently switched from Ubuntu to Arch, installed oracle-xe without any issues, except that the JDBC queries on metadata are incredibly slow. I am working on an enterprise product which sends this type of query to database when loading the datasource :

final Properties props = new Properties();
props.setProperty("user", "theuser");
props.setProperty("password", "thepassword");
final OracleConnection connection = (OracleConnection) DriverManager.getConnection(dburl, props);
final DatabaseMetaData metaData = connection.getMetaData();
final String[] types = { "TABLE" };
final ResultSet rs = metaData.getTables(null, null, null, types);
...

This final instruction lasts for about 2 minutes on Arch, while it takes nothing on Ubuntu, and I cannot figure out what in the OS could lead to that. I know it's a bad practice to put "null" in the schema parameter since Oracle will scan the whole DB, but the code is coming from the enterprise product I am using and which cannot be modified. Actually, I wrote a simple Java class with the code above, and specifying the schema or anything else doesn't change the response time.
I also tried to build my class with ojdbc6_g.jar for the debug mode, and surprisingly, during the execution of this statement, the JDBC logger does not write a single line in the log file.

The executed query (found in the jdbc log file) is :
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

It takes no time to be executed in SQLDeveloper or any other client, but takes forever with JDBC. No errors or warnings anywhere..

I am using :
- Kernel: x86_64 Linux 4.7.2-1-ARCH
- Oracle JDK 1.8.0_102
- oracle-xe 11.2.0-1.0
- ojdbc6

I know this looks more like an Oracle issue, but I am using the exact same version of everything on Ubuntu and I never had this problem. Also, I did not have to customize any settings when installing oracle-xe. And queries not related to metadata get executed promptly. I have been investigating this issue for several weeks and I am really running out of ideas. It would really be a pity to have to switch to another distro or to use a VM for Oracle just because of this issue..

Any help is greatly appreciated!

Offline

#2 2016-08-30 16:06:06

ewaller
Administrator
From: Pasadena, CA
Registered: 2009-07-13
Posts: 19,740

Re: oracle-xe metadata jdbc query too slow

Welcome to Arch Linux.   Just a shot in the dark -- is your hostname set correctly?  What are the output of hostnamectl    ?


Nothing is too wonderful to be true, if it be consistent with the laws of nature -- Michael Faraday
Sometimes it is the people no one can imagine anything of who do the things no one can imagine. -- Alan Turing
---
How to Ask Questions the Smart Way

Offline

#3 2016-08-31 08:23:01

sahbi
Member
Registered: 2016-08-30
Posts: 3

Re: oracle-xe metadata jdbc query too slow

Thank you for your answer !

I guess it is.
The output is :
   Static hostname: sahbi-arch
         Icon name: computer-laptop
           Chassis: laptop
        Machine ID: 62eefbcec2944be29b37696d2195601d
           Boot ID: 563a34fb2a474b09bbd945239ca3d2bb
  Operating System: Arch Linux
            Kernel: Linux 4.7.2-1-ARCH
      Architecture: x86-64

I think if the hostname wasn't set correctly, I would not have been able to connect to Oracle in any way. Actually, I am able to connect to the schemas that I have created, and everything works fine, except the metadata queries through JDBC, which also run properly giving the right results, but they take about 2 minutes to get executed. Really strange...

Offline

#4 2016-09-19 12:22:21

sahbi
Member
Registered: 2016-08-30
Posts: 3

Re: oracle-xe metadata jdbc query too slow

Anybody has an idea about this?

Offline

Board footer

Powered by FluxBB