Edit:
Several people mentioned the case of the table and columns...Well this is not something I can control, so please stop asking me to change them...
Now I can get a read speed at around 40MBytes/s with a local connection, following the answers I found here: https://dba.stackexchange.com/questions/299426/the-expected-read-speed-of-sqlplus
I have another issue about cx_Oracle. You may take a look if you are interested: https://dba.stackexchange.com/questions/299444/possible-memory-leak-of-cx-oracle-pyodbc-oracle-instant-client
The issue is many-fold but it boils down to this simple scenario: I have a Oracle Database Version 19.4.0.0.0 and SQL*PLUS Version 21.3.0.0.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 arraysize to 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 SDU to 65535.
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...)
Thanks!