Stored SQL Reduces Network Traffic

David Clement

September 2004

Oracle versions 7-8

An in-line SQL statement is embedded directly into the host language. Here is an example.

          exec sql update crd_rqt_accts 
          set crd_rqt_retry = :unRetry,
          crd_rqt_exch_id = :chExchId,
          crd_rqt_user_id = :chUserId,
          crd_rqt_dt = to_date (:chInpDt, 'DD-MON-YYYY HH24:MI:SS'),
          where acctid = :chAcctId;

Stored SQL is submitted to the database as a procedure or package. A procedure, for instance, looks like this.


						            create or replace procedure updCrdRqtAccts
              (pnCrdRqtRetry in number,
              pchExchId in varchar2,
              pchUserId in varchar2,
              pchRqtDt in varchar2,
              pchAcctId in varchar2)
          as
          begin
              update crd_rqt_accts
              set crd_rqt_retry = pnCrdRqtRetry,
              crd_rqt_exch_id = pchExchId,
              crd_rqt_user_id = pchUserId,
              crd_rqt_dt = to_date (pchRqtDt, 'DD-MON-YYYY HH24:MI:SS')
              where acctid = pchAcctId;
          end;

The procedure is called in-line as follows.


						            exec sql updCrdRqtAccts (:unRetry, :chExchId, :chUserId, 
            :chInpDt, :chAcctId);

It is easy for an in-line SQL statement to get long; statements of 700 to a thousand bytes are not uncommon. Procedure calls, on the other hand, stay relatively short, even though it takes more typing to create the procedure in the first place. This actually makes a difference in Oracle performance.

The reason is that a user process submits an in-line SQL statement as an ASCII string, which, if it is long, has to be disassembled into packets by the Oracle network client before it is shipped across the network, and then reassembled for the use of the server process on the other machine. The reassembly process has its own wait state in the trace files: sql*net more data from client. In most applications these wait states are completely unnecessary.

For this particular tip, you might want to bear in mind that the usual UDP datagram size is 256 bytes. It's configurable but this is the most common size. An in-line SQL statement 520 bytes long would thus require three packets. If that statement was executed a few million times a day, the chore of splitting up packets and reassembling them would amount to something.