OCI 8 -- pre-fetch ???

From: Roger Loeb <rloeb_at_martech.com>
Date: Thu, 30 Apr 1998 07:36:37 -0600
Message-ID: <6i9up5$59q$1@news1.rmi.net>

The documentation for OCI 8 is ambiguous, rather terse, and confusing. (Other than that, it's almost readable.) I have a specific question regarding the "pre-fetch" attribute.

In some parts of the documentation, it "sounds" like prefetch works for almost any query (eliminating the need to do array fetches ???) In other parts of the documentation, it sounds like it works only with updates.

Can anyone shed any light on the actual performance of OCI?

This is what it says in the documention:

The Oracle8 OCI has several enhancements to improve application performance and scalability. Application performance has been improved by reducing the number of client to server round trips required and scalability improvements have been facilitated by reducing the amount of state information that needs to be retained on the server side. Some of these features include:
* Increased client-side processing, and reduced server-side requirements

• Implicit prefetching of SELECT statement result sets to eliminate the describe round trip
• Elimination of open and close cursor round trips
• Improved support for multi-threaded environments

There is a place in OCI to set the pre-fetch attribute, although this appears to only have an impact on statements that will write output, i.e., the documentation says:
OCI_ATTR_PREFETCH_ROWS
Mode
WRITE
Description
Sets the number of top level rows to be prefetched. The default value is 1 row.

The reference to Mode: Write is the important statement.

However, the OCIStmtFetch() description makes reference to the execution being a local call if pre-fetched rows suffice. OCIStmtFetch()
Purpose
Fetches rows from a query.
Syntax

sword OCIStmtFetch ( OCIStmt     *stmtp,
                     OCIError    *errhp,
                     ub4         nrows,
                     ub2         orientation,
                     ub4         mode );

Parameters
stmtp (IN) - a statement (application request) handle. errhp (IN) - an error handle which can be passed to OCIErrorGet() for diagnostic information in the event of an error. nrows (IN) - number of rows to be fetched from the current position. orientation (IN) - for release 8.0, the only acceptable value is OCI_FETCH_NEXT, which is also the default value. mode (IN) - for release 8.0, pass as OCI_DEFAULT. Comments
The fetch call is a local call, if prefetched rows suffice. However, this is transparent to the application. If LOB columns are being read, LOB locators are fetched for subsequent LOB operations to be performed on these locators. Prefetching is turned off if LONG columns are involved.

I'm confused ... If pre-fetching works, why would anyone bother to implement an array fetch???

Rog
--
roger@_delete_this_to_reply_.martech.com Received on Thu Apr 30 1998 - 08:36:37 CDT