JL Computer Consultancy

Oracle almost invariably works through a TWO_TASK mechanism - a front-end process such as SQL*Plus uses some form of pipe to a shadow process which handles the database processes and files. The purpose of this strategy is to protect the database from sloppy, or malicious, programs that might otherwise trespass into sensitive memory locations; the drawback to the strategy is that a narrow pipe-line can result in waste of resources as the CPU switches from the front-end to the shadow and back again.

This note tells you how to change the Session Data Unit (SDU) so that Oracle will widen the pipeline, sending larger packages between the front-end and the shadow, and possibly saving a few percent on processing time for very large jobs (such as an export, or bulk unload). The note will concentrate on the IPC mechanism for linking a front-end and shadow which are running on the same machine, but the same approach can be used to investigate tcp links, and any other protocols you may use.

SQL*Net seems to be particularly susceptible to minor variations (both deliberate and accidental, it seems) from version to version, so I can only tell you that this note has been tested on HP-UX 11.00, using Oracle 7.3.4.1; you may need to experiment on your own platforms.

Investigation:

In the sqlnet.ora file set up high level tracing (temporarily) so that you can see what pipeline sizes are currently in use -

 
复制

        trace_level_client=16
复制

        trace_directory_client=/u05/app/oracle/admin/network
复制

        trace_file_client=client.trc
复制

        trace_unique_client = true
复制

        trace_level_server=16
复制

        trace_directory_server=/u05/app/oracle/admin/network
复制

        trace_file_server=server.trc
复制

This will result in vast quantities of data being churned out into trace files as you create and use a simple SQL*Plus session. (You might notice the OFA-like structure in the name of the directory I have used for the SQL*Net output - the default is under $ORACLE_HOME but I prefer to put it closer to the udump/bdump directories used for other trace files).

If you scan through a couple of trace files, you will find lines like the following which is from a server trace with an explicit IPC connection and the multi-threaded server not running - the actual content of the packet will vary significantly;

        nsprecv: 187 bytes from transport
复制

        nsprecv: tlen=187, plen=187, type=1
复制

        nsprecv:packet dump
复制

        nsprecv:00 BB 00 00 01 00 00 00  |........|
复制

        nsprecv:01 35 01 2C 08 01 7F 80  |.5.,....|
复制

        nsprecv:7F FF 73 08 00 00 00 01  |..s.....|
复制

        nsprecv:00 89 00 32 00 00 08 00  |...2....|
复制

        nsprecv:09 09 00 00 00 00 00 00  |........|
复制

        nsprecv:00 00 00 00 00 00 00 00  |........|
复制

        nsprecv:00 00 28 44 45 53 43 52  |..(DESCR|
复制

        nsprecv:49 50 54 49 4F 4E 3D 28  |IPTION=(|
复制

        nsprecv:73 64 75 3D 33 32 36 34  |sdu=3264|
复制

        nsprecv:30 29 28 43 4F 4E 4E 45  |0)(CONNE|
复制

        nsprecv:43 54 5F 44 41 54 41 3D  |CT_DATA=|
复制

        nsprecv:28 53 49 44 3D 44 37 33  |(SID=D73|
复制

        nsprecv:34 29 28 43 49 44 3D 28  |4)(CID=(|
复制

        nsprecv:50 52 4F 47 52 41 4D 3D  |PROGRAM=|
复制

        nsprecv:29 28 48 4F 53 54 3D 68  |)(HOST=h|
复制

        nsprecv:70 37 31 32 29 28 55 53  |p712)(US|
复制

        nsprecv:45 52 3D 6A 70 6C 29 29  |ER=jpl))|
复制

        nsprecv:29 28 41 44 44 52 45 53  |)(ADDRES|
复制

        nsprecv:53 5F 4C 49 53 54 3D 28  |S_LIST=(|
复制

        nsprecv:41 44 44 52 45 53 53 3D  |ADDRESS=|
复制

        nsprecv:28 50 52 4F 54 4F 43 4F  |(PROTOCO|
复制

        nsprecv:4C 3D 49 50 43 29 28 4B  |L=IPC)(K|
复制

        nsprecv:45 59 3D 44 37 33 34 29  |EY=D734)|
复制

        nsprecv:29 29 29 00 00 00 00 00  |))).....|
复制

        nsprecv: normal exit
复制

        nscon: got NSPTCN packet
复制

        nsconneg: entry
复制

        nsconneg: vsn=309, lov=300, opt=0x801, sdu=32640, tdu=32767, ntc=0x7308
复制

        nsconneg: vsn=309, gbl=0x801, sdu=32640, tdu=32767
复制

        nsconneg: normal exit
复制

This is part of the negotiation between the client side and the server side to determine the Session Data Unit to be used. In the first section you can see the connection data being passed in by the client (note especially the chunk sdu=32640) and in the last section (my guess is that nsconneg is short of Network Substrate CONnection NEGotiation) we see the lines where the server reports what it can do and what the negotiation has arrived at.

In a default setup, the sdu figures will probably be 2048, rather than 32640. You may also find that the two nsconneg lines show two different values for the sdu - the client and server both volunteer information about what they can do, and the final result is the lower of the two offers.

Implementation:

Once you have a clear idea of how to check what is going on, you can try changing it. I have to say that I have found some systems where the nominal method for changing the SDU simply does not work, but basically you simply fix up the listener.ora and the tnsnames.ora to include the lines: SDU={required value} in the appropriate places:

The listener.ora file could then look like this, with the SDU being part of the SID_DESC::

 
复制

        SID_LIST_LISTENER=
复制

               (SID_LIST=
复制

                       (SID_DESC=
复制

                               (SDU=32640)
复制

                               (SID_NAME=D734)
复制

                               (ORACLE_HOME=/u05/app/oracle/product/7.3.4)
复制

                       )
复制

               )
复制

and a suitable tnsnames.ora entry would look like this, with the SDU being part of the DESCRIPTION:

 
复制

        D734=
复制

            (DESCRIPTION=
复制

               (SDU=32640)
复制

               (ADDRESS=
复制

                       (PROTOCOL=IPC)
复制

                       (KEY=D734)
复制

               )
复制

               (CONNECT_DATA=
复制

                       (SID=D734)
复制

                       (SERVER=DEDICATED)
复制

               )
复制

            )
复制

I have included 'server=(dediated) to try to ensure that your attempts at using this feature work. Various combinations of multi-threaded server, automatic_ipc etc. will result in the IPC pipeline being 2K whatever you specify in these files, but it seems that demanding a dedicated server process (which is only sensible for large volume work anyway) avoids the traps.

With these entries in place, (and after restarting the listener) connect through SQL*Plus, specifying the required tns alias:

        sqlplus userid/password@D734
复制

alternatively you might use the TWO_TASK variable:

        export two_task=D734
复制

        sqlplus userid/password
复制

and then check that the expected changes appear in the trace files.

You will note, by the way, that this setup is not entirely symmetrical -the listener is listening for the sid D734, and is prepared to offer a pipeline of 32640 when it appears; however we could have several different tnsnames entries which resolve to sid D734, and each one could specify a different protocol and SDU. Forexample, if you wanted to have some sessions connect using a very small pipeline between the front-end and back-end.It would be perfectly reasonable for instance to have a second entry which looks like this:

 
复制

        D734S=
复制

            (DESCRIPTION=
复制

               (sdu=1024)
复制

               (ADDRESS=
复制

                       (PROTOCOL=IPC)
复制

                       (KEY=D734)
复制

               )
复制

               (CONNECT_DATA=
复制

                       (SID=D734)
复制

                       (SERVER=DEDICATED)
复制

               )
复制

            )
复制

If you want to investigate in more detail the trace files and negotiation process for the SDU (and the TDU, which you can set in the same way) then you might want to change the client and server one after the other to some 'silly' values which can be found easily in the files.

Confirmation:

Apart from checking the sqlnet trace files to see that the sdu has been set as expected, an alternative for identifying sessions which are enhancing the size of their pipelines is the Unix ps command. With a little luck, and with depending on the implementation of ps on your platform, you may see enough of the shadow's 'command line' to see the SDU setting:

 
复制

        ps -ef | grep oracleD734
复制

        oracle 2324    1  0 19:49:40 ?     0:01 oracleD734 (DESCRIPTION=(LOCAL=NO)(SDU=32640))
复制

        oracle 2327 2318 12 19:50:09 pts/2 0:00 grep oracleD
复制

Conclusion:

Remember - this isn't going to give you huge performance benefits; but for processes which shift large volumes of data from the back-end to the front-end it may save you a couple of percent on the CPU due to reduced task-switching; and when applied to tcp links you may find that a good choice of SDU may reduce the number of packets sent across the network.

Addendum:

In earlier versions of Oracle, it was possible to generate 'single-task' versions of Oracle's front-end tools which were significantly more efficient than the two-tasking versions; but this feature was always frowned upon by Oracle support, and is now desupported. (In fact in Oracle 7.3.4 on HP-UX 11.00 I have not yet been able to make a single-tasking exp). For those of you interested in creating a single-tasking import/export set, you may find that the following still works on your platform:

        cd $ORACLE_HOME/rdbms/lib
复制

        make -f ins_rdbms.mk single_task
复制

This will leave a single-task version of imp, exp, and sqlldr in the local directory ready for you to test.