Oracle Listener and multi-homing query

[KapilRaj]

Guys,

I have a multihomed Node which currently hosts the Database as well as runs it's batch processing.
There are three listeners configured on 3 diffrent adapters.

ETH-1 10 Mbps listener_a
ETH-2 100 Mbps listener_b
ETH-3 1000 Mbps listener_c

The batch processing jobs use TNS to connect to the database. And they are currently using listener_a i.e. the 10 Mbps one.

Is there any advantage of making a tnsnames.ora change so that connections are going through listener_c i.e. 1000 Mbps ?.

If I do this, Am I gonna get a better response for oracle sessions ?.

Regds,

Kaps

[Joseph Loo]

hi,

What is the network backbone, i.e. gigabits at your environment and the network point you are connecting for the gigabits card?

If the network point is on gigabit, the throughput would most definitely be higher than what the 10Mbps could reach. However if your network point is a 10Mbps, the gigabit card would only reach that much.

As for Oracle sessions, there are too many varied on better response. Establishing faster oracle connections may also be set at the listener.ora, STARTUP_WAIT_TIME_ = 0 among others.

hope that explains.

regards.

[Rita C Workman]

Well I'm no Oracle guru, so hopefully some Oracle whiz will correct me if I err-too far.

We run Oracle, and we only have 1 listener running last time I checked. We run the big batch jobs at night and only do small things during the day so as to leave the major resources to the end user (we're a biger on read only/querries).

With that said...I guess the obvious is what comes to mind....will you get better response on a 1000Mb connection from a 10Mb connection ?..Yeah you should! Otherwise we'd all still be using 10Mb cards. As long as your ports & lan cards are configured accordingly...Yeah...

Rgrds,
Rita

[Joseph Loo]

rita, guess you say the magic word, 'big' which is why it should be run during a lull (non-peak) period. if the batch job gets too 'big', there may be a need to re-look the script and see if any improvement may be make to tune the statement/s or whether the table structure need modification, i.e. indexes.

regards.

[KapilRaj]

Joseph Loo,
We have diffrent Networks for diff functions,

1. The internal N/W which runs at 10Mbps we use for node-node system check scripts etc.
2. External N/W runs at 100 Mbps used for online access for users.
3. Backend N/W == 1000 Mbps, we use for oracle connections , NFS mounts etc.

If the batch processing and database was running on diffrent nodes, I would have choosen the eth-3 which gives me a better speed, but here every thing is on the same node. And I have a doubt.

Rita,

Nice to see you after a long time (?!). I can understand ur statement 10 Mbps would get a better response but I have batch processing and database on the same node !.

Is it that a TCP packet size depends on the interface speed ?. (Wild guess)

Mates ... I need more info !

Regds,

Kaps

[Jean-Luc Oudart]

Kaps,

if your batch processing and database are on same node you should use the BEQ protocol and bypass the TCP overhead.

Regards,
Jean-Luc

[KapilRaj]

Is it diffrent from tns ?. If yes , I can not go for it as my standards says I should always use tns !

Regds,

Kaps

[Nicolas Dumeige]

Same tnsnames.ora just change the PROTOCOL.

Cheers

Nicolas

[KapilRaj]

Can u explain why and how it is better than TCP in my scenario ?

Kaps

[Jean-Luc Oudart]

Kaps

your listener.ora should look like :
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = ))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/8.1.7)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = )
(ORACLE_HOME = /oracle/product/8.1.7)
)
)

the connection should be done as connect user/passwd and not user/passwd@

unset TWO_TASK variable if set

Regards,
Jean-Luc

[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?)