How to Reduce Database Round Trips in Oracle Forms

A form is slow for one of two reasons: it does a lot of work, or it waits a lot. In Oracle Forms applications, waiting dominates. Every trip from the Forms server to the database takes time that has nothing to do with the work itself, the latency of the network, and a form that makes thousands of trips is slow on any network.

This guide measures database round trips in Oracle Forms 14.1.2 with a form that fetches all 1,341 appointments of a sample clinic in different ways. It shows how to count trips from a form, what lookups in POST-QUERY really cost, and how Query Array Size, DML Array Size, and a few other settings cut the trips.

Sample Form for This Guide

The examples and screenshots use the sample form CH34_PERF from the Oracle Forms code repository on GitHub. Download it, open it in Forms Builder, and connect as CAREWELL to follow along.

The forms run against the CareWell Clinic sample schema, which you install first.

The Results at a Glance

The test database ran next to the Forms server, so its times are short. The counts are what matter, because each trip costs more on a real network.

Measure Round Trips from a Form

The database counts the work of every session. V$MYSTAT holds the statistics of the current session and V$STATNAME their names. The ones that matter here are SQL*Net roundtrips to/from client, the trips, and execute count, the statements run.

A form reads them like any view, given the privilege to select from them; the test granted SELECT on SYS.V_$MYSTAT and SYS.V_$STATNAME to the schema user.

Program unit SESSION_STAT:

function session_stat(p_name varchar2) return number is
  v_value number;
begin
  select m.value into v_value                       -- this session's statistic
  from   v$mystat m, v$statname n
  where  n.statistic# = m.statistic# and n.name = p_name;
  return v_value;
end;

The query uses the old join syntax because the Forms PL/SQL engine rejects ANSI joins, as explained in how to write PL/SQL in Oracle Forms.

MEASURE reads the statistics before and after a query that fetches every row, since LAST_RECORD fetches up to the last one, and times it with DBMS_UTILITY.GET_TIME, in hundredths of a second.

Program unit MEASURE:

procedure measure(p_mode varchar2, p_label varchar2) is
  v_start number; v_trips number; v_execs number;
begin
  :ctl.mode := p_label;
  v_trips := session_stat('SQL*Net roundtrips to/from client');
  v_execs := session_stat('execute count');
  v_start := dbms_utility.get_time;                 -- centiseconds
  go_block('APPTS');
  execute_query;
  last_record;                                      -- fetches every row
  :ctl.cs    := dbms_utility.get_time - v_start;
  :ctl.trips := session_stat('SQL*Net roundtrips to/from client') - v_trips;
  :ctl.execs := session_stat('execute count') - v_execs;
  :ctl.rows  := :system.cursor_record;
  first_record;
end;

For a whole application, the database's own tools do the same across sessions: SQL trace, and the reports of the Automatic Workload Repository. Forms Trace records the triggers, statements, and built-ins of a Forms session, with their durations.

Lookups in POST-QUERY

POST-QUERY runs for every row fetched. The sample performance form shows appointments with the patient's name, the doctor's name, and the amount billed, which are not columns of APPOINTMENTS. Its POST-QUERY fills them in one of two ways: three queries, or one call to a stored procedure, CW_API.APPT_DETAILS, that returns all three from one query.

Example (POST-QUERY trigger on APPTS):

if :ctl.mode = 'Three queries' then
  select first_name || ' ' || last_name into :appts.patient
  from   patients where patient_id = :appts.patient_id;
  select 'Dr. ' || last_name into :appts.doctor
  from   doctors where doctor_id = :appts.doctor_id;
  select max(i.total_amount) into :appts.billed
  from   visits v, invoices i
  where  i.visit_id = v.visit_id and v.appt_id = :appts.appt_id;
elsif :ctl.mode = 'One call' then
  cw_api.appt_details(:appts.appt_id, :appts.patient, :appts.doctor, :appts.billed);
end if;

The No Lookups, Three Queries, and One Call buttons each fetch all 1,341 appointments.

Three queries cost three round trips per row, and one call costs one. With the database a few milliseconds away instead of a few hundred microseconds, the 4,000 extra trips of the three queries add many seconds to every query of the form.

The Best POST-QUERY Is None

A block based on a view, or on a query in its Query Data Source, fetches the names and the amount with the appointments, in the same statement and the same array fetches. Keep POST-QUERY for what SQL cannot compute, and when it must query, make it one call. See how to base a block on a FROM clause query and how to control queries using query triggers.

Tune the Query Array Size

A block fetches its rows in arrays. Its Query Array Size is the number of rows per fetch; by default, the number of records it displays, 10 here. Three versions of the form, with the lookups off, fetched the 1,341 appointments:

With an array of one, the Oracle client fetched ahead on its own, and needed 676 trips rather than 1,341. A larger array fetches the same rows in fewer trips; the price is memory on the Forms server and a first screen that waits for a whole array.

For a block that users scroll through, or that Query All Records fills completely, an array of 50 to 100 costs little. The Forms server keeps fetched records in memory, up to the block's Number of Records Buffered, and moves the rest to a temporary file. These block properties are described in how to create data blocks in Oracle Forms.

DML Array Size and Other Settings

DML Array Size does for inserts, updates, and deletes what Query Array Size does for queries: with 20, saving 20 new records is one statement execution instead of 20. The block then needs its primary key items marked, because Forms no longer reads each record's ROWID, and Update Changed Columns Only is ignored.

Other settings serve the database too:

• DML Returning Value brings back values set by the database in the same statement, not with a query per row.
• Query criteria become bind variables, but a DEFAULT_WHERE that concatenates values creates a new statement for every value, which the database must parse.
• The block's Optimizer Hint adds a hint to its queries, for the rare case where the database's plan needs one.
• Stored procedures and procedure-based blocks move work that needs many statements into the database, close to the data.

For the trips between the user's computer and the Forms server, see how to reduce round trips to the Forms server.

Conclusion

Oracle Forms applications wait more than they work, so count database round trips with V$MYSTAT before and after an action. A POST-QUERY that queries costs a round trip per query per row: 4,149 trips for 1,341 rows with three lookups, 1,467 with one call, and 127 with none, so fetch computed values with the block's own query where possible. Raise Query Array Size for blocks users scroll through, use DML Array Size for saving, and keep the SQL shareable with bind variables.