Oracle Listener and multi-homing query

[KapilRaj]

thanks for that .. If I have to prove a point in front of my change management , I should have some knowledge of what I am saying. Hence please explain me how & why it is better than TCP here ?

Also note that I can not go for a change in the way I connect as it will require a lot of batch script changes , due to which my Change Request will be held for years :(


Regds,

Kaps

[Jean-Luc Oudart]

TCP has an overhead compare to IPC connection. Therefore use BEQ protocol whenever you can.
Obsioulsy for batch processing this is better

Regards,
Jean-Luc

[Volker Borowski]

Hi,

I just like to put in another aspect.
If you have long or short transactions does not indicate, if they have need for network traffic.

I.e. DROP TABLE will do the same speed on Gbit or 9600Baud. It depends on how much data is transfered. So if you have big batches, that use statements, which can be processed at a high percentage on the server, or may be you mostly execute stored procedures, I think you will see slightly diffrences.

If you have large cursors (with a lot of fetches) in your application, that select a big number of rows and need to manipulate them, you will have network traffic which of course will benefit from a faster connection.

IPC vs. TCP: If you connect via IPC, the application uses direct memory communication, while TCP, even if on the same host, will go through the network layer.
If you need to connect through tcp, even if you are on the same host, consider to connect via loopback interface (always up, even if network switch is down), but I would go for IPC connection (saves a layer in data-transmission).

In general you should go for the faster card.
Volker

[KapilRaj]

Volker,

Now we are in the right direction ... To change my connections to BEQ , I am not sure how much time and efforts that would require hence keep it apart.
Now I want to go with it the TCP solution. As you know I do not have a listener on loopback interface.

What would you suggest ?

Use the listener on 10 Mbps interface ,100 Mbps or 1000 Mbps After all does it make any diffrence ?.

For the time take it like my batch processing is huge.

Regds,

Kaps

[Stephen Andreassend · Accepted solution]

Changing the selected network to 100/1000, or using BEQ/IPC connections is good practice, but you may not find the significant performance gain you are looking for.

There is some tuning you can do if you continue to use TCP/IP.

The automatic_ipc parameter of the sqlnet.ora file bypasses the network layer, thereby speeding local connections to the database. When automatic_ipc=on, Oracle Net checks to see if a local database is defined by the same alias. If so, network layers are bypassed as the connection is translated directly to the local IPC connections.

The automatic_ipc parameter should be used only on the database server when an Oracle Net connection must be made to the local database.

If your server is in MTS shared server mode, ensure that your batch jobs get a dedicated server process by setting the (SERVER=DEDICATED) parameter in the tnsnames.ora file entry too. If your batch jobs get a shared server process then your performance can be bad.

If you are using SQL*Plus and retrieving data in your batch processing apps, then consider the "array size" parameter:

SQL> set array_size value

This determines the number of rows (called a batch) that SQL*Plus fetches from the database at one time. The value parameter can range from 1 to 5000, and defaults to 15. A large value increases the efficiency of queries that fetch many rows, but requires more host memory.

If the array size is set to a higher value, the application passes a large amount of data to SQL*Net. The SQL*Net buffer size determines the amount of data that can be processed by SQL*Net. The session data unit (SDU) parameter defines the SQL*Net buffer and can be set in both the tnsnames.ora and listener.ora. If set then it should be set to the same value in both. It defaults to 2048 bytes, and can as big is 32767 bytes.

The transport date unit (TDU) parameter is also located in the tnsnames.ora and listener.ora files. The TDU is the default packet size used within Oracle Net to group data together. The TDU parameter should ideally be a multiple of the SDU parameter. The default value for TDU is 2048, and the maximum value is 32767 bytes. The SDU should never be set larger than the TDU.

If the SDU parameter is smaller than the array size, fragmentation could occur.
If SDU is larger than the array size, there is no fragmentation, and the entire packet can be sent across the network. This is better for performance.

The maximum transfer unit (MTU) defines the buffer size of TCP/IP (1500 bytes) using ethernet. Different network topologies have different MTU sizes, eg token ring 16 Mbit/sec has 17914.
SDU = MTU is the ideal situation as no fragmentation occurs.
SDU > MTU fragmentation occurs.
SDU < MTU performance does not increase.

For optimal network performance in the case of ethernet, consider setting SDU = TDU = MTU = 1500 bytes, and try to ensure that your (array size * rowsize) is just a bit less than 1500 bytes as there is some overhead that needs to be accounted for.

Finally, since your apps are on the same machine as the db server, and you are using TCP, then you could bypass TCP buffering. This can bring some performance benefits. The protocol.ora file can be specified to indicate no data buffering for all TCP/IP implementations. The parameter can be used on both the client and server. The protocol.ora statement is:

tcp.nodelay = yes

If you have other client machines doing OLTP type work, then those client machines should have tcp.nodelay = no.

Setting this parameter to no causes TCP buffering to be skipped so that every request is sent immediately.

Setting tcp.nodelay can give a huge improvement in performance when there is high-volume traffic.

[Volker Borowski]

Hi,

to use loopback, simply ensure it is correctly configured ( ping 127.0.0.1 ).

Then build a new Listener to use IP 127.0.0.1 and connect to this one. (Will be possible only from the local machine).

Volker

[KapilRaj]

Thanks you all that has given me little more knowledge on oracle and sql ..

Coming back to my question ..

Is there any advantage of changing my tns to use a better interface (in terms of speed) if batch and database are on the same node ?.

Regds,

Kaps

[Jean-Luc Oudart]

If you change your listener.ora entry to use BEQ , i don't think you will require the listener to be up.

tnsnames.ora (extract) :
dev4beq =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = BEQ)(PROGRAM = oracle)
(ARGV0 = oracledev4)
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
)
(CONNECT_DATA = (SID = dev4))
)

Regards,
Jean-Luc

[Alzhy]

This thread caught me interest as we're somewhat in the same boat -- we've servers that have no Database and Apps (online/batch/mid-ware) separation. The only difference is the Database also need to be accessed by remote apps and clients.

So:

Is it possible for the Database to serve transactions BEQ for the local/co-located apps and TCP (via listener) for the rest?

Also is it possible to have multiple listeners if our servers are multi-homed -- meaning -- multiple Gigabit NICs to serve various LAN client apps?


TIA.

And KapilRaj (if yer still in the bizness) -- did you try BEQ or ended up having your listener use the gigabit local NIC -- which IMHO should improve things assuming the DB transactions pass a lot of data on the network pipe?)