This discussion is archived
3 Replies Latest reply: Sep 4, 2012 8:22 PM by rp0428 RSS

OJDBC DatabaseMetaData getExportedKeys very slow on 10g

923811 Newbie
Currently Being Moderated
We're running against Oracle 10g with the latest ojdbc14_g.jar. We also use Hibernate 1.9.4 and when we call database.createDatabaseSnapshot the application takes almost 4 hours. I tracked it down through hibernate to where databaseMetaData.getExportedKeys is called for every table and I found that each call takes ~3 minutes.

I found this forum post: getImportedKeys/getExportedKeys slow! but changing the optmizer_mode and rebuilding the temp tablespace didn't seem to improve performance. The other parameters mentioned seemed fine (to my very inexperienced eye). I also tried rebuilding the system statistics.

Any suggestions would be more than appreciated!
Thanks,
Adam Griffis
  • 1. Re: OJDBC DatabaseMetaData getExportedKeys very slow on 10g
    959970 Newbie
    Currently Being Moderated
    Did you find a solution to the problem? I am having the same issue on 11g.
  • 2. Re: OJDBC DatabaseMetaData getExportedKeys very slow on 10g
    rp0428 Guru
    Currently Being Moderated
    >
    We're running against Oracle 10g with the latest ojdbc14_g.jar. We also use Hibernate 1.9.4 and when we call database.createDatabaseSnapshot the application takes almost 4 hours. I tracked it down through hibernate to where databaseMetaData.getExportedKeys is called for every table and I found that each call takes ~3 minutes.
    >
    That is likely to be a Hibernate issue.

    Tests performed on 11g using ojdbc6 jar to directly execute the 'getExportedKeys' method consistently shows about 5 seconds per table for me.

    Create your own test file to measure the Java timing for that method call and post your results
        String schema;
        String tablename;
        try {
            con = . . . get a connection
            DatabaseMetaData dbmd = con.getMetaData();
            long start = System.currentTimeMillis();
            rs = dbmd.getExportedKeys("", schema, tablename);
            long stop = System.currentTimeMillis();
            System.out.println(stop - start);
    . . .
        } catch (SQLException ex) {
            System.out.println("error code : " + ex.getErrorCode());
            System.out.println("error message : " + ex.getMessage());
        } finally {
            . . . do any clean up
        }
    }
    The time taken above will tell you if it is a JDBC issue which is unlikely in my opinion.

    The time may be taken by Hibernate iterating the result set and creating all of the class instances.

    You may want to post in a hibernate forum.
  • 3. Re: OJDBC DatabaseMetaData getExportedKeys very slow on 10g
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    Did you find a solution to the problem? I am having the same issue on 11g.
    >
    I responded to OP even though it has been several months but if you need to provide any particulars about your own situation you should create your own thread and provide them there. You can add a link to this thread if you think it will help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points