{{announcement.body}}
{{announcement.title}}

SQL Plus: The Sweet Spot

DZone 's Guide to

SQL Plus: The Sweet Spot

Let's take a look at each to see how quickly we can drag data back to our SQL Plus client.

· Database Zone ·
Free Resource

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
 




x
60


 
1
SQL> set feedback only
2
SQL> set rowprefetch 1
3
SQL> set timing on
4
SQL> set arraysize 15
5
SQL> select object_name from tx;
6
 
           
7
10129860 rows selected.
8
 
           
9
Elapsed: 00:00:45.37
10
SQL> set arraysize 50
11
SQL> select object_name from tx;
12
 
           
13
10129860 rows selected.
14
 
           
15
Elapsed: 00:00:15.20
16
SQL> set arraysize 100
17
SQL> select object_name from tx;
18
 
           
19
10129860 rows selected.
20
 
           
21
Elapsed: 00:00:08.77
22
SQL> set arraysize 200
23
SQL> select object_name from tx;
24
 
           
25
10129860 rows selected.
26
 
           
27
Elapsed: 00:00:05.59
28
SQL> set arraysize 500
29
SQL> select object_name from tx;
30
 
           
31
10129860 rows selected.
32
 
           
33
Elapsed: 00:00:03.58
34
SQL> set arraysize 1000
35
SQL> select object_name from tx;
36
 
           
37
10129860 rows selected.
38
 
           
39
Elapsed: 00:00:03.42
40
 
           
41
SQL> set arraysize 2000
42
SQL> select object_name from tx;
43
 
           
44
10129860 rows selected.
45
 
           
46
Elapsed: 00:00:02.88
47
SQL>
48
SQL> set arraysize 3000
49
SQL> select object_name from tx;
50
 
           
51
10129860 rows selected.
52
 
           
53
Elapsed: 00:00:02.86
54
 
           
55
SQL> set arraysize 5000
56
SQL> select object_name from tx;
57
 
           
58
10129860 rows selected.
59
 
           
60
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
 




xxxxxxxxxx
1
30


1
SQL> set rowprefetch 500
2
SQL> set arraysize 500
3
SQL> select object_name from tx;
4
 
           
5
10129860 rows selected.
6
 
           
7
Elapsed: 00:00:03.04
8
SQL> set arraysize 1000
9
SQL> set rowprefetch 1000
10
SQL> select object_name from tx;
11
 
           
12
10129860 rows selected.
13
 
           
14
Elapsed: 00:00:02.81
15
 
           
16
SQL> set arraysize 2000
17
SQL> set rowprefetch 2000
18
SQL> select object_name from tx;
19
 
           
20
10129860 rows selected.
21
 
           
22
Elapsed: 00:00:02.86
23
 
           
24
SQL> set arraysize 3000
25
SQL> set rowprefetch 3000
26
SQL> select object_name from tx;
27
 
           
28
10129860 rows selected.
29
 
           
30
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
 




xxxxxxxxxx
1
24


 
1
SQL> set arraysize 100
2
SQL> set rowprefetch 500
3
SQL> select * from tx;
4
 
           
5
10129860 rows selected.
6
 
           
7
Elapsed: 00:00:32.16
8
SQL>
9
SQL>
10
SQL> set arraysize 1000
11
SQL> set rowprefetch 5000
12
SQL> select * from tx;
13
 
           
14
10129860 rows selected.
15
 
           
16
Elapsed: 00:00:29.21
17
SQL>
18
SQL> set arraysize 2000
19
SQL> set rowprefetch 5000
20
SQL> select * from tx;
21
 
           
22
10129860 rows selected.
23
 
           
24
Elapsed: 00:00:29.33
15
 
           



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.

Topics:
arraysize ,database ,rowprefetch ,sql ,sql plus ,tutorial

Published at DZone with permission of Connor McDonald , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}