Oracle is a fast database, but CX Oracle appears extraordinarily slow, and I have no idea why. For simple queries, CX Oracle seems to be the bottleneck.
I have a table that is a 2000x5 matrix of integers. And I have a 500us roundtrip latency to my database.
>>> %timeit cx_con.ping()
504 µs ± 7.35 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
cursor.execute is unexpectedly slow in the most simple case. It's nearly twice as slow.
In [257]: %time cursor.execute("select count(*) from matrix_table"); res = cursor.fetchall()
CPU times: user 424 µs, sys: 41 µs, total: 465 µs
Wall time: 932 µs
If I'm reading this correctly, we spend half of that time in the network (467us walltime-totalcpu). Which is fine, but what on earth is happening for the rest of the the 465us? When I took a look in 'v$sql', the query takes about 65us. Which leaves 400us to marshal a single integer. Assuming that Oracle has a fairly efficient wire protocol, that should take nanoseconds.
What worries me is that the time spent (presumably) marshalling data, increases very quickly.
In [25]: %time cursor.execute("select id,customer_id from matrix_table"); print(len(cursor.fetchmany()))
100
CPU times: user 552 µs, sys: 50 µs, total: 602 µs
Wall time: 1.04 ms
In [26]: cursor = cx_oracle.cursor()
In [27]: cursor.arraysize = 500
In [28]: cursor.prefetchrows = 500
In [29]: %time cursor.execute("select id,customer_id from matrix_table"); print(len(cursor.fetchmany()))
500
CPU times: user 947 µs, sys: 86 µs, total: 1.03 ms
Wall time: 1.89 ms
In [30]: cursor = cx_oracle.cursor()
In [31]: cursor.arraysize = 1000
In [32]: cursor.prefetchrows = 1000
In [33]: %time cursor.execute("select id,customer_id from matrix_table"); print(len(cursor.fetchmany()))
1000
CPU times: user 2.27 ms, sys: 0 ns, total: 2.27 ms
Wall time: 2.73 ms
I know Python isn't the fastest language, but spending nearly 2 milliseconds marshalling 2000 integers in a binary format is absurdly slow. As you can see, each 500 rows, seems to roughly add 800us to the processing time.
I've left the all the settings like the Oracle Session Data unit unchanged. I've run the connector with strace, and I see an expected number of syscalls.