SQL Plus: The Sweet Spot

Need to get a bunch from rows out of the database? Most people are aware of the ARRAYSIZE parameter to improve the fetch performance back to the client, but many people are not aware of the newer ROWPREFETCH parameter.

Let's take a look at each to see how quickly we can drag data back to our SQL Plus client. I'll start with table called TX which is approximately 1500MB in size and has just over 10millions.

First, let's look at the benefits you get from ramping up ARRAYSIZE. I'll start with the default size of 15, and quickly you'll see why that is not a good starting point if you are fetching lots of rows. (Important emphasis on the "if" there)

SQL> set feedback only

SQL> set rowprefetch 1

SQL> set timing on

SQL> set arraysize 15

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:45.37

SQL> set arraysize 50

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:15.20

SQL> set arraysize 100

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:08.77

SQL> set arraysize 200

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:05.59

SQL> set arraysize 500

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:03.58

SQL> set arraysize 1000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:03.42

SQL> set arraysize 2000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:02.88

SQL>

SQL> set arraysize 3000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:02.86

SQL> set arraysize 5000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:02.93

It looks like the sweet spot is an array size of around 2000-3000 for this test. Notice that once it went out to 5000 the performance dropped a little. Its possible (unverified) that we're losing a little time just reserving/utilising client memory for all those rows.

Now we'll introduce ROWPREFETCH into the mix.

xxxxxxxxxx

SQL> set rowprefetch 500

SQL> set arraysize 500

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:03.04

SQL> set arraysize 1000

SQL> set rowprefetch 1000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:02.81

SQL> set arraysize 2000

SQL> set rowprefetch 2000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:02.86

SQL> set arraysize 3000

SQL> set rowprefetch 3000

SQL> select object_name from tx;

10129860 rows selected.

Elapsed: 00:00:02.84

We can see that it doesn't add a dramatic improvement to the performance, but it has indeed lowered the sweet spot to to an arraysize of 1000.

In any case, dragging 10 million rows out of the database in just a few seconds is impressive. But don't forget - it is easy to manipulate a benchmark. In this case, I'm only selecting a single column, and that column is near the front of each row, hence less row "slicing and dicing" is needed by the database engine. 

Query performance is not only about reading data off disk; there is also a CPU cost to extracting the rows from the blocks, and the columns from the rows, as well as constructing that information to be passed back to the client. Here's the same queries but accessing all of the (20) columns in the table TX

xxxxxxxxxx

SQL> set arraysize 100

SQL> set rowprefetch 500

SQL> select * from tx;

10129860 rows selected.

Elapsed: 00:00:32.16

SQL>

SQL>

SQL> set arraysize 1000

SQL> set rowprefetch 5000

SQL> select * from tx;

10129860 rows selected.

Elapsed: 00:00:29.21

SQL>

SQL> set arraysize 2000

SQL> set rowprefetch 5000

SQL> select * from tx;

10129860 rows selected.

Elapsed: 00:00:29.33

And also remember, the columns you select also dictates some of the mechanisms via which these rows are delivered to the client. For more on that, see the video I did on "row shipping" below.