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.
Join the DZone community and get the full member experience.Join For Free
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)
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.
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
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.
Published at DZone with permission of Connor McDonald, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.