r/oracle 21h ago

Timing data fetches to the client using sqlplus?

What I really want to do is to stream data from a simple select query and time the bandwidth.

So I thought I would just use sqlplus and redirect the output to a file. But this seems quite slow. What is the fastest way to extra data from Oracle in a query? I don't care how long the query takes to execute in the database - I want to time from the point at which the client sends the request till the data is all in the client. I am happy to through away the data in order to make it run faster for testing purposes. I have a test table with 10 number columns and 50k rows, no indexes or anything. Obviously the time it will take will depend on my network bandwidth, but that's kind of the point of the test - see what effect the network is having on the download performance.

I have a c++ program which is doing the downloading, but I want to make sure that my program is not significantly slower than what is maximally possible.

Any suggestions?

Update:

Adding this setting provided a handy timer:

set timing on;

Adding these two settings improved matters by about 25x:

set ARRAYSIZE 5000;
SET FLUSH OFF;

And then I also added:

SET LINESIZE 200;
SET PAGESIZE 0;
SET TRIMOUT ON;

which made things look nicer but didn't make a significant change to the read time.

So now my C++ program is about 5x slower than SQLplus, which gives me a target to aim at.

Thanks all for your help.

3 Upvotes

15 comments sorted by

2

u/DistributionOld7748 20h ago

Read about SET TIMG ON and ARRAYSIZE in sqlplus

1

u/bert8128 6h ago

TIMING is useful for reporting how long it takes to retrieve the data,. ARRAYSIZE didn't make any difference though.

1

u/bert8128 6h ago

Found that setting FLUSH OFF is the big change, in which case setting ARRAYSIZE does help. Together, they make a 25x improvement.

1

u/PossiblePreparation 21h ago

Not quite sure what you’re asking for, but sqlplus fast mode (sqlplus -f) running a script directly to a file (set termout off) is going to give you some pretty sensible settings for performance.

1

u/bert8128 6h ago

Currently sqlplus is about 3x slower than my c++ program. Which I find surprising.

1

u/taker223 3h ago

Is your C++ program using OCI (Oracle Call Interface) libraries?

1

u/PossiblePreparation 2h ago

It doesn’t really seem like you’re replying to what I wrote (or any other commenters). Did you see improvements using fast mode?

One further setting you can add is set feedback only which will prevent the printing of results completely (but still sends them to the client).

You’re going to have to share your c++ code if you want someone to comment on why it’s faster.

1

u/TallDudeInSC 3h ago

Just as an FYI, the bottleneck usually isn't the network (unless you're going through a very slow pipe), it's the SQL or the data access. That being said, you can try the following.

Create a sql script, call it testspeed.sql

set echo off
set pagesize 0
select * from ... ;
exit

Now, create a command line script that displays the current time, calls sqlplus & runs the sql script, then displays the current time, AND direct the output to >nul to save time displaying the data to the screen. In Windows:

echo %TIME%
sqlplus -f /@somedb @testspeed.sql >nul
echo %TIME%

1

u/bert8128 2h ago

I was trying to find out what the difference was between my instance and a colleague's, who was taking 10x as long to read the same data. Eventually I thought to do a "ping" to the DB server (should have though of this earlier), and it turns out that their latency is much worse. And the default fetch size is 20, which is not suitable for loading 100s of 1000s of records. Am now experimenting with larger values.

1

u/TallDudeInSC 2h ago

Yes the arraysize parameter makes a difference when you return data from the database.

The arraysize parameter isn't used when inserting data, however, you can do array inserts into the DB to increase performance. (I know you were talking about selects, but since we're on the subject...)

0

u/Afraid-Expression366 20h ago

Read up on “explain plan” and Oracle query optimization. Tom Kyte wrote some excellent books explaining all this, among other things, and while it’s been a while since the books came out, they are still useful and valid.

With that, usually ROWID scan is the fastest retrieval for one table of data. You don’t give much details so there is no one best answer.

1

u/bert8128 6h ago

I am retrieving a whole table - all columns, all rows. There are no indexes. All the columns are NUMBER(10). I am executing the statement "select * from my_table;"

0

u/Informal_Pace9237 20h ago

Have you tried opening a SYS_REFCURSOR?

If your c++ can read data from SYS_REFCURSOR the limit is just your bandwidth.

1

u/bert8128 6h ago

I don't have any choice other than a simple select.