Re: TCP vs. IPC - don't understand results.

On Tue, 13 Nov 2001 20:19:10 GMT, Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) wrote:

>Subject: TCP vs. IPC - don't understand results.
>
>Platform: NT4, Oracle 8.0.5 SE
>
>I posted this the other day and didn't get any takers. Here's the
>scenario:
>
>We have a server hosting multiple databases. One of these databases
>(let's call it CORP) is the target of dblinks defined in some of the
>other db's on the same server. Let's call one of these APPL. In
>looking at performance issues, we find that the CORP database spends
>significant wait time with SQLNet More Data to Client, and APPL spends
>significant wait time with SQLNet Msg from DBLink.
>
>It occurred to me that the dblink was using a TCP/IP connection and if
>I changed that to an IPC call we should see some significant
>performance gains by cutting the network out of the picture. On my
>first attempt, which I posted a few days ago, I confused BEQUETH with
>IPC connections, and my results were disappointing. Now that I've got
>that cleared up, my results are still disappointing. At this point I
>don't know if it's my testing method or my interpretation of the
>results that is flawed. Or maybe both.
>
>To start, we had this entry in the server's TNSNAMES for the CORP
>database:
>
>CORPDB.world =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = TCP)
> (Host = xx.xx.xx.xx)
> (Port = 1526)
> )
> )
> (CONNECT_DATA =
> (SID = CORP)
> (SRVR = DEDICATED)
> (GLOBAL_NAME = CORPDB.WORLD)
> )
> )
>
>And this in LISTENER.ORA:
>
>LISTENER =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = TCP)
> (Host = xx.xx.xx.xx)
> (Port = 1526)
> )
> )
>
>SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (GLOBAL_DBNAME = CORPDB.world)
> (SID_NAME = CORP)
> (PRESPAWN_MAX = 0)
> )
>
>First step was to add an entry for IPC connections to the listener:
>
>LISTENER =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = TCP)
> (Host = xx.xx.xx.xx)
> (Port = 1526)
> )
> (ADDRESS =
> (PROTOCOL = IPC)
> (KEY = IPCKEY1)
> )
> )
>
>Stopped and restarted the listener, then added an entry in TNSNAMES:
>
>CORP-IPC.world =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = IPC)
> (KEY = ipckey1)
> )
> )
> (CONNECT_DATA = (SID = CORP)
> )
> )
>
>Connected to corp-ipc to confirm the connection was working.
>
>Captured some live queries from v$sqlarea, identified 3 that exercised
>the dblink, and saved them to a file, "test_link.sql"
>
>For the test itself I built a batch file as follows:
>
>Rem --- benchmark the tcp connection ----
>Rem - run a sql to define the dblink to point to the CORPDB.WORLD
>(tcp) entry --
>Plus80 /nolog @set_tcp
>Rem - run utilbstat on the two db's being watched
>For %%1 in (APPL CORP) do call run_bstat
>Rem - repeatedly execute the queries that exercise the dblink
>For /L in (1,1,50) do call sqlplus /nolog test_link
>Rem -- run utilestat on the two db's being watched, rename the
>REPORT.TXT files
>For %%1 in (APPL CORP) do call run_estat
>
>Rem ---- repeat above using IPC connection ----
>Rem - run a sql to define the dblink to point to the CORP-IPC.WORLD
>(IPC) entry --
>Plus80 /nolog @set_ipc
>Rem - run utilbstat on the two db's being watched
>For %%1 in (APPL CORP) do call run_bstat
>Rem - repeatedly execute the queries that exercise the dblink
>For /L in (1,1,50) do call sqlplus /nolog test_link
>Rem -- run utilestat on the two db's being watched, rename the
>REPORT.TXT files
>For %%1 in (APPL CORP) do call run_estat
>
>Sent the utilestat results to Oraperf for analysis. In the CORP
>database, the SQLNet Msg to Client event disappeared completely, just
>as I expected. However, on the APPL database, the SQLNet Msg from
>DBLink event did not disappear, but did show some reduction:
>
>SQLNet Msg from DBLink, w/ TCP connection:
>Per execute = 0.43
>Per user call = 0.17
>Per Transaction = 1422.00
>
>SQLNet Msg from DBLink, w/ IPC connection:
>Per execute = 0.14
>Per user call = 0.06
>Per Transaction = 469
>
>These numbers are from a tightly controlled sample on a lightly loaded
>test system. The TCP numbers taken from the production system in
>15-minute bstat/estat periods show worse numbers.
>
>So the question is, do I need to modify my test procedure? Do I need
>to modify my interpretation? Or is this dead-on and as good as it
>gets?

Unfortunately you posted the trivial parts of your code, and so it is impossible to discern if there are any problems in the sql statements you are firing and/or whether the driving_site hint would help.

If you are looking to eliminate those waits, I think you can safely forget about that. You can trace on various levels, and you always see the same: sqlnet is a fully _synchronous_ protocol, so the local site will always wait for the remote site to confirm completion of communication.
As every individual packet is going to be confirmed by the other site (or the Far Side :) ) the only thing you can do is decrease the number of packets by increasing their size. The SDU parameter in listener.ora and tnsnames.ora serves this purpose. This Session Data Unit however should be a multiple of the Max Transmission Unit of the protocol. On NT, unfortunately, I have no idea how to determine the MTU of the IPC protocol.

Hth anyway

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Nov 13 2001 - 15:09:13 CST