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.

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

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!!!!

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.

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

FYI
I also benchamarked Bequeath and it averages 13 seconds

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.

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

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.

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

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

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?

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

$ 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 

>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

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.

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.

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?

..... 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> 

....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;