14 Replies Latest reply: Feb 25, 2010 11:27 AM by 755557 RSS

    IPC vs SQL*Net connection

    501556
      Hi All,

      My database is 10.2.0.4 running on windows 2008 EE (with all sevice packs). On the same box I have several workers (implemented as windows service) written in c#.net using latest ODP.NET drivers.
      To improve performance I'm using ICP connection so that TCP stack (that is used in 'regular' sqlnet connections) is bypassed. I confirmed ICP is actually in use:

      h4. listener log
      13-NOV-2009 03:01:34 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:34 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:34 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:35 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:35 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:35 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:35 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      13-NOV-2009 03:01:35 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=orclwin)(CID=(PROGRAM=C:\Simulation\Agent.exe)(HOST=WIN2008EE)(USER=LOCAL?SERVICE))) * (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)) * establish * orclwin * 0
      However, when I look at awr report I see sqlnet waits on top:

      h4. awr top waits
      Top 5 Timed Events                                         Avg %Total
      ~~~~~~~~~~~~~~~~~~                                        wait   Call
      Event                                 Waits    Time (s)   (ms)   Time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      SQL*Net message to client         3,014,783   5,086,969   1687 ######    Network
      SQL*Net more data to client       1,712,644   2,441,249   1425 ######    Network
      log file sync                       695,446      75,297    108 ######     Commit
      log file parallel write             679,437      33,288     49  916.9 System I/O
      db file parallel write               49,057      22,149    452  610.1 System I/O
      I have read Taner Poder's blog entry [http://blog.tanelpoder.com/2008/02/07/sqlnet-message-to-client-wait-gotcha/] concerning SQNet message to client
      and his further comments about SQLNet more data to client [http://blog.tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-client/]
      to get the deeper understanding of them.

      The question is why do I observe SQLNet connection specific waits when using IPC connections ?

      I'll appreciate any comments.
      Thanks,
      Rafal.
        • 1. Re: IPC vs SQL*Net connection
          Gerwin Hendriksen
          Can you state how your listener.ora setup looks like. It should be something with (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=IPCKEY))).

          Regards, Gerwin
          • 2. Re: IPC vs SQL*Net connection
            501556
            Hi Gerwin,

            My listner.ora:
            SID_LIST_LISTENER =
              (SID_LIST =
                (SID_DESC =
                  (SDU=32767)
                  (TDU=32767)
                  (SID_NAME = PLSExtProc)
                  (ORACLE_HOME = C:\u01\app\oracle\product\10.2.0\db_1)
                  (PROGRAM = extproc)
                )
              )
            
            LISTENER =
              (DESCRIPTION_LIST =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = win2008ee)(PORT = 1521))
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                )
              )
            for reference tnsnames.ora
            ORCLWIN =
              (DESCRIPTION =
                (SDU = 32767)
                (TDU = 32767)
                (ADDRESS_LIST=
                 (ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC0))
                 (ADDRESS = (PROTOCOL = TCP)(HOST = win2008ee)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SID = orclwin)
                )
              )
            
            EXTPROC_CONNECTION_DATA =
              (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                )
                (CONNECT_DATA =
                  (SID = PLSExtProc)
                  (PRESENTATION = RO)
                )
              )
            Regards,
            Rafal.
            • 3. Re: IPC vs SQL*Net connection
              Billy~Verreynne
              These events are equally valid to IPC connections. The client server model is not different because of IPC. The only difference is the communication transport layer. Instead of using TCP via the IP stack, the OCI driver and Oracle server processes communicate directly with one another via IPC.

              The [Oracle® Database Reference|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref3196] define "+SQL*Net message to client+" as:
              >
              The server (foreground process) is sending a message to the client.
              Wait Time: The actual time the send takes
              • 4. Re: IPC vs SQL*Net connection
                Billy~Verreynne
                Gerwin Hendriksen wrote:
                Can you state how your listener.ora setup looks like. It should be something with (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=IPCKEY))).
                Why should this matter? An IPC connection does not need the listener. Shutdown the listener, do an IPC connection and see for yourself.

                The reason why ORACLE_HOME needs to be set for an IPC connection is to enable the OCI client driver to find and execute an Oracle server process. The reason why ORACLE_SID needs to be set is to enable that server process to attach to the correct SGA and join the correct instance.

                The listener does not feature at all here. (assuming the connection initiated by the client is true IPC and not TWO_TASK or something else)

                To illustrate this, here's a hack I did on my local XE instance:
                oracle:/usr/lib/oracle/xe> echo $ORACLE_HOME
                /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
                
                'I have replaced the oracle executable with the following shell script'
                oracle:/usr/lib/oracle/xe> cat $ORACLE_HOME/bin/oracle
                #!/bin/sh
                
                echo "Hello from ORACLE HACK:"
                echo "command line: $*"
                echo "..passing control to the real oracle executable..."
                
                /tmp/oracle "$*"
                
                'I have moved the oracle executable to /tmp'
                oracle:/usr/lib/oracle/xe> file /tmp/oracle
                /tmp/oracle: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.2.5, stripped
                
                'now I execute SQL*Plus - and the local OCI driver launches the oracle dedicated server process'
                oracle:/usr/lib/oracle/xe>
                oracle:/usr/lib/oracle/xe> sqlplus "/ as sysdba"
                
                SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 13 13:52:15 2009
                
                Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                
                Hello from ORACLE HACK:
                command line: (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                ..passing control to the real oracle executable...
                Connected to an idle instance.
                
                SQL>
                • 5. Re: IPC vs SQL*Net connection
                  501556
                  Hi Billy,

                  I previously put reference to Tanel Podler's blog, where he states that documentation is inaccurate for this wait
                  SQL*Net message to client” does NOT measure network latency! It merely measures how long it took to put the response message into TCP send buffer on the server!
                  
                  Once the response packet is put into TCP send buffer, Oracle server process continues on and starts waiting for “SQL*Net message FROM client” again. It’s up to TCP stack to deliver this packet from this point and Oracle server process has no way for measuring directly when did the packet arrive (it would have to start intercepting TCP ACK packets at kernel level for that).
                  
                  This behaviour also explains, why the “SQL*Net message TO client” waits are usually unbelievably short – like 1 microsecond etc. The light in vacuum travels only 300 meters in one microsecond, the signal in a cable travels much less – yet when sending a single packet from London to Hong-Kong, we see few microsecond “response times”…
                  so basically if the above is correct then this wait is all about TCP..

                  For IPC protocol I would expect to see waits like:
                  rdbms ipc message     
                  rdbms ipc reply
                  rdbms ipc message block
                  Documentation states that those are inter process communications (ipc?) waits between Oracle's background processes.. However, I would expect similar behaviour for ipc communication between other processes. But that's just guessing..

                  I'll try to confirm that SQLNet waits are valid for IPC by tracing sessions. I'll post the result.

                  Thanks,
                  Rafal.
                  • 6. Re: IPC vs SQL*Net connection
                    Billy~Verreynne
                    rafi2 wrote:

                    I previously put reference to Tanel Podler's blog, where he states that documentation is inaccurate for this wait
                    I did read that and do not see a contradiction with that and what the Oracle Reference guide states regarding these events - except perhaps that people read more into the very basic definition that the Reference guide provides.
                    so basically if the above is correct then this wait is all about TCP..
                    The assumption that wait events that contains the text "+SQL*Net+" infers a TCP/IP connection is incorrect.

                    Simple test. Use "+sqlplus scott/tiger+" (or similar) on the db server to connect to the Oracle instance via IPC. From another session, select the wait event for that IPC session. It will state "+SQL*Net message from client+".

                    So you can argue that Oracle is confusing the terminology, but you cannot argue that SQL*Net events only apply to TCP/IP connections and not IPC connections.
                    • 7. Re: IPC vs SQL*Net connection
                      Billy~Verreynne
                      rafi2 wrote:

                      Documentation states that those are inter process communications (ipc?) waits between Oracle's background processes.. However, I would expect similar behaviour for ipc communication between other processes. But that's just guessing..
                      Exactly.. don't second guess the documentation without proof. :-)

                      If the document states "+ABC+" then it is a 99.9999% chance that it is "+ABC+" and not "+123+". Remember that we're at a pretty mature version of Oracle and using pretty mature documentation.

                      Not saying that there are not the occasional bummer in the Oracle documentation. But it is a mistake to assume by default that the documentation lacks accuracy and then start to second guess that documentation - especially when it comes to definitions (like the definitions of wait states). In that case, we just as well can not even bother with the documentation at all...
                      • 8. Re: IPC vs SQL*Net connection
                        Billy~Verreynne
                        Oh yeah - one more comment.

                        In order for the server process to indicate whether the wait is over TCP/IP or IPC, it needs to know the transport mechanism employed.

                        It is very likely that the wait event is recorded by the server process when it calls its API to send() data to the client. As it calls the send(), it logs that as the current wait state. It has no idea what the underlying code is using as the transport mechanism. In client and server code, the transport layer is one of the very lowest layers. So higher up in the application code, you have no idea what the actual transport is (remember that Oracle also support, or did support, other communication layers like Lu6.2).

                        So from the application layer, you deal with the transport layer as somewhat of a black box. In Oracle terminology, that is the so-called SQL*Net layer. And can be anything that the transport layer implements.

                        So this is why it is dangerous to make assumptions that a higher level wait state indicates what a lower level wait state is.
                        • 9. Re: IPC vs SQL*Net connection
                          501556
                          Hi Billy,

                          Thanks for explaining me this issue. I did a session trace, I'm enclosing tkprof summary below:
                           Event waited on                             Times   Max. Wait  Total Waited
                            ----------------------------------------   Waited  ----------  ------------
                            SQL*Net message to client                    5635        0.00          0.02
                            SQL*Net message from client                  5635        3.18        399.46
                            log file sync                                1300        0.91          7.53
                            latch: In memory undo latch                    16        0.00          0.00
                            SQL*Net more data to client                  3210        0.00          0.07
                            db file sequential read                       268        0.05          0.75
                            buffer busy waits                               8        0.00          0.00
                            latch: cache buffers chains                     1        0.00          0.00
                          So you're right.. Even if ipc connection is used Oracle counts 'communication' waits under the SQL*Net type waits.

                          Thanks,
                          Rafal.
                          • 10. Re: IPC vs SQL*Net connection
                            jgarry
                            rafi2 wrote:
                            Hi Billy,

                            Thanks for explaining me this issue. I did a session trace, I'm enclosing tkprof summary below:
                            Event waited on                             Times   Max. Wait  Total Waited
                            ----------------------------------------   Waited  ----------  ------------
                            SQL*Net message to client                    5635        0.00          0.02
                            SQL*Net message from client                  5635        3.18        399.46
                            log file sync                                1300        0.91          7.53
                            latch: In memory undo latch                    16        0.00          0.00
                            SQL*Net more data to client                  3210        0.00          0.07
                            db file sequential read                       268        0.05          0.75
                            buffer busy waits                               8        0.00          0.00
                            latch: cache buffers chains                     1        0.00          0.00
                            So you're right.. Even if ipc connection is used Oracle counts 'communication' waits under the SQL*Net type waits.

                            Thanks,
                            Rafal.
                            Just curious, did you see an improvement bypassing the tpc stack? I've long assumed this to be true, but wondering if it truly is, since a vendor told me it isn't (and I don't have your environment to test).
                            • 11. Re: IPC vs SQL*Net connection
                              501556
                              Hi jgarry,


                              h5. Trace output for TCP connection
                              OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
                              
                              call     count       cpu    elapsed       disk      query    current        rows
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              Parse        0      0.00       0.00          0          0          0           0
                              Execute  10090     10.75 1144524.46        276     112889      73406       29849
                              Fetch     4716      1.71  229204.57         83      26747          0      893596
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              total    14806     12.46 1373729.04        359     139636      73406      923445
                              
                              Misses in library cache during parse: 0
                              
                              Elapsed times include waiting on following events:
                                Event waited on                             Times   Max. Wait  Total Waited
                                ----------------------------------------   Waited  ----------  ------------
                                SQL*Net message to client                   12577     1599.37      54258.38
                                SQL*Net message from client                 12577     1467.00       6954.63
                                log file sync                                2895     1566.06       1576.40
                                SQL*Net more data to client                  7088     1597.62      41420.76
                                latch: In memory undo latch                    65        0.00          0.00
                                latch: cache buffers chains                     7        0.00          0.00
                                db file sequential read                       359        0.34          2.12
                                buffer busy waits                              94        0.00          0.00
                                read by other session                          14        0.23          0.46
                                enq: TX - row lock contention                   7        0.02          0.03
                                enq: TX - index contention                     15        0.08          0.17
                                latch free                                      1        0.00          0.00
                                latch: enqueue hash chains                      1        0.00          0.00
                                log file switch completion                      1        0.02          0.02
                              
                                 419  elapsed seconds in trace file.
                              h5. Trace output for IPC connection
                              OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
                              
                              call     count       cpu    elapsed       disk      query    current        rows
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              Parse        0      0.00       0.00          0          0          0           0
                              Execute  12112     12.78 1152071.88        355     186165      89153       36120
                              Fetch     5638      1.48  690948.18         86      31448          0     1078848
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              total    17750     14.26 1843020.07        441     217613      89153     1114968
                              
                              Misses in library cache during parse: 0
                              
                              Elapsed times include waiting on following events:
                                Event waited on                             Times   Max. Wait  Total Waited
                                ----------------------------------------   Waited  ----------  ------------
                                SQL*Net message to client                   15120     2943.66     108296.72
                                SQL*Net message from client                 15120     2787.53      19403.85
                                log file sync                                3492     2701.66       5365.24
                                db file sequential read                       441     2703.21       2705.20
                                SQL*Net more data to client                  8554     2953.65      90231.21
                                latch: In memory undo latch                    74        0.00          0.00
                                buffer busy waits                             101        0.00          0.00
                                enq: TX - row lock contention                   9        0.05          0.12
                                enq: TX - index contention                      6        0.01          0.02
                                buffer deadlock                                 1        0.00          0.00
                                latch: cache buffers chains                     5        0.00          0.00
                                cursor: pin S                                   3        0.00          0.00
                                read by other session                           3        0.01          0.01
                                latch: enqueue hash chains                      1        0.00          0.00
                              
                               455  elapsed seconds in trace file.
                              Once we get into consideration figures:
                                Wait                        IPC – Total waited / Time waited TCP – Total waited / Time waited IPC/TCP
                                SQL*Net message to client     7,16                              4,31                             1,66
                                SQL*Net message from client     1,28                             0,55                               2,32
                                SQL*Net more data to client     10,55                            5,84                             1,81
                              In my test case TCP waits are actually smaller than corresponding IPC waits.. However applications registers a bit higher throughput for IPC connections (49.8380 vs. 48.4595 units of work per second). So IPC is a little bit better in my case. Traces, however, suggest TCP is way better. That is inconclusive result.

                              Does anyone have more thorough tests and results for TCP vs IPC connections? Could you share your results?

                              Regards,
                              Rafal.
                              • 12. Re: IPC vs SQL*Net connection
                                jgarry
                                Here's an [old classic|http://www.oreillynet.com/pub/a/network/excerpt/optimizing_oracle_chap12/index.html] case study showing how other things going on in a system can vastly override simple performance specs.

                                Moral: sometimes you just want to do things different just to get away from the madding crowd. Like using the surface streets to go around the freeway jam.
                                • 13. Re: IPC vs SQL*Net connection
                                  755557
                                  I am new here, thank you all for the clear explanation so far.
                                  I do have a specific connection question regarding IPC/TCP/BEQ.
                                  'I have replaced the oracle executable with the following shell script'
                                  oracle:/usr/lib/oracle/xe> cat $ORACLE_HOME/bin/oracle
                                  #!/bin/sh

                                  echo "Hello from ORACLE HACK:"
                                  echo "command line: $*"
                                  echo "..passing control to the real oracle executable..."

                                  /tmp/oracle "$*"

                                  'I have moved the oracle executable to /tmp'
                                  oracle:/usr/lib/oracle/xe> file /tmp/oracle
                                  /tmp/oracle: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.2.5, stripped

                                  'now I execute SQL*Plus - and the local OCI driver launches the oracle dedicated server process'
                                  oracle:/usr/lib/oracle/xe>
                                  oracle:/usr/lib/oracle/xe> sqlplus "/ as sysdba"

                                  SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 13 13:52:15 2009

                                  Copyright (c) 1982, 2005, Oracle. All rights reserved.

                                  Hello from ORACLE HACK:
                                  command line: (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                                  ..passing control to the real oracle executable...
                                  Connected to an idle instance.

                                  SQL>
                                  See the bold comment above, does it mean that the protocol is BEQ and NOT IPC. I thought they are different. IPC does require listener and BEQ does NOT.

                                  I can use " !ps auxw |grep xxxxSID " to view processes associated with this to verify indeed the protocol is BEQ. Then, how do I setup IPC connection. And does it require listener or not? wait, I already know BEQ does NOT need listener. But if I stop my listener, IPC does not work for me.

                                  Please help my understanding.

                                  al
                                  • 14. Re: IPC vs SQL*Net connection
                                    755557
                                    Oracle support's metalink note: IPC Explained [ID 29232.1] provides a very clear and concise explanation of IPC.
                                    My question above was answered.
                                    IPC connection does require listener!
                                    +<< It is important to remember is that IPC can only be used when the Client and Server reside on the same host. The Inter-Process Communication takes place between two processes within the memory space of the Operating System, so those two processes must reside on the same Host. Another important fact is that IPC is only be used by having the Client connect through the Oracle Listener. If the Listener is bypassed, the Client and Server processes will use the Bequeath protocol (BEQ) instead. BEQ is not covered in this document, but please refer to the Oracle Network Administration Manual for more on the BEQ protocol.>>+