One useful technique for increasing performance in your ODP.NET applications is controlling the fetch size. In fact, I discussed this technique in the ODP.NET column in the July/August 2006 issue of Oracle Magazine. In terms of performance, fetching the data from the database to the client tier (or a middle tier) can be one of the more expensive operations in an application’s execution. By controlling the fetch size (i.e. the amount of data fetched during each round-trip to the database) you can dramatically decrease (or increase!) the amount of time taken to fetch the data.
However, if you are working with Ref Cursors rather than "plain" SQL statements, the ability to control the fetch size is not readily available. This is because the RowSize property of an OracleCommand object is set to 0 (zero) when working with PL/SQL code, as would be the case with Ref Cursor data. A simple example will illustrate why this is so. The RowSize property is an integer property. Let’s say you have the following Anonymous PL/SQL code block associated with an OracleCommand object:
begin
open :1 for select * from scott.emp;
open :2 for select * from scott.dept;
open :3 for select * from scott.salgrade;
end;
Upon execution of this block, there will be three Ref Cursors opened. Therein lies the issue: A single integer property (RowSize) exposed by the OracleCommand object cannot properly represent the row size for the three opened Ref Cursors. Therefore, the property value is set to 0.
Typically you would create an OracleDataReader object for each of the Ref Cursors opened and read the data into the client. However, the OracleDataReader does not expose a RowSize property. Therefore, you can not set the FetchSize property on the OracleDataReader object to a specific number of rows using the normal technique of assigning the product of RowSize * NumberOfRowsToFetch to the FetchSize property. Keep in mind the RowSize property is 0 for the OracleCommand object and the OracleDataReader object does not expose a RowSize property.
However, if you have ever examined the members of the OracleDataReader in the debug window within Visual Studio, you may have noticed that the OracleDataReader does have a particularly enticing non-public member named "m_rowSize" which just happens to represent the row size. If only you could access that value you could then easily set the FetchSize property on the OracleDataReader to a specific (meaningful) value rather than be forced to accept the default of 64KB (65536). To see this, assume you have the following code:
OracleDataReader dr = cmd.ExecuteReader();
After executing this code while stepping through your application in the Visual Studio debugger, expand the "dr" node in the "Locals" window. Next navigate to the "Non-Public members" node, and expand this node. Scroll down the list and eventually you will find the "m_rowSize" member:
Now, of course, as this is a Non-Public member, you can’t just reference "m_rowSize" in your application code to retrieve this value. However, using the "reflection" capabilities of the .NET Framework, you can access this value. Once you have done that, you can set the value of the FetchSize property to whatever value is appropriate for your application.
Please note that this technique is not necessarily a supported technique, and, of course, if the "m_rowSize" member name should change, it would break any code using this technique - Caveat Emptor.
Here is a complete sample application using the SH sample schema. I used this schema simply because the SALES table is the largest table in the supplied sample schemas and, thus, is one that benefits from controlling the fetch size more than a small table (less than 64KB).
PL/SQL Code:
-- run as SH user
-- the SH user has the largest table in the demo schemas
create or replace procedure get_sales (p_refcur out sys_refcursor) is
begin
-- open the cursor using the passed in ref cursor
-- sys_refcursor is a built in type
open p_refcur for
select *
from sales;
end;
/
C# Application Code:
using System;
using System.Data;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace RefCursorFetchTest
{
class Program
{
// connection string -- be sure to adjust for your environment
public const string constr = "User Id=sh; Password=sh; Data Source=otndemo; Pooling=false; Enlist=false";
static void Main(string[] args)
{
// create and open connection
OracleConnection con = new OracleConnection(constr);
con.Open();
// enable extended sql tracing
// this can be used to verify the number of rows fetched
// uncomment to create trace file in user_dump_dest directory
// EnableExtendedTrace(con);
// do the fetch test
// passing 0 for "rows" will use default fetch size of 64k
FetchTest(con, 100);
FetchTest(con, 1000);
FetchTest(con, 10000);
FetchTest(con, 0);
// clean up
con.Dispose();
// prevent console window from automatically closing
Console.Write("ENTER to continue...");
Console.ReadLine();
}
static void EnableExtendedTrace(OracleConnection con)
{
// create, setup, and execute command to enable extended sql trace
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "alter session set events '10046 trace name context forever, level 4'";
cmd.ExecuteNonQuery();
// clean up
cmd.Dispose();
}
static void FetchTest(OracleConnection con, int rows)
{
// used to track execution duration
DateTime timeStart;
DateTime timeEnd;
double totalSeconds;
// create and setup command to call stored procedure
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "get_sales";
cmd.CommandType = CommandType.StoredProcedure;
// create the ref cursor parameter and add to command parameter collection
OracleParameter prm = new OracleParameter("p_refcur", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(prm);
// get the data reader
OracleDataReader dr = cmd.ExecuteReader();
// There are 3 steps in getting the m_rowSize property value...
// Step 1 - get the data reader type
Type type = dr.GetType();
// Step 2 - get the "m_rowSize" field info
FieldInfo fi = type.GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
// Step 3 - get the value of m_rowSize for the dr object
int rowSize = (int)fi.GetValue(dr);
// if rows is greater than 0 use to set fetch size,
// otherwise use default size (64k)
if (rows > 0)
{
// set the fetch size on the dr object using the row size
dr.FetchSize = rowSize * rows;
}
// capture test start time
timeStart = DateTime.Now;
// simply loop forcing the entire result set to be fetched
while (dr.Read())
{
}
// capture test end time
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for test
Console.WriteLine("Number of rows = {0,5}: {1,8:###.0000} total seconds, fetch size = {2,9:###,###,###} bytes.", rows, totalSeconds, dr.FetchSize);
// clean up
dr.Dispose();
prm.Dispose();
cmd.Dispose();
}
}
}
Execution results on my laptop:
Number of rows = 100: 10.3125 total seconds, fetch size = 20,800 bytes.
Number of rows = 1000: 4.9219 total seconds, fetch size = 208,000 bytes.
Number of rows = 10000: 2.4219 total seconds, fetch size = 2,080,000 bytes.
Number of rows = 0: 4.0625 total seconds, fetch size = 65,536 bytes.
ENTER to continue...
