Newsgroup: it.comp.software.database
Titolo: utilizzo banda oracle

Sapete mica qual e' la dimensione media di un pacchetto di dati di
NET8?
L'unico dato che riesco a vedere da server e' il numero di pacchetti
al secondo ma dovrei risalire alla banda minima richiesta per
potenziare la rete.
Greetings,
Alessandro Trebbi
Pesaro, Italy
http://www.b3soft.com

Ciao Alessandro,
per analizzare la dimensione dei pacchetti credo che ti serva per forza
un analizzatore di rete, da posizionare tra il server Oracle ed un
client in modo da sentire il traffico e la banda occupata.
Su metalink, non vi sono molte cose ma questo credo che ti possa aiutare:
Subject: 	Tuning SQL*Net for better performance
Problem Description:
====================
How can I tune SQL*Net?
Search words:
sqlnet improve performance tuning improving
sdu packet buffer size
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
.
	Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
Notices and Terms of Use. 	
A presto,
Simone
"I'm a travelling man, I'm a drinking man, I'm a gentle when I'm sober"

 being sent in the third chunk.
Ho un piccolo dubbio: non ho toccato l'SDU quindi dovrei avere i
default, solo che il server e' 8.1.7 mentre il client e' 7.3.4 ed ho
paura che siano diversi.
Sto utilizzando spotlight per monitorare la situazione, ho il numero
di pacchetti al secondo nelle informazioni del OS nonche' i wait del
database suddivisi per tipologia (dovrebbe essere il contenuto di
V$SESSION_EVENT (AVERAGE_WAIT) e V$SESSION_WAIT (EVENT)), ma non so
come risalire alla banda; tra l'altro non so neanche come possa essere
utile come informazione visto che la rete e' utilizzata per tante
altre cose, ma questo e' quanto mi e' stato richiesto...


 altre cose, ma questo e' quanto mi e' stato richiesto...
per le performances pure:
ftp://oracle-ftp.oracle.com/apps/patchsets/AOL/SCRIPTS/PERFORMANCE/TRCA.zip
(mi sembra sia relativamente nuovo come tool)
per la rete il top sarebbe settare ad un certo livello il tracing nel 
sqlnet.ora.
a quel punto *potresti* usare il trace assistant fornito con oracle per 
capirci qualcosa. dico *potresti* perché come tutti i tool minori di 
oracle non funziona bene e ci sono operazioni e bug reports da leggersi 
sul metalink per cavare un ragno dal buco.
ciao
-- 
punkarruso

 prevalga quello del server.
mi sa che come stima do l'SDU size del server per il numero di
pacchetti al secondo (che e' in bytes, quindi per 8 per avere i
bits...), e' un po' approssimata per eccesso ma in linea di massima mi
sembra che sia abbastanza attendibile


 sembra che sia abbastanza attendibile
occhio. può darsi che sia così ma da come avevo capito io a suo tempo 
l'sdu è la quantità di dato "reale" che al massimo viene inviata in un 
pacchetto. non so quanto questa coincida con le dimensioni del pacchetto 
richieste dal protocollo net8 vero e proprio.
comunque, se vedi che anche "a occhio" il traffico è maggiore di quanto 
ti aspettavi, entrano in gioco tantissimi punti su cui limare. non sto a 
dirteli, ne saprai più di me! (bind vars, riciclo connessioni ecc.)
ciao
-- 
punkarruso