TCP or IPC - how to find out which got the connection ?

Norman Dunbar

unread,

May 17, 2001, 7:41:49 AM5/17/01

to

This might be a silly question, and networking is not one of my fortes,
but here goes :

On my HP box I have listener.ora with this setting :

LISTENER =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = ... )(PROTOCOL = TCP)(Host = ... )(Port
= ...) )
(ADDRESS=(PROTOCOL=IPC)(KEY= TEST.world) )
(ADDRESS=(PROTOCOL=IPC)(KEY= XXXX.world) )
(ADDRESS=(PROTOCOL=IPC)(KEY= YYYY.world) ) )

and then this :

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (SID_NAME = TEST) (ORACLE_HOME = ...) (PRESPAWN_MAX =
...))
(SID_DESC = (SID_NAME = XXXX) (ORACLE_HOME = ...) (PRESPAWN_MAX =
...))
(SID_DESC = (SID_NAME = YYYY) (ORACLE_HOME = ...) (PRESPAWN_MAX =
...))
)

And in my tnsnames, also on the HP box, I have this :

TEST.world =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = ...) (PROTOCOL
= TCP) (Host = ...) (Port = ...) ) )
(CONNECT_DATA = (SID = TEST) )
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = ...) (PROTOCOL
= TCP) (Host = ...) (Port = ...) ) )
(CONNECT_DATA = (SID = XXXX) )
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = ...) (PROTOCOL
= TCP) (Host = ...) (Port = ...) ) )
(CONNECT_DATA = (SID = YYYY) )
)

So the listener says IPC and the tnsnames says TCP for the protocol.

If I tnsping the sid from the hp box, I get a result which says that it
used TCP. as in :

tnsping TEST
Attempting to contact
(ADDRESS=(COMMUNITY=...)(PROTOCOL=TCP)(Host=...)(Port=...))
OK (80 msec)

This looks like the data from the tnsnames.ora file.

If I change the tnsnames.ora file to use IPC protocol and add the KEY
parameter the tnsping uses IPC as follows :

Attempting to contact (ADDRESS=(COMMUNITY=...)(PROTOCOL=IPC)(KEY=TEST))

OK (50 msec)

And it takes less time as well :o)

Here come the questions :

Should I be using IPC in the tnsnames.ora file to stop the 'traffic'
hitting the network card and coming straight back in ?

Is it true that a connection as in 'sqlplus user/password@TEST' will use
tnsnames and the protocol defined there while a connection of the form
'sqlplus user/password' will not use tnsnames.ora (ORACLE_SID defaulted
to TEST) but will go 'straight there' using IPC as defined in
listener.ora ?

Assuming that the SQLNet traffic *is* hitting the network when TCP is in
use, am I likely to see a serious degradation in performance over that
when ICP is in use. I know that 30 Milli seconds in a tnsping doesn't
amount to much, but if there is a lot of traffic .....

I welcome and abuse/answers/explanations from anyone more knowlegable
than I am - which is probably most of you out there - SQLNet is a tad
confusing and I feel that I need to know it better.
I might point out that I inherited this setup and it has worked fine for
all the time I've had it - I just want to know more about it and the
NET80 admin manual is not giving me what I want. If I missed the chapter
then please feel free to tell me to RTFM again :o)

Thanks in advance,

Kind regards,
Norman.

------------------------------------------------------------------------
--------
Norman Dunbar EMail: NDu...@LynxFinancialSystems.co.uk
Database/Unix administrator Phone: 0113 289 6265
Lynx Financial Systems Ltd. Fax: 0113 201 7265
URL: http://www.LynxFinancialSystems.com
------------------------------------------------------------------------
--------

Brian Peasland

unread,

May 17, 2001, 9:14:34 AM5/17/01

to

> Should I be using IPC in the tnsnames.ora file to stop the 'traffic'
> hitting the network card and coming straight back in ?

IPC will perform faster because you have two processes, local to the
server, talking to each other. In fact, IPC stands for Inter Process
Communication. TCP is slightly slower because it has to hit your NIC
card and come back. But you can't use IPC if your client and your
database are on two different machines. If speed is an issue for you,
then use IPC. But for most client applications, your network speed is
not the issue so TCP works just fine.

> Is it true that a connection as in 'sqlplus user/password@TEST' will use
> tnsnames and the protocol defined there while a connection of the form
> 'sqlplus user/password' will not use tnsnames.ora (ORACLE_SID defaulted
> to TEST) but will go 'straight there' using IPC as defined in
> listener.ora ?

If you supply the database name, as in '@TEST', then it will resolve the
name (normally through TNSNAMES.ORA0 and use whatever protocol is
defined there. If you do not include the '@TEST', then is looks at your
ORACLE_SID. From there, it uses the Bequeath (BEQ) protocol. This is
slightly different than IPC. To verify this, make your connection than
issue "ps -ef | grep beq". I'm assuming you are on Unix here. If you
rely on your ORACLE_SID for determining which database to connect to,
then name resolution does not take place and your TNSNAMES.ORA file is
not used.

There is a subtle difference between BEQ and IPC. IPC is for your client
process talking to your Oracle dedicated server process. BEQ is "more
native" to Oracle software (such as SQL*Plus). Your SQL*Plus session is
"bequeathed" to the dedicated process.

> Assuming that the SQLNet traffic *is* hitting the network when TCP is in
> use, am I likely to see a serious degradation in performance over that
> when ICP is in use. I know that 30 Milli seconds in a tnsping doesn't
> amount to much, but if there is a lot of traffic .....

If there is a lot of net traffic and you can use IPC, then yes, this can
lead to performance improvements. But these improvements will likely be
slight compared to the improvements one can get from well formed SQL
statements.

HTH,
Brian

--
========================================
Brian Peasland
Raytheons Systems at
USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my
company!
========================================

Norman Dunbar

unread,

May 17, 2001, 10:45:12 AM5/17/01

to

Brian,

much obliged to you. I have carried out a couple of tests and found that
bequeath is indeed used when the sid is omitted. For TCP and IPC all I
get on the connection is (LOCAL=NO).
One for the 'handy stuff to know' folder on my desktop.

I wasn't after performance gains as such from this, just a better
understanding of what goes on.

Thanks again.

Regards, Norman.

------------------------------------------------------------------------
--------
Norman Dunbar EMail: NDu...@LynxFinancialSystems.co.uk
Database/Unix administrator Phone: 0113 289 6265
Lynx Financial Systems Ltd. Fax: 0113 201 7265
URL: http://www.LynxFinancialSystems.com
------------------------------------------------------------------------
--------

-----Original Message-----
From: ne...@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739)
[mailto:ne...@igsrsparc2.er.usgs.gov]On Behalf Of Brian Peasland
Posted At: Thursday, May 17, 2001 2:15 PM
Posted To: server
Conversation: TCP or IPC - how to find out which got the connection ?
Subject: Re: TCP or IPC - how to find out which got the connection ?

HTH,
Brian