January 20, 2012

ODP.NET : Using Fetch Size against Ref Cursors

We can increase the performance of our ODP.NET applications by controlling the fetch size of either OracleCommand or OracleDataReader. 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.

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 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).

As "m_rowSize" 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 a good coding approach, and, of course, if the "m_rowSize" member name should change, it would break any code using this technique.

The following sample code will illustrate the use of fetchsize in the case of Ref Cursors.
using(OracleConnection conn = new OracleConnection
(@"DataSource=source;User Id=username;Password=password;"))
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "StoredProcedureName";
        cmd.Parameters.Add("REF_CURSOR_SAMPLE",OracleType.RefCursor).Direction =  ParameterDirection.Output;
        OracleDataReader reader = cmd.ExecuteReader();
        FieldInfo fieldInfo = reader.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
        int cmdRowSize = (int)fieldInfo.GetValue(reader);
        reader.FetchSize = cmdRowSize * noOfRowsExpected;
             // do your logic here


  1. Are we using the ref cursor here? Is there any way to fetch the batches of rows?



    Connection Pool (CP)

    Ensure you have enough connections in CP – better to have many than too few.
    OS-authenticated CP available with ODP.NET 11g
    Keep a steady state of CP – never destroy or create large number of connections
    Close/Dispose connections explicitly – don’t rely on garbage collector
    You can monitor CP performance counters using ODP.NET and higher

    Bind Variables

    Always use Bind Variables. Using Bind Variables will prevent reparsing of frequently executed statements. Literal value changes

    in commands force a reparse. Reparsing is fairly expensive CPU operation and requires shared pool locks.

    Statement Caching

    Using statement caching retains parsed statement in shared pool.
    Cursor stays open on client side and Metadata remains on client
    Best used/works with Bind Variables
    Caching works with and caches the last 10 executed statements.
    Developer can choose which statement to cache.

    Statement Cache Size=0 (no caching)

    Statement Cache Size=1 (caching)

    With ODP.NET cache size dynamically changes at runtime and provides automatic optimization/self-tuning. Self-tuning is

    enabled by default and no code changes are required.

    Data Retrieval

    You can control how much data is retrieved from the database per round-trip. Too much data can cause excessive client-side memory

    used and too little may cause additional round-trips. You can use OracleCommand.RowSize and OracleDataReader.FetchSize to control

    the result. FetchSize can be set as multiple of RowSize and RowSize can be dynamicall populated after statement execution.

    FetchSize = RowSize X 1
    FetchSize = RowSize X 100

    Mass Data Movement with Arrays

    PL/SQL associative arrays can be used to pass large amounts of data between .NET and DB of the same data type.
    If you are executing the same statement multiple times, you could use a parameter array binding.

    Statement Batching

    You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
    You can use anonymous PL/SQL for disparate or similar statements

    ODP.NET DataTypes

    Try and avoid unnecessary datatype conversions between .NET and the DB
    Use OracleParameter.Dbtype

    public void CreateOracleDbParameter()
    OracleParameter parameter = new OracleParameter();
    parameter.ParameterName = "pDName";
    parameter.DbType = DbType.String;
    parameter.Value = "ENGINEERING";
    parameter.SourceColumn = "DName";

    Use REF Cursors

    With REF Cursors, you can retrieve data as needed and control data retrieved via FetchSize. You can fill a DataSet with just a

    portion of the REF cursor result. You can pass REF cursors back as input stored procedure parameters. Use OracleRefCursor class.

    Oracle Performance Tuning in Visual Studio

    You can tune ad-hoc SQLs in query window

    Tune bad SQL using Oracle Performance Analyzer – requires:

    SYSDBA privilege

    database license for Oracle Diagnostic Pack

    database license for Oracle Tuning Pack

    AWR and ADDM

    These are built into Oracle Database 10g and are invaluable for diagnosing Performance issues.

    AWR – Automatic Workload Repository

    Evolution of statspack

    builtin repository

    captures performance stats at regular intervals

    ADDM – Automatic Database Diagnostic Monitor

    Analyses AWR stats and generates recommendations

    AWR and ADDM nodes are now available in Visual Studio.