The DataServer caches results sets from the Oracle database to enhance performance. It caches as much data as fits in its allocated cache size. Depending on what kind of cursor a query is using, the DataServer caches row identifiers or records:

  • Standard cursors — The DataServer caches row identifiers for the results set. If the database table is using the native ROWID as the row identifier, each identifier requires 18 bytes of cache. If the table is using a PROGRESS_RECID column or another index as the row identifier, each identifier requires 8 bytes of cache. Therefore, a results set of 100 records requires either 1800 or 800 bytes of cache.
  • Lookahead cursors — The DataServer caches complete records or partial records as specified by a field list. The DataServer uses the maximum length allowed for a row as defined in the Oracle database to calculate the record length, not the actual contents of the record. In addition to the defined row length, the record consists of a row identifier field. Therefore, a row with a defined maximum length of 100 bytes and a native ROWID field (used by the DataServer as the row identifier) requires 118 bytes of cache. The DataServer counts a LONG or LONG RAW column as being 256 bytes long. If a LONG or LONG RAW column is longer than 256 bytes, the DataServer refetches it.

In the case of joins, each record in the cache is a result of the fields selected in the join. In addition to the record, there is a row identifier field (8 or 18 bytes) for each table involved in the join. For example, a three-way join for tables that use the native ROWID as a row identifier, adds 54 bytes to the cache for each result row.

You can affect the performance of a query by controlling the size of the cache. As queries generate different results, they benefit from different cache sizes. Generally, the larger the cache, the faster the performance. However, you must balance cache size against other memory requirements for your system. Consider also that continually adjusting cache size in an application might decrease performance, as each adjustment requires the DataServer to make several calls to the OCI.

To determine the optimal cache size for a query, experiment with different values for CACHE-SIZE and use DEBUG EXTENDED to generate cursor statistics in the dataserv.lg file that you can examine. Aim for minimal cursor activity. You might also want to lower the cache size for queries that typically fetch only a row or two. This makes memory available for other, more productive uses.

The following statement is an example of setting an optimal cache size for a particular query against the Sports database:

FOR EACH customer, EACH order OF customer WHERE order.ordernum > 20
  QUERY-TUNING(CACHE-SIZE 20 ROW DEBUG EXTENDED):