The issue is many-fold but it boils down to this simple scenario: I have a Oracle Database Version 184.108.40.206.0 and SQL*PLUS Version 220.127.116.11.0 running on the same Red Hat Linux (I actually tried both local and remote connections, here let’s just consider the local case to separate concerns…). I run this SQL statement:
SELECT "Date", "ID", "Name", "Value" FROM "A_Table" WHERE ("A_Table"."Date" >= '11-SEP-20' AND "A_Table"."Date" <= '11-SEP-21');
So basically I just want to fetch all the rows based on
Date and the resultant file should be around 2GB in size. The statement works, but after using a wide range of techniques (ICYW, techniques including checking filesystem IO, network IO and file size regularly), I am sure that this
SELECT statement can only fetch data at a speed of 2 MByte/s maximum.
Some extra observation and tuning:
- An index is correctly built and this speed limit is NOT from Oracle finding the right data to send, but rather, all data are there ready to be sent, just somehow they cannot be sent at a speed higher than 2 MBytes/s–I observe that, at first few seconds, Oracle service will read hard drive at 300-500MB/s, after a short while (i.e., a few seconds), it stops reading and then the SELECT command starts showing results. So I believe that Oracle is done reading and organizing;
- I set
arraysizeto 5,000, which, according to Oracle’s manual, seems to be highest possible value (but sure I tried other values such as 100 as well)–this is particularly odd, I tried tuning this parameter when using
cx_Oracle, it is very effective.
- I also tried setting
Nothing seems working.
Is this the normal speed that I can expect from SQL*PLUS? (But I believe the answer is no, seems I tried using Python’s
cx_Oracle package which in turn, utilizing Oracle Instant Client to do the fetching, I can reach a speed of around 40MBytes/s, but that one has other issues which prevent us from using it at the moment…)