Thursday, May 10, 2007

ODP.NET Tip: Ref Cursors and FetchSize

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:

Image of m_rowSize in debugger

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


Sunday, May 06, 2007

ODP.NET Tip: Retrieving dbms_output Text

If you frequently work with PL/SQL code from your ODP.NET application you may have encountered code that uses the dbms_output package. This is a common debugging technique employed in PL/SQL code. In fact, the Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) has this to say about the dbms_output package:

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

In practice, calls to dbms_output are embedded in the PL/SQL code and then the resulting text is often displayed in a character-based tool such as SQL*Plus. But what if you are using an ODP.NET application? How is it possible to retrieve values into your client application that have been output by the dbms_output package into a server-side buffer?

Fortunately the dbms_output package provides two procedures that can be used to accomplish this:

- get_line
- get_lines

As their names suggest, get_line is used to retrieve a single line from the buffer while get_lines can be used to retrieve multiple lines from the buffer.

In this tip I show you how to use both of these procedures in a single sample application.

In order to retrieve a single line of text, you use a parameter object that represents a Varchar2 data type and a parameter object that represents a Decimal data type. The Varchar2 parameter holds the actual line of text while the Decimal parameter holds the status code returned from the dbms_output package.

The code to retrieve multiple lines of text also uses two OracleParameter objects: one parameter object is a PL/SQL Associative Array to hold the lines of text and the other is a Decimal parameter that holds the number of lines to fetch before the call to dbms_output or the number of lines that were fetched after the call to dbms_output.

Be sure to review the documentation for dbms_output linked above.

Begin by creating the PL/SQL code as the database user that will connect to the database from the ODP.NET application. Of course, this is just sample code to illustrate this technique. Your real code would perform additional activities and almost certainly would be more complex.

The PL/SQL code for the sample package and package body:

create or replace package dbms_output_test as

  procedure emit_single_line;

  procedure emit_multiple_lines;

end;

/

 

create or replace package body dbms_output_test as

  procedure emit_single_line is

  begin

    /* enable dbms_output using defaults */

    dbms_output.enable;

 

    /* output a single line of text */

    dbms_output.put_line('This is a simple line of text emitted by dbms_output.');

  end;

 

  procedure emit_multiple_lines is

  begin

    /* enable dbms_output using defaults */

    dbms_output.enable;

 

    /* output multiple lines of text */

    dbms_output.put_line('Sgt. Pepper''s Lonely Hearts Club Band Track List');

    dbms_output.put_line('================================================');

    dbms_output.put_line('01 - Sgt. Pepper''s Lonely Hearts Club Band');

    dbms_output.put_line('02 - With a Little Help From My Friends');

    dbms_output.put_line('03 - Lucy in the Sky With Diamonds');

    dbms_output.put_line('04 - Getting Better');

    dbms_output.put_line('05 - Fixing a Hole');

    dbms_output.put_line('06 - She''s Leaving Home');

    dbms_output.put_line('07 - Being for the Benefit of Mr. Kite!');

    dbms_output.put_line('08 - Within You Without You');

    dbms_output.put_line('09 - When I''m Sixty-Four');

    dbms_output.put_line('10 - Lovely Rita');

    dbms_output.put_line('11 - Good Morning Good Morning');

    dbms_output.put_line('12 - Sgt. Pepper''s Lonely Hearts Club Band (Reprise)');

    dbms_output.put_line('13 - A Day in the Life');

  end;

end;

/



As you can see, this code simply outputs text using the dbms_output package and performs no other processing. The emit_single_line procedure outputs a single line of sample text while the emit_multiple_lines procedure outputs the track listing for Sgt. Pepper's Lonely Hearts Club Band by The Beatles.

The C# sample code is fairly well commented and should be easy to follow:

using System;

using System.Data;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

 

namespace DbmsOutput

{

  class Program

  {

    // connect to default database using o/s authenticated account

    // be sure to adjust for your environment

    private const string constr = "user id=/; enlist=false; pooling=false";

 

    static void Main(string[] args)

    {

      // create and open connection

      OracleConnection con = new OracleConnection(constr);

      con.Open();

 

      // call method for single line output

      GetSingleLine(con);

 

      // call method for multiple line output

      GetMultipleLines(con);

 

      // clean up

      con.Dispose();

 

      // prevent console from closing automatically when run from within VS

      Console.Write("ENTER to continue...");

      Console.ReadLine();

    }

 

    static void GetSingleLine(OracleConnection con)

    {

      // call the procedure that emits a single line of text

      string stored_procedure = "dbms_output_test.emit_single_line";

 

      // anonymous pl/sql block to get the line of text

      string anonymous_block = "begin dbms_output.get_line(:1, :2); end;";

 

      // create command and execute the stored procedure

      OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = stored_procedure;

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.ExecuteNonQuery();

 

      // create parameters for the anonymous pl/sql block

      OracleParameter p_line = new OracleParameter("",

                                                  OracleDbType.Varchar2,

                                                  32000,

                                                  "",

                                                  ParameterDirection.Output);

 

      OracleParameter p_status = new OracleParameter("",

                                                    OracleDbType.Decimal,

                                                    ParameterDirection.Output);

 

      // set command text and parameters to get the text output

      // and execute the anonymous pl/sql block

      cmd.CommandText = anonymous_block;

      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add(p_line);

      cmd.Parameters.Add(p_status);

      cmd.ExecuteNonQuery();

 

      // write location, return status value, and the text to the console window

      Console.WriteLine("In method GetSingleLine...");

      Console.WriteLine("Return status: {0}", p_status.Value.ToString());

      Console.WriteLine("Return text: {0}", p_line.Value.ToString());

      Console.WriteLine();

      Console.WriteLine();

 

      // clean up

      p_line.Dispose();

      p_status.Dispose();

      cmd.Dispose();

    }

 

    static void GetMultipleLines(OracleConnection con)

    {

      // write location to console window

      Console.WriteLine("In method GetMultipleLines...");

      Console.WriteLine();

 

      // call the procedure that emits multiple lines of text

      string stored_procedure = "dbms_output_test.emit_multiple_lines";

 

      // anonymous pl/sql block to get multiples lines of text per fetch

      string anonymous_block = "begin dbms_output.get_lines(:1, :2); end;";

 

      // used to indicate number of lines to get during each fetch

      const int NUM_TO_FETCH = 8;

 

      // used to determine number of rows fetched in anonymous pl/sql block

      int numLinesFetched = 0;

 

      // simple loop counter used below

      int i = 0;

 

      // create command and execute the stored procedure

      OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = stored_procedure;

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.ExecuteNonQuery();

 

      // create parameter objects for the anonymous pl/sql block

      OracleParameter p_lines = new OracleParameter("",

                                                    OracleDbType.Varchar2,

                                                    NUM_TO_FETCH,

                                                    "",

                                                    ParameterDirection.Output);

 

      p_lines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

      p_lines.ArrayBindSize = new int[NUM_TO_FETCH];

 

      // set the bind size value for each element

      for (i = 0; i < NUM_TO_FETCH; i++)

      {

        p_lines.ArrayBindSize[i] = 32000;

      }

 

      // this is an input output parameter...

      // on input it holds the number of lines requested to be fetched from the buffer

      // on output it holds the number of lines actually fetched from the buffer

      OracleParameter p_numlines = new OracleParameter("",

                                                      OracleDbType.Decimal,

                                                      "",

                                                      ParameterDirection.InputOutput);

 

      // set the number of lines to fetch

      p_numlines.Value = NUM_TO_FETCH;

 

      // set up command object and execute anonymous pl/sql block

      cmd.CommandText = anonymous_block;

      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add(p_lines);

      cmd.Parameters.Add(p_numlines);

      cmd.ExecuteNonQuery();

 

      // get the number of lines that were fetched (0 = no more lines in buffer)

      numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();

 

      // as long as lines were fetched from the buffer...

      while (numLinesFetched > 0)

      {

        // write the text returned for each element in the pl/sql

        // associative array to the console window

        for (i = 0; i < numLinesFetched; i++)

        {

          Console.WriteLine((p_lines.Value as OracleString[])[i]);

        }

 

        // re-execute the command to fetch more lines (if any remain)

        cmd.ExecuteNonQuery();

 

        // get the number of lines that were fetched (0 = no more lines in buffer)

        numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();

      }

 

      // just a couple of separator lines

      Console.WriteLine();

      Console.WriteLine();

 

      // clean up

      p_numlines.Dispose();

      p_lines.Dispose();

      cmd.Dispose();

    }

  }

}



Executing the sample produces the following output in the console window:

In method GetSingleLine...

Return status: 0

Return text: This is a simple line of text emitted by dbms_output.

 

 

In method GetMultipleLines...

 

Sgt. Pepper's Lonely Hearts Club Band Track List

================================================

01 - Sgt. Pepper's Lonely Hearts Club Band

02 - With a Little Help From My Friends

03 - Lucy in the Sky With Diamonds

04 - Getting Better

05 - Fixing a Hole

06 - She's Leaving Home

07 - Being for the Benefit of Mr. Kite!

08 - Within You Without You

09 - When I'm Sixty-Four

10 - Lovely Rita

11 - Good Morning Good Morning

12 - Sgt. Pepper's Lonely Hearts Club Band (Reprise)

13 - A Day in the Life

 

 

ENTER to continue...



Happy coding...