DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Net8 arraysize

  1. #1
    Join Date
    Sep 2000
    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 ..

  2. #2
    Join Date
    Mar 2001
    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

    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
    (ADDRESS =
    In the listener.ora in the SID_DESC clause, use:
    (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

    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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width