1

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 (

Edit1: after revising my script following @ik_zelf's answer, I get 8-9 MBytes/s with a local connection (same machine) and 4 Mbytes/s with a remote connection (within a LAN);

Edit2: after adding a -FAST option, following @Andrew Sayer's comment, I get 50-60 MBytes/s with a local connection (same machine) and 10-15 Mbytes/s with a remote connection (within a LAN); ).

Edit3: Get some results from set autotrace traceonly statistics:

122096809 rows selected.


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
    2097347  consistent gets
    1994129  physical reads
        220  redo size
 4626657759  bytes sent via SQL*Net to client
     269324  bytes received via SQL*Net from client
      24421  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
  122096809  rows processed

Edit4: In my case, index is actually NOT used. However, this turns out to be not the cause of the issue. (But this means that some of the below observation is INcorrect)

Some extra observation and tuning:

  1. 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 8-9 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;
  2. 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.
  3. 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!

9
  • 1
    If you want sql*plus to extract lots of data fast, use fast mode (that’s not sarcasm, there’s a literal switch for it). You saying that you see the Oracle process running at 300+ Mb/s does not correspond to lookups via index - are you sure you’re not full scanning (and therefore reading way more data than you are having to transmit)? Your date filter is for a years worth of data, for reasonably sized tables if you want this to be fast, partitioning is a good idea. That said, why are you just spooling it to a file, what’s the actual purpose of that file? Expdp might be a more suitable solution Commented Sep 11, 2021 at 14:56
  • Additionally, always use the correct data type, you are comparing your column to a string. If your column is a date data type (it should be) then Oracle will have to implicitly convert your string to a date, you should do this explicitly, providing the date format (and using 4 digits for years) Commented Sep 11, 2021 at 14:57
  • Hi @AndrewSayer, firstly--yes! the -FAST option is much faster lol. Regarding the 300+ Mbytes/s read, I am not sure if index is play a big role. But my emphasis is in the later half--Oracle reaches 300+MB/s immediately after I execute my query, it also uses a lot of CPU and memory--my original idea is that, it implies Oracle is very busy. After a few seconds, hard drive IO, CPU goes to nearly zero, implying Oracle is done. This is the source of my feeling that Oracle gets the data ready within a few seconds since it is not working anymore, not necessarily because of indexing.
    – user238532
    Commented Sep 11, 2021 at 15:21
  • regarding the file issue, it is actually more complicated then the question...We need to convert this resultant table into a Python object, so that our program can further handle it. I tried using Python packages (i.e., pyodbc and cx_Oracle)to do this directly--there appears to be a memory leakage issue, basically the data is around 2GB in size, but my Python script uses more than 30GB (my workstation only has 30, so not sure how high they will be) to fetch them. So I am investigating the approach of saving everything to a csv, just for interoperability.
    – user238532
    Commented Sep 11, 2021 at 15:26
  • 1
    Yes, whichever of those ways you can do (probably best to use the ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; to start and ALTER SESSION SET EVENTS '10046 trace name context off'; to finish from your session running the process. You can also use the tracefile_identifier if you’re not sure how to identify which file is yours. Commented Sep 12, 2021 at 7:48

1 Answer 1

1

You are not measuring the read speed. If you dig a bit deeper you will discover that most time is spent formatting the output and writing it to stdout.

For the real read speed a table scan will be quicker than an indexed read.

If you look in the wait events of your session, the event sqlnet more data to client will be the winner, if there are a lot of rows to write. https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_net_more_to_client_pct.html

If your real goal is to test your max io performance, use slob. See https://kevinclosson.net/category/oracle-io-performance/

Slob is server only, taking away all protocol and client overhead.

9
  • I actually set these parameters: arraysize 5000, set colsep ,, set headsep off, set pagesize 0, set trimspool on. Do you mean that I should turn off the SQL*PLUS stdout altogether?
    – user238532
    Commented Sep 11, 2021 at 14:27
  • Ah yes you are very correct--after turning off the stdout (following this link: toolbox.com/tech/oracle/question/…), I get an output speed at around 8MBytes/s. Is this the upper limit then? Since we have index, my original thought is that the only bottleneck is my hard drive's IO--but 8Mbytes/s is definitely still far from it.
    – user238532
    Commented Sep 11, 2021 at 14:39
  • If you want to see the max scan speed, drop the index. Compared to dd it should come closer. Also make multi block read count larger.
    – user953
    Commented Sep 11, 2021 at 15:09
  • Hi @ik_zelf, when saying "300MB/s speed", my emphasis is this: Oracle reaches 300+MB/s immediately after I execute my query, it also uses a lot of CPU and memory--my original idea is that, it implies Oracle is very busy. After a few seconds, hard drive I/O and CPU drop to nearly zero, implying Oracle is done. So my argument here is that my script is slow NOT because Oracle is busy finding and organizing the data to sent, but rather, Oracle is ready and just waiting the request to fetch those data. So my feeling is that, Oracle service is fine, it is SQL*PLUS that is causing the issue.
    – user238532
    Commented Sep 11, 2021 at 15:30
  • 1
    Yes, sqlplus is busy digesting and formatting. You could use bequeth connection to strip the tcp protocol overhead.
    – user953
    Commented Sep 11, 2021 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.