 |
|
|
|
|
|
|
|
Unclogging those network tubes It’s usual for your Oracle client – be it an application server, windows client or even humble old SQL*Plus – to be running on a computer other than the server hosting the database. Obviously, in those circumstances, Oracle must transfer data between the client and the database server. Occasionally, this network traffic can in itself become a bottleneck.
When a US politician described the internet as “a series of tubes” he was ridiculed for his lack of technical sophistication. However, I a think a "series of tubes" is not a bad analogy for a layperson, since it at least intuitively explains the concepts of bandwidth and latency.
Messages between two locations on a network are passed as packets that travel across the physical network layers – such as fibre optic cable - at a significant fraction of the speed of light. However, packets must pass through routers that direct the packets to their ultimate destinations. The time spent in routers is usually far greater than the time spent travelling down the “pipes”. Also, if more packets hit the router than can be immediately processed, then packets will be queued. In extreme cases, packets might be lost - and then resent – if the number of packets queued exceeds the buffer capacity of a router.
Consequently, if the number of packets sent across the network is excessive then routers will begin to queue messages and network latency – the time taken to send a packet from source to destination – will increase. We might call that situation network contention. The maximum number of packets that can be sent from source to destination in any unit of time is referred to as the bandwidth of the network.
From all of the above, it’s fairly clear that to reduce network contention we need to either improve the bandwidth of the network or reduce the number of packets sent across the network. Increasing network bandwidth is a complex topic, but reducing packet transmissions in Oracle is actually fairly straight forward.
When an Oracle client wants to retrieve rows from a result set, the code at some level will look something like this:
UNTIL all rows retrieved
FETCH next ROW
DONE
If each of the FETCH operations results in a network operation, then there will be two network packets for each row retrieved: one to request the next row, one with the row in it. However, network packets are typically 2K at least in size, and if each row were 100 bytes long, then we could fit 200 rows in each packet. So we would be better off doing something like this:
UNTIL all rows retrieved
FETCH 100 ROWS
DONE
Oracle’s array interface allows you to do exactly this. Using the array interface reduces the number of network packets and so will reduce network contention. Just as importantly, each network round trip involves some network latency, so we improve our response time as well. Figure 1 shows how response time improved dramatically as we increase the array size. Eventually, however, increasing the array size stops helping, since the network packets are already full. As well as using the array interface to improve bulk selects, it’s also very important in optimizing bulk inserts.
Figure 1 Relationship between array size and elapsed time
So exactly how do we exploit this important feature? Unfortunately, each programming environment is different in this respect. For a lot of tools, the array size is set with a configuration parameter. For instance, in SQLPLUS the ROWCOUNT variable transparently controls the array size. In TOAD, it’s set in the Oracle/General options (Figure 2).
Figure 2 Setting the array size in TOAD
In many programming languages you often have to modify your code to exploit the array interface. For instance in PL/SQL the BULK COLLECT keyword can be used to perform array selects (Figure 3), while the FORALL keyword can be used to perform array inserts. Quest PL/SQL guru Steven Feuerstein explains how to do this in this Toad World database tip. Although PL/SQL does not perform network round trips (it runs inside the database server), using the array interface in PL/SQL still reduces overhead quite dramatically.
Figure 3 Using array fetch in PL/SQL
Most versions of Oracle Java drivers will automatically use the array interface, but you can always set it explicitly using the setFetchSize parameter as shown in Figure 4. Doing array inserts is a little tricker, but can be done using the addBatch() method. Virtually every programming language – C#, PHP, Perl, Ruby, etc – supports the array interface, but it’s often up to the programmer to make it happen. Table 1 provides a starting point for some popular languages,
| Language | Array fetch interface | | Oracle Data Provider for .NET (C#, VB.NET) | Set the FetchSize property of the OracleCommand or OracleDataReader object.
| | Java | setFetchSize method of the Statement object | | perl | Set the RowCacheSize property of the database handle (eg, $db->{RowCacheSize} = 100;) | | OCI | Use OCIDefineByPos() to define an array as destination for output and OCIStmtFetch() to provide array size | | PHP | If using the OCI8 interface, set the oci8.default_preset propery. | | Python | If using the cx_oracle interface, set the arraysize property of the cursor object | Table 1: How to do array fetch in various programming languages
Figure 4 Setting the array size in Java
Spotlight will raise an alarm should it detect that a significant proportion of network traffic could be eliminated by the use of the array interface. Drilldowns to the alarm will identify the sessions and SQLs associated with poor array interface usage.
Figure 5 Spotlights array interface alarm
Using the array interface is one of the simplest ways to optimize a program that retrieves or inserts large number of rows. As well as optimizing individual SQLs and programs, it also reduces overall network packet transmissions, thereby reducing the chance of network contention or – as some might put it – “clogging up the tubes”.
|
|
|
|
|
|
|
|
|
|
|
|
|
|