How can I improve query performance for CLOB and LONG values in Oracle-DB (cx_Oracle vs OJDBC)?

Viewed 2081

I have performance problems when querying CLOBs and LONGs of big Oracle database tables.

So far, I wrote the following unit tests with cx_Oracle (python) and JDBC (java):

Python code using cx_Oracle:

class CXOraclePerformanceTest(TestCase):
    def test_cx_oracle_performance_with_clob(self):
        self.execute_cx_oracle_performance("CREATE TABLE my_table (my_text CLOB)")

    def test_cx_oracle_performance_with_long(self):
        self.execute_cx_oracle_performance("CREATE TABLE my_table (my_text LONG)")

    def execute_cx_oracle_performance(self, create_table_statement):
        # prepare test data
        current_milli_time = lambda: int(round(time.time() * 1000))
        db = cx_Oracle.connect(CONNECT_STRING)

        db.cursor().execute(create_table_statement)
        db.cursor().execute("INSERT INTO my_table (my_text) VALUES ('abc')")

        for i in range(13):
            db.cursor().execute("INSERT INTO my_table (my_text) SELECT 'abc' FROM my_table")

        row_count = db.cursor().execute("SELECT count(*) FROM my_table").fetchall()[0][0]
        self.assertEqual(8192, row_count)

        # execute query with big result set
        timer = current_milli_time()

        rows = db.cursor().execute("SELECT * FROM my_table")
        for row in rows:
            self.assertEqual("abc", str(row[0]))

        timer = current_milli_time() - timer
        print("{} -> duration: {} ms".format(create_table_statement, timer))

        # clean-up
        db.cursor().execute("DROP TABLE my_table")
        db.close()

Java code using ojdbc7.jar:

public class OJDBCPerformanceTest {

    @Test public void testOJDBCPerformanceWithCLob() throws Exception {
        testOJDBCPerformance("CREATE TABLE my_table (my_text CLOB)");
    }

    @Test public void testOJDBCPerformanceWithLong() throws Exception {
        testOJDBCPerformance("CREATE TABLE my_table (my_text LONG)");
    }

    private void testOJDBCPerformance(String createTableStmt) throws Exception {
        // prepare connection
        OracleConnection connection = (OracleConnection) DriverManager.getConnection(connectionString);
        connection.setAutoCommit(false);
        connection.setDefaultRowPrefetch(512);

        // prepare test data
        Statement stmt = connection.createStatement();
        stmt.execute(createTableStmt);
        stmt.execute("INSERT INTO my_table (my_text) VALUES ('abc')");

        for (int i = 0; i < 13; i++)
            stmt.execute("INSERT INTO my_table (my_text) SELECT 'abc' FROM my_table");

        ResultSet resultSet = stmt.executeQuery("SELECT count(*) FROM my_table");
        resultSet.next();
        Assert.assertEquals(8192, resultSet.getInt(1));

        // execute query with big result set
        long timer = new Date().getTime();

        stmt = connection.createStatement();
        resultSet = stmt.executeQuery("SELECT * FROM my_table");
        while (resultSet.next())
            Assert.assertEquals("abc", resultSet.getString(1));

        timer = new Date().getTime() - timer;
        System.out.println(String.format("%s -> duration: %d ms", createTableStmt, timer));

        // clean-up
        stmt = connection.createStatement();
        stmt.execute("DROP TABLE my_table");
    }

}

Python test output:

CREATE TABLE my_table (my_text CLOB) -> duration: 31186 ms
CREATE TABLE my_table (my_text LONG) -> duration: 218 ms

Java test output:

CREATE TABLE my_table (my_text CLOB) -> duration: 359 ms
CREATE TABLE my_table (my_text LONG) -> duration: 14174 ms
  • Why is the difference between both durations so high?
  • What can I do to improve the performance in one or both programs?
  • Is there any Oracle specific option or parameter which I can use to improve the query performance?
2 Answers
Related