Skip to Main Content
  • Questions
  • tnsnames.ora TCP vs. IPC for DB on local host

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Matt.

Asked: October 24, 2002 - 2:18 pm UTC

Answered by: Tom Kyte - Last updated: July 13, 2005 - 3:02 pm UTC

Category: Database - Version: 8.1.5 and 8.1.7

Viewed 10K+ times! This question is

You Asked

We run an app server (Vantive) which makes OCI calls against a database located on the same host. The database administrators setup tnsnames.ora to connect to that database with (PROTOCOL=TCP). Isn't that much slower than (PROTOCOL=IPC) because each request and response actually gets sent to the NIC? They say it would make only a miniscule difference in performance. In my research I can't find a conclusive answer whether this is correct. Performance is just OK - I'm just looking for the "low-hanging fruit" for improvements and I really should test this but I thought I'd get your feedback also.

Configuration:
Oracle 8.1.5 and 8.1.7
HP-UX 11.00

Does the following describe the behavior that (PROTOCOL=TCP) would have for connections initiated from the same host?

1. Application server makes request on Net8
2. Net8 sends request to interface
3. interface sends request to hub/switch
4. hub/switch sends request back to same interface
5. interface sends request to dedicated server proc
6. statement processed
7. dedicated server sends response to net8
8. net8 sends response to interface
9. interface sends response ot hub
10. hub sends response to same interface
11. interface sends response to application server

Thanks Tom... Also: this is best technical site on the net.

Matt Butler


and we said...

well, tuning TCP vs IPC would give you marginal improvements.

Overhauling data structures, re-architecting application, finding and fixing poorly performing SQL -- massive payback.

(tuning tcp vs ipc isn't "low hanging fruit" material. Its fine tuning after all other avenues have been exhausted).

I've seen tcp be faster then ipc and vice versa. varies by OS, port, settings and so on.


I'm not a networking expert -- there is probably one out there -- but I do believe that some OS's will recognize that the network data doesn't need to hit the network. I'm sure if I'm wrong -- someone will say something ;) (i'm sort of curious myself -- hoping someone follows up)

But -- you know -- it would be easy to benchmark. Just setup a small test with a tcp and ipc based listener. open two sqlplus sessions, one tcp, one ipc and

set termout off
select * from all_objects;
set termout on
select to_char(sysdate,'dd-mon hh24:mi:ss' ) from dual;
set termout off
select * from all_objects;
/
/
/
/
set termout on
select to_char(sysdate,'dd-mon hh24:mi:ss' ) from dual;

and see which one appears to be "much faster"



and you rated our response

  (21 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

On most versions of UNIX, at least...

October 24, 2002 - 5:58 pm UTC

Reviewer: Jeremy Smith from Salt Lake City, UT

The network stack realizes when a packet needs to be routed to the localhost/loopback and doesn't go so far as to actually send it to the network interface. Otherwise, you couldn't run any services on a machine with no network card. It does have to touch the tcp/ip code so there might be a tiny bit of delay there...but otherwise, it's pretty much the equivalent of using IPC's.

Easy to prove that it doesn't hit the network Solaris or Linux if you have root: use tcpdump or snoop and see if you see any traffic there when you make a local connection.

If you really want to see how it works, check out the linux source.

Sorry, didn't mean to give it two stars!

October 24, 2002 - 5:59 pm UTC

Reviewer: Jeremy Smith from Salt Lake City, UT

Nothing you ever write is only worth two stars, Tom. :)

Test results

October 24, 2002 - 8:46 pm UTC

Reviewer: Matt Butler from Roseville, CA

Here are the test results for your example:

USING TCP:

system@vwrld817>@asktom

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:32:11


TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:32:23

system@vwrld817>@asktom

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:33:33


TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:33:45

system@vwrld817>@asktom

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:33:52


TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:34:05


A consistent 12 seconds!!!


Now for IPC:

connect system@vwrld817ipc
Enter password:
Connected.

system@vwrld817>@asktom

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:36:35

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:36:50
system@vwrld817>@asktom

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:37:00

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:37:15
system@vwrld817>@asktom

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:37:26

TO_CHAR(SYSDATE,'DD-M
---------------------
24-oct 17:37:41
system@vwrld817>


A CONSISTENT 15 SECONDS!!!!

IPC is SLOWER!!!

I cannot believe it!

My listener setup:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = foo.bar.agilent.com)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = foo.bar.agilent.com)(PORT = 2481))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = vwrld817))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
)
[....]

My tnsnames.ora:

[....]
vwrld817 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = foo.bar.agilent.com)
(Port = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = vwrld817)
)
)
vwrld817ipc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY=vwrld817)
)
)
(CONNECT_DATA = (SERVICE_NAME = vwrld817)
)
)
[...]

This flys in the face of all logic - would expect IPC to be faster or at least the same.

Well thanks Tom!!!!



How about the context switching from Pro*C / PLSQL

October 25, 2002 - 9:31 am UTC

Reviewer: Dev from USA

We've a PRO*C program which calls a packaged function around 1.2 mill times. The driving cursor pulls 1.2 mill records and calls the packaged function.

The package has around 25 in/out parameter of number data type, Pro*C calls them with double data type.

I guess the context switching happens lot

When we watch the program while it's running most of waits or SQL*Message to Client.

It's 8.1.7 and the pro*C program and oracle Database resides on the same unix box.

Please advice if this can be related to TCP protocol and there is work around to reduce the waits.

Thanks.


Tom Kyte

Followup  

October 26, 2002 - 11:44 am UTC

why not do this.....

put the driving cursor into plsql and move the code entirely into the database -- that'll make this take one round trip...

Bequeath?

October 25, 2002 - 4:16 pm UTC

Reviewer: A reader

If the client and the database are on the same machine, why not use bequeath?

Very interesting

October 25, 2002 - 4:50 pm UTC

Reviewer: andrew from ca, usa


Benchmarked Bequeath - no help

October 25, 2002 - 5:56 pm UTC

Reviewer: Matt Butler

FYI
I also benchamarked Bequeath and it averages 13 seconds

Tom Kyte

Followup  

October 26, 2002 - 12:35 pm UTC

they (bequeath and IPC) both use IPC. it varies from OS to OS and OS release to OS release as to which will be faster.


Case in point (that things change). I have a small demo I run. It shows the effects of using table compression in 9iR2. It had been taking 31 seconds to full scan the uncompressed table and 4 seconds to full scan the compressed on my laptop running redhat 7.3 -- like clockwork. In fact, I would kick off the demo and had a little "talk" I would be giving while it ran.

I upgraded to redhat 8.0 and did the demo. It took 7 seconds and 1 second. (same ratio -- about 7ish to 1, just way faster). Threw me off tho, it never ran like that. I attributed to "i must have run it before to test and it was in the OS file cache). So, next time I rebooted -- still 7:1 not 32:4 as it had always been.

Turns out -- redhat 8.0 was just that much faster working with Oracle 9iR2 then 7.3 was. Everything changed due to that OS upgrade!

8.0 not faster

December 30, 2002 - 9:07 pm UTC

Reviewer: A reader

I seriously doubt whether your quicker times had anything to do with your Red Hat upgrade. Networking is no different and kernel is close to that in 7.2. The OS was enhanced though the periferials.

Tom Kyte

Followup  

December 31, 2002 - 8:37 am UTC

Umm -- it was all about disk performance and YES, in fact it was due to the upgrade thank you very much.

It had nothing to do with network performance -- it was all about a full scan and the amount of data moving in from disk to buffer cache.



what're all the other options

December 31, 2002 - 10:34 am UTC

Reviewer: A reader

>
>Followup:
>why not do this.....

>put the driving cursor into plsql and move the code >entirely into the database
>-- that'll make this take one round trip...

The program does so many calculation and it's not easy to migrate that logic to PL/SQL. It's the vendor code and not the custom code. Our DBA saying to me that changing to IPC is a global change (not even agreeing for doing it in test box)

So is there any other option?


Tom Kyte

Followup  

December 31, 2002 - 11:59 am UTC

what does IPC have to do with this?

anyway -- you have an algorithmic problem. Look to your algorithm to fix it, not to some magic switch.

My advice to them was -- get the code OFF the network, put it in the database. Don't pull all of the data OUT just to put it back IN.


If you have vendor supplied code, you are stuck, you get what you got. there is not much you can do about it other then ask them "hey, why did you do it wrong?"



December 31, 2002 - 1:24 pm UTC

Reviewer: A reader

I guess I've to repeat my question

We've a PRO*C program which calls a packaged function around 1.2 mill times.
The driving cursor pulls 1.2 mill records and calls the packaged function.

The package has around 25 in/out parameter of number data type, Pro*C calls them
with double data type.

I guess the context switching happens lot

When we watch the program while it's running most of waits or SQL*Message to
Client.

It's 8.1.7 and the pro*C program and oracle Database resides on the same unix
box.

Please advice if this can be related to TCP protocol and there is work around to
reduce the waits.

Thanks.


Tom Kyte

Followup  

December 31, 2002 - 1:31 pm UTC

remember -- after all you are just "a reader" -- there is no way for me to see what reader, just a reader

I told you what I would do. Maybe I'll repeat myself (with a cut and paste)

put the driving cursor into plsql and move the code entirely into the database
-- that'll make this take one round trip...


but you already said "no can do"...



do you use array fetching? if not, do so.

calling anything 1.2 million times is going to take quite a bit of time. Parallelize your operation -- run 15 copies of your pro*c app, each working on a different bit of the table.


"When we watch the program while it's running most of waits or SQL*Message to
Client."

does not make sense unless you meant "most waits are 'SQL*Net Message to client'" -- but that is an idle wait (normal)...

if you are on the same unix box, why are you using net at all? Just user/pass -- no connect string.





Very informative response, Tom

December 31, 2002 - 2:56 pm UTC

Reviewer: Jay Hart


That's what I guess I was missing

December 31, 2002 - 4:58 pm UTC

Reviewer: A reader

I'll run the program without the connect string and let you know.

No listener, got connect with the connect string

May 08, 2003 - 12:17 am UTC

Reviewer: A reader

On the the server, I stopped the listener as
lsnrctl stop

I verified it was indeed stopped.

Then on the same server I used:

conn scott/tiger@xxx

Got connected, Why? (The connect string and The listener was down.)

I recall there may be configured for no need the listener. Is it the case here?

Thanks.
Sean

Tom Kyte

Followup  

May 08, 2003 - 9:34 am UTC

you were using a different listener. really.

TCP is fast enought.

May 08, 2003 - 5:30 pm UTC

Reviewer: Leonardo Zacché from RJ/Brazil

In fact, if you point to a host named localhost (or 127.0.0.1, if you prefer) your packet doesn't reach the network. The NIC sends it back to you.

Try this:
set up a machine to use DHCP and boot it up, but with NO network cable attached. This machine will not receive any IP number. If you ping any address (valid or not) it'll fail. BUT if you ping localhost (or 127.0.0.1) you will get.
And even Oracle will listen to and respond to your application server.

Even if you use your ip address (fixed or given by a DHCP server) instead of localhost your package will not "go for a ride" on the network. Your NIC is smart enought to prevent it.

In fact, 127.0.0.1 and localhost are equivalent, and both "reserved TCP/IP words". You cannot set a machine this IP and expect it to be found by anyone on your network.

Hope this help.

LZ

ps: Tom, why the "spell checker" doesn't recognize over 90% of my words?

Bequeath Connection = IPC

June 18, 2003 - 6:43 pm UTC

Reviewer: Robert from PA

Tom,
Is the the "old" Bequeath Connection now called IPC ?
Bequeath connection can be established without the listener running, right ?
I can't verify this right now, but is IPC also listener-independent ?
Thanks

Tom Kyte

Followup  

June 19, 2003 - 7:53 am UTC

$ sqlplus /

ops$tkyte@ORA920LAP> @getspid

DEDICATED_SE CLIENTPID
------------ ------------
3526         3525

ops$tkyte@ORA920LAP> !ps -auxww | grep 3526
ora920    3526  0.2  2.8 232876 14756 ?      S    07:51   0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))


bequeath is bequeath, IPC is instead of TCP (network)

bequeath uses ipc under the covers (that is just interprocess communication). we can use it with or without using a listener to connect 

BEQUEATH

June 19, 2003 - 12:31 pm UTC

Reviewer: Robert from PA

>ops$tkyte@ORA920LAP> !ps -auxww | grep 3526
>ora920 3526 0.2 2.8 232876 14756 ? S 07:51 >0:00 oracleora920
>(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

I could swear Bequeath was presented as one of the selections in net configuration (Windoz GUI).

But i don't see it now on my 9iR2/Linux installation -- only TCP, TCPS, IPC. Obviously it's still there.
Was it actually removed from the GUI list ? can you confirm this , Tom.
Thanks


Tom Kyte

Followup  

June 19, 2003 - 1:20 pm UTC

nope, no windoze to look at, only thru

Outstanding!

June 20, 2003 - 12:22 am UTC

Reviewer: Wayne Zhu from Philadelphia, PA

I had some question some times ago that I thought (IPC or BEQ) is "faster" than TCP, but, after tests, it was found that there were basically no obvious difference using IPC or BEQ vs TCP (Note: the tests were done for a whole application, in which, for example, order processing measured by order lines per hour, on different Unix flavors). Your anwser and follow-ups are surprisingly accurate to me and more importantly very constructive. Well done, Tom, and Thanks.

local vs TCP

November 03, 2003 - 1:56 pm UTC

Reviewer: Zoran Martic from Dublin, Ireland

Hi all,

Just to share a few things I tested in the past on Solaris and HP platforms.

Just to start first with the standard way of measuring the improvement here:

Overall time = Client time + network time + server time

When you are not using array processing (both bulk DML and prefetching in SELECT) and your applcation is local to the database server you should consider using BEQ or IPC.
If you cannot move your application to use array/bulk operations consider using BEQ or IPC network connection
, of course only, if the network time here is the big percentage of overall time or you want to increase the scalability of overall system.

Now the scenario to network time to be very big is always when you are doing very fast SQL so many times per second in a loop like simple insert/update/select from dual/....

We had the situation to use simple INSERT's a lot and not have the possibility to change it to be bulk insert.
The application was on the same machine as Oracle server. We just switch the application to use BEQ and we have got a very nice improvement in overall speed of 30%.
We have also tested on other ocassions BEQ/IPC vs TCP and it was faster in many cases.

It is very normal that BEQ/IPC to be faster then TCP because of the complexity build arround TCP but not always true.
But at the end it is all about your platform and your test.
We are now using on all our sites BEQ if possible, if not IPC (usually when the applciation is 32 bit and Oracle is 64 bit) and TCP when the application is on a different server. At the end this is generic approach and should be tested.

Hope I help something.



Connection from SQL*Plus hanging

July 13, 2005 - 11:36 am UTC

Reviewer: Developer from USA

I can connect via sqlplus as sys ( sysdba ) with no problem session is started very quickly.

But when I use a particular account it just hangs.
I checked bdump and udump and there is nothing funny.

I checked the accounts default table space and it is fine as far as the datafiles ( autoextend is on and unlimited ).

I am on 9.2.0.6 on Win2k pro and the DB server is local.
Is there a way I can trace this connect attempt?


Tom Kyte

Followup  

July 13, 2005 - 1:00 pm UTC

what is in your sqlnet.ora

interesting

July 13, 2005 - 1:35 pm UTC

Reviewer: DEVELOPER from USA

..... under c:\oracle\ora92 I have 1 sqlnet.ora and it is the SAMPLE in the \network\admin\sample folder.

So I would venture to say that is not good.

I have the below info ( i am trying to log in as SAMS user )
it shows even though there is no active sqlplus session as SAMS that a SAMS session exists ( which is probably preventing me from starting another session ??? )

SQL> @c:\sqlfiles\showsql

USERNAME                              SID    SERIAL# PROCESS      STATUS
------------------------------ ---------- ---------- ------------ ----------
SAMS                                   10         12 2404:1092    ACTIVE
SYS                                    11         12 3072:2500    ACTIVE
--------------------
SYS(11,12) ospid = 3072:2500 program = sqlplus.exe
Wednesday 12:15  Wednesday 12:58 last et = 0
SELECT USERNAME||'('||SID||','||SERIAL#|| ') ospid = ' || PROCESS || ' program =
' || PROGRAM USERNAME, TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,
TO_CHAR(SYSDATE,' Day HH24:MI') CURRENT_TIME, SQL_ADDRESS, LAST_CALL_ET FROM
V$SESSION WHERE STATUS
--------------------
SAMS(10,12) ospid = 2404:1092 program = sqlplus.exe
Tuesday   14:10  Wednesday 12:58 last et = 80230
insert into ut_MM_AMMAL_ITEM_MASTER select * from MM_AMMAL_ITEM_MASTER

USERNAME        MODULE          ACTION          CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SAMS(10,12)     SQL*Plus
SYS(11,12)      sqlplus.exe
SQL> 

Tom Kyte

Followup  

July 13, 2005 - 2:32 pm UTC

(that has been running that insert for over 22 hours!!!!)

are you connecting via SHARED SERVER? and if so, how many shared servers do you have configured, if just "one", then yes, the existing connection would do that (block you out)

select username, server from v$session

not shared...

July 13, 2005 - 2:43 pm UTC

Reviewer: developer from USA

....im not using MTS here is the results from SQL

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> @c:\sqlfiles\showsql

USERNAME                              SID    SERIAL# PROCESS      STATUS
------------------------------ ---------- ---------- ------------ ----------
SAMS                                   10         12 2404:1092    KILLED
SYS                                    11         16 2904:3204    ACTIVE
--------------------
SYS(11,16) ospid = 2904:3204 program = sqlplus.exe
Wednesday 13:46  Wednesday 13:46 last et = 0
SELECT USERNAME||'('||SID||','||SERIAL#|| ') ospid = ' || PROCESS || ' program =
' || PROGRAM USERNAME, TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,
TO_CHAR(SYSDATE,' Day HH24:MI') CURRENT_TIME, SQL_ADDRESS, LAST_CALL_ET FROM
V$SESSION WHERE STATUS

USERNAME        MODULE          ACTION          CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SAMS(10,12)     SQL*Plus
SYS(11,16)      sqlplus.exe
SQL> select username, server from v$session;

USERNAME        SERVER
--------------- ---------
                DEDICATED
                DEDICATED
                DEDICATED
                DEDICATED
                DEDICATED
                DEDICATED
                DEDICATED
SAMS            DEDICATED
SYS             DEDICATED
SQL>

I tried to kill the session 'alter system kill session '10,12' immediate' then I ran the SQL.

Dont know why it is doing that. The resource 'SESSIONS_PER_USER' is set to unlimited in dba_profiles;
 

Tom Kyte

Followup  

July 13, 2005 - 3:02 pm UTC

there would be nothing preventing you from connecting then