Beyond Data Access and Join Optimization - Troubleshooting Oracle Performance - page 640

while (resultset.next())
{
id = resultset.getLong("id");
pad = resultset.getString("pad");
// process data
}
resultset.close();
statement.close();
ODP.NET
The default fetch size of ODP.NET (65,536) is defined in bytes, not rows. You can change this value through the
FetchSize property provided by the OracleCommand and OracleDataReader classes. The following code snippet is an
example of how to set the value for fetching 100 rows. Notice how the RowSize property of the OracleCommand class is
used to compute the amount of memory needed to store the 100 rows. The C# program in the RowPrefetching.cs file
provides a complete example:
sql = "SELECT id, pad FROM t";
command = new OracleCommand(sql, connection);
reader = command.ExecuteReader();
reader.FetchSize = command.RowSize * 100;
while (reader.Read())
{
id = reader.GetDecimal(0);
pad = reader.GetString(1);
// process data
}
reader.Close();
As of ODP.NET version 10.2.0.3, you can also change the default fetch size through the following registry entry
( <Assembly_Version> is the full version number of Oracle.DataAccess.dll ):
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\<Assembly_Version>\FetchSize
PHP
Row prefetching is enabled with the PECL OCI8 extension by default. You can change the default number of fetched
rows (100—through version 1.3.3 of the extension it's 10) in two ways. The first is to change the default value by setting
the oci8.default_prefetch option in the php.ini configuration file. The second is to override the default at the
statement level by calling the oci_set_prefetch function between the parse and the execution phase. The following
code snippet is an example of how to set the value to fetch 100 rows. The RowPrefetching.php script provides a
complete example:
$sql = "SELECT id, pad FROM t";
$statement = oci_parse($connection, $sql);
oci_set_prefetch ($statement, 100);
oci_execute($statement, OCI_NO_AUTO_COMMIT);