如何提高Oracle-DB中的CLOB和LONG值的查询性能(cx_Oracle vs OJDBC)?
查询大Oracle数据库表的CLOB和LONG时,出现性能问题。如何提高Oracle-DB中的CLOB和LONG值的查询性能(cx_Oracle vs OJDBC)?
到目前为止,我写了下面的单元测试与cx_Oracle(蟒蛇)和JDBC(Java):
Python代码使用cx_Oracle:使用ojdbc7.jar
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代码:
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的测试输出:
CREATE TABLE my_table (my_text CLOB) -> duration: 31186 ms
CREATE TABLE my_table (my_text LONG) -> duration: 218 ms
Java测试输出:
CREATE TABLE my_table (my_text CLOB) -> duration: 359 ms
CREATE TABLE my_table (my_text LONG) -> duration: 14174 ms
- 为什么既是持续时间如此之高的区别?
- 如何改善一个或两个程序的性能?
- 是否有任何Oracle特定的选项或参数可用于提高查询性能?
经过一番研究,我可以部分回答我的问题。
我设法提高了OJDBC的性能。 OJDBC API提供了属性useFetchSizeWithLongColumn
,您可以通过它快速查询LONG列。
新建查询时间: CREATE TABLE my_table (my_text LONG) -> duration: 134 ms
Oracle文档:
它是一个很小的唯一属性。它不应该与任何其他驱动程序一起使用。 如果设置为“true”,则在“SELECT”中检索数据时的性能将得到改进,但处理LONG列的默认行为将更改为获取多行(预取大小)。这意味着将分配足够的内存来读取这些数据。因此,如果您想使用此属性,请确保您检索的LONG列不会太大,否则可能会导致内存不足。这个属性也可以设置为Java属性:
java -Doracle.jdbc.useFetchSizeWithLongColumn=true myApplication
或通过API:
Properties props = new Properties();
props.setProperty("useFetchSizeWithLongColumn", "true");
OracleConnection connection = (OracleConnection) DriverManager.getConnection(connectionString, props);
- http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html
- http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html
我还没有cx_Oracle的解决方案。这就是为什么我开了一个github上的问题:
要获得相同的性能,长,你需要告诉cx_Oracle以那种方式来获取的CLOB。你可以看看这个例子: https://github.com/oracle/python-cx_Oracle/blob/master/samples/ReturnLongs.py。
在代码中,我添加了这个方法:
def output_type_handler(self, cursor, name, defaultType, size, precision, scale):
if defaultType == cx_Oracle.CLOB:
return cursor.var(cx_Oracle.LONG_STRING, arraysize = cursor.arraysize)
然后,已创建数据库连接后,我加入这个代码:
db.outputtypehandler = self.output_type_handler
这些变化,性能几乎完全相同。
需要注意的是在幕后,cx_Oracle使用动态获取和分配。这种方法对于小的CLOB(小的一般意味着几兆字节或更少)非常适用。在这种情况下,数据库可以直接发送数据,而当使用LOB的,只是定位器返回到客户端,则需要另一轮的访问数据库获取数据。你可以想像,那显著减慢运行,特别是在数据库和客户端在网络上分离出来!