TCP vs. IPC - don't understand results.

From: Ed Stevens <Ed_Stevens_at_nospam.noway.nohow>
Date: Tue, 13 Nov 2001 20:19:10 GMT
Message-ID: <3bf18018.82182191@ausnews.austin.ibm.com>

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?

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)

Received on Tue Nov 13 2001 - 14:19:10 CST