what is arraysize in sqlnet ...Where is the parameter used .how it has to be used ...Has anybody used that ?? what was the signigicant performance gain that you got on using the arraysize.
Pls do explain as elobrate as possible ..
Printable View
what is arraysize in sqlnet ...Where is the parameter used .how it has to be used ...Has anybody used that ?? what was the signigicant performance gain that you got on using the arraysize.
Pls do explain as elobrate as possible ..
There is a document in Metalink that is taking about SQL*NET performance and Arraysize, I hope this will help you.
How can I tune SQL*Net?
Solution Description:
=====================
1. Does SQL*Net send data to TCP in "array size" increments? How does
this happen?
SQL*Net is a messenger service. If the application says, "Send two bytes,"
that's what SQL*Net sends, along with the overhead associated with networking
communications. If the application says, "Send five millions bytes," SQL*Net
chops the data into "SDU-size" chunks and delivers each chunck to TCP/IP for
transmission.
What if SDU were 2048 and you wanted to send 4097 bytes (that's 2048 times two
plus 1)? Two chucks of 2048 bytes would be sent, with only one byte
being sent in the third chunk.
You can avoid such an undesireable situation by restricting every unit of
information to exactly 2048 bytes, but that doesn't work. If you allow
applications to set the sizes of their units based on logical, object-oriented
factors, then you must accept that those units will not be absolutely
optimized for transmission over a network.
It's good to try to maximize performance, but there are limits to how far you
can go. There's little you can do to improve the efficiency of SQL*Net.
2. What relationships exist between array_size parameter and SDU size,
MTU and Window size? Does SQL*Net write a full segment buffer (1460 bytes)
on Ethernet) to TCP, or does it write a buffer that is dependent on the
array size?
Applications accumulate "arraysize-number-of-row-worth" of data. If there are
varchar2 columns, and if those columns are not all full, then the number of
bytes of data will be something less than the maximum possible. If some
column values are null, that will also cause less data to be sent to SQL*Net.
SQL*Net puts the data that the application gives into "SDU-size" chunks.
These chunks are given to TCP/IP, which breaks the bytes in each chunk up into
packet-sized units for transmission. Neither the application nor SQL*Net can
tell TCP/IP to use a larger packet size for any particular transmission.
An example tnsnames.ora
UKHP55_V732.UK.ORACLE.COM =
(DESCRIPTION =
(SDU=8192)
(TDU=8192)
(ADDRESS =
.............
In the listener.ora in the SID_DESC clause, use:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 8192)
(TDU = 8192)
(SID_NAME = V7323)
.......
3. If an SQL select statement returns 3 records of 500 bytes each, how
will this be buffered into SQL*Net and written into TCP? Will it pass data
to the TCP, based on the (Ethernet) MTU?
If arraysize is one, then 500 bytes will go to SQL*Net and on to TCP/IP
followed by another 500 bytes to SQL*Net and TCP/IP followed by a third.
If arraysize is three, then 1500 bytes will be sent by the application to
SQL*Net. The SQL*Net default SDU of 2K will hold all of them in one unit.
The one unit will go to TCP/IP, which may well be able to send 1547 bytes of
user data in one packet. So, one packet will hold all the data.
If the arraysize for one particular session is four, between partially filled
columns and null columns, the data for one set of four rows may be of lengths
380, 391, 395 and 382 bytes respectively for a total of 1548 bytes.
The application (such as the Oracle server) will submit 1548 bytes
to SQL*Net for sending. This will easily fit in one SDU and be delivered as
one chunck to TCP/IP. TCP/IP will send the first 1547 bytes in the first
packet, and one byte in a second packet.
Given an uncontrolled amount of data that one might want to send, the last
packet that TCP/IP sends will be likely to be half full. To eliminate that
"inefficiency" you must impose unmanageable restrictions on all applications.
Avoiding this "waste" in networking would add immense inefficiency and
waste on applications and users.
4. Does increasing the array_size parameter provide a consistent increase
in performance? Should this be raised until the MTU size is met? What are
the negative consequences?
Array_size is often the easiest way to make orders-of-magnitude gains in
performance. There is, however, a point of diminishing returns. In general,
aim for an arraysize that would result in about 2k of data per request to
SQL*Net if all columns were full. You could aim for an arraysize of 4K, a
SQL*Net SDU of 4K and TCPIP packet size of 4K and maybe see some improvement.
5. What parameter(s) can be changed to maximize data transfer and reduce the
effect of high-latency WANs?
Array_size is the only parameter.
Tune your applications to avoid sending unnecessary rows through the
network. If you use database links to other Oracle databases, avoid cartesian
products (by having too few WHERE clauses). If you use Oracle Transparent
Gateways to non-Oracle databases, avoid functions that the database cannot
perform. If such a function is used, then the Gateway is likely to send a
large number of rows over to the driving Oracle database, where the function
will be applied. If a million rows are sent, but only one survives the
"WHERE" function filtering, that would appear to be very inefficient. (On the
other hand, if the Oracle function allows you to do something that there's no
other quicker way of doing, then it's as effecient at you can get.)
In a WAN (Wide Area Network) environment, you may have a variety of LAN (Local
Area Network) Topologies (4Mb token ring, 16Mb token ring, ethernet, fast
ethernet, FDDI, ATM, etc.), combined with various WAN Topologies (Frame Relay,
X.25, T-1, E-3, etc.) connecting them. All of these topologies will have
different PDU's (Protocol Data Units). For example, ethernet will have a PDU
of 1518 octets. Octets are 8 bits in size and is the "standard" unit of
measure for PDU's. The construction of an ethernet PDU is typically "header,"
"data," and "CRC." The data component can vary in size from 64-1500 octets.
Increase the size of the MTU:
In a WAN, there will be a minimum of two devices to handle the translation of
the LAN media to the WAN media. They will be at each end of the WAN media
and are either a Router or a Bridge. Gateways do not handle protocol traffic
so we will leave them out of this discussion). The WAN media can be, but is
not limited to:
* T-1
* Frame Relay
* X.25
The WAN is supplied by a PDN (Public Data Network). Each of these too, will
have a maximum PDU size associated with them. This PDU size is usually
referred to as MTU (Maximum Transmission Unit). This unit can be increased or
decreased depending on what you purchase from the PDN, and is also limited on
the technology they own. Purchasing the appropriate MTU for your application
and configuring your routers (or bridges) for that MTU will maximize the
"pipe" between the routers.
If, for instance, you are connecting two ethernet segments using X.25 as your
WAN media, the PDN you are purchasing the X.25 from supports data packet sizes
of 128, 256, 1024, and 2048 octets (excluding header and CRC), and their
default service is 128 octets of data, then the ethernet frame of 1500 octets
will have to be chopped up into 12 X.25 packets. If the application is
client/server based, with response time being a critical issue, it would be
beneficial to purchase a service with the 2048 octet X.25 packet. This
usually is an additional cost. If the application is based on telnet, then
the purchasing a large data packet will not increase response time much since
the data transferred is very small, and rather consistent).
Increase the ACK Time-out on the Server:
One other place to tune is the Server's protocol. There are always time-outs
for ACK's (acknowledgement packets). These time-outs are normally configured
for LAN performance and not WAN performance. Many times there are delays in
delivery of PDUs that would delay the client's ACKs. This will cause the
packet to be sent again until the server either gets the ACK back in time or
disconnects the clients connection. Telling the server to wait longer for the
ACKs will reduce the WAN traffic, thereby increasing response time.
Set only one Data Path on the WAN:
Many WAN configurations have several routes from the client to the
server, to create adequate redundancy. These routes will have different
speeds, due to different levels of traffic at different times of
day, or the actual speed of the link.
IP, the protocol that is actually on the wire, does not care which path the
PDU takes, nor does it care what order the PDUs arrive. With multiple paths
to it's destination, a TCP/IP "conversation" may use all the paths at once,
and the client will have to reassemble that "conversation." This will add to
the delay.
If a single path is configured, the possibility that PDU's will arrive at the
server or client out of order is reduced, if not eliminated. If redundancy
is required (more paths to the server in case one is not working), then
configure the backup paths with a higher "cost of service" (configured on the
bridge or router at the protocol level). This way, if the main path fails,
the clients will use the backup path, but not use it if the main path is
operational.
6. What is TCP.NODELAY?
From the TCP man page (Solaris):
"Under most circumstances, TCP sends data when it is presented. When
outstanding data has not yet been acknowledged, TCP gathers small amounts of
output to be sent in a single packet once an acknowledgement has been received.
For a small number of clients, such as window systems that send a stream of
mouse events which receive no replies, this packetization may cause significant
delays. To circumvent this problem, TCP provides a socket-level boolean option
TCP_NODELAY."
The tcp.nodelay parameter in the protocol.ora uses the TCP_NODELAY option on
the socket, and data is sent immediately. However, this could potentially send
more packets across the network.
It gives more consistent results because TCP/IP buffering and time outs do not
factor into the response times.
Aside from prefetching, this could also affect array fetching performance with
different array sizes