Database Reference
In-Depth Information
Even if row prefetching is much more important for the client, the database also profits from it. In fact, row
prefetching greatly reduces the number of logical reads (from 100,004 to 3,542). The following execution statistics
show the reduction when 50 rows are prefetched:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2001 0.11 0.13 665 3542 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2003 0.11 0.21 665 3542 0 100000
The next sections provide some basic information on how to take advantage of row prefetching with PL/SQL, OCI,
JDBC, ODP.NET, and PHP. In addition to the functionalities provided by every API, from version 12.1 onward, the value set
by an application can be overridden by Oracle's client $TNS_ADMIN/oraaccess.xml configuration file. Note that because
it's a client configuration file, the PL/SQL engine isn't impacted by it. However, all applications that are connected through
the OCI libraries are. The following example shows how to set row prefetching to 100 for all connections:
<?xml version="1.0" encoding="ASCII" ?>
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<prefetch>
<rows>100</rows>
</prefetch>
</default_parameters>
</oraaccess>
To take advantage of the $TNS_ADMIN/oraaccess.xml configuration file, only the client binaries must be those
of version 12.1. In other words, the database version doesn't matter.
Note
Detailed information about the $TNS_ADMIN/oraaccess.xml configuration file is provided by the Oracle Call
Interface Programmer's Guide manual.
PL/SQL
If, at compile time, the plsql_optimize_level initialization parameter is set to 2 (the default value) or higher, row
prefetching is used for cursor FOR loops. For example, the query in the following PL/SQL block prefetches 100 rows at
a time:
BEGIN
FOR c IN (SELECT * FROM t)
LOOP
-- process data
NULL;
 
 
Search WWH ::




Custom Search