SQL Plus and the ROWPREFETCH parameter

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.

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

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.