Stored SQL Reduces Network Traffic
Oracle versions 7-8
An in-line SQL statement is embedded directly into the host language.
Here is an example.
Stored SQL is submitted to the database as a procedure or package.
A procedure, for instance, looks like this.
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;
The procedure is called in-line as follows.
create or replace procedure updCrdRqtAccts
(pnCrdRqtRetry in number,
pchExchId in varchar2,
pchUserId in varchar2,
pchRqtDt in varchar2,
pchAcctId in varchar2)
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;
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.
exec sql updCrdRqtAccts (:unRetry, :chExchId, :chUserId,
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.