Optimizing Data Access Performance with ODP.NET


Options



Before You Begin

Purpose

This tutorial demonstrates several coding techniques to enhance ODP.NET data access performance. These .NET techniques are mostly exclusive for Oracle database development.

Time to Complete

Approximately 30 minutes

Background

The Oracle database has numerous methods to optimize query execution and data retrieval to clients. ODP.NET enables these performance optimizations, allowing .NET developers to build more efficient database programs. This tutorial covers some of the most commonly used ODP.NET performance tuning methods.

The first section discusses statement caching. Statement caching eliminates the need to re-parse each executed SQL or PL/SQL statement by caching server cursors created during the initial statement execution. Subsequent executions of the same statement can reuse the parsed information from the cursor, and then execute the statement without re-parsing for better performance. In order to see performance gains from statement caching, Oracle recommends caching only those statements that will be repeatedly executed. The newest ODP.NET versions automate this task by self-tuning the statement cache. Furthermore, SQL or PL/SQL statements should use parameters rather than literal values. Doing so takes full advantage of statement caching since parsed information from parameterized statements can be reused even if the parameter values change in subsequent executions.

The second section shows how to control the amount of data retrieved per database round trip by using the ODP.NET FetchSize and RowSize properties. It can be inefficient to make multiple round trips to retrieve one distinct set of data. These two properties allow developers to fine-tune the amount of data to retrieve from the database server so that fewer database round trips need to be used.

The third section demonstrates how to pass a PL/SQL associative arrays between ODP.NET and the Oracle database. PL/SQL associative arrays allow Oracle to pass large amounts of data of the same datatype to .NET arrays. This method provides a flexible and easy way to batch data together in a single parameter to pass between the two tiers as needed.

The fourth section shows how to batch SQL, use the Oracle REF Cursor, and demonstrates multiple active result sets (MARS). Batching SQL allows developers to execute multiple SQL statements in a single database round trip. The Oracle REF Cursor is a data type unique to the Oracle database. It provides unique result set handling capabilities that allow for flexible data handling and tuning. MARS has been a feature that Oracle has always supported. This allows for a single connection to have more than one result set active at the same time.

Note: Performance gains seen in this demonstration may not necessarily reflect results seen in production environments. Real-world results may be better or worse than the demonstration's results, depending on set up, hardware, database configuration, etc. differences. For example, if you run this demonstration with the client and server on the same machine, improvements gained from reducing the number of database round trips will be greater in a production environment where the client and server are generally on two different machines.

This tutorial uses ODP.NET, Unmanaged Driver, but you can also use these same techniques with ODP.NET, Managed Driver.

What Do You Need?

Before starting this tutorial, you should:
  • Install Microsoft Visual Studio 2015 or later.
  • Install Oracle Database 12c or later.
  • Install Oracle Data Access Components (ODAC) 12c Release 4 or later from OTN. The ODAC download includes Oracle Developer Tools for Visual Studio and ODP.NET that will be used in this lab.
  • Extract these OptimizingDotNet files into your working directory.

Using Statement Caching

Statement caching is beneficial when you repeatedly execute the same SQL or PL/SQL statements. By default, ODP.NET will cache the most recently used statements and will automatically adjust the cache size to accommodate the number of statements being frequently executed. Cached statements improve performance by retaining the statement's parse tree and allowing it to be quickly looked up. Because statement caching is on by default, there is no setup required. This part of the tutorial will merely demonstrate statement caching's benefit by turning it on and off explicity. Perform the following steps

  1. Open Visual Studio 2015. Select File > Open > Project/Solution.

    Oracle OBE
    Description of this image
  2. Navigate to the OptimizingDotNet files you have downloaded in your working directory as part of the prerequisites and select the ODP.NET Performance Best Practices.sln solution and click Open.

    Oracle OBE
    Description of this image
  3. Select View > Server Explorer

    Oracle OBE
    Description of this image
  4. Select View > Solution Explorer

    Oracle OBE
    Description of this image
  5. Double click open the ODP.NET Best Practices.cs file in the solution explorer.

    Oracle OBE
    Description of this image
  6. Observe the screen. ODP.NET Best Practices.cs file opens up.

    Oracle OBE
    Description of this image
  7. Add the following code inside the main method, to create a connection string. Modify the Data Source value as needed for your specific deployment. You will need to connect to an Oracle Database with the HR sample schema.

    string conString = "User Id=hr; Password=hr; Data Source=orcl; Pooling=false;";
    Oracle OBE
    Description of this image
  8. Enter the following code. This will set the cache size to 0. This is to see how quickly statements execute with statement caching off. You will execute a statement 10,000 times with the statement cache set to 0.

    con.ConnectionString = conString + "Statement Cache Size=0";
    Oracle OBE
    Description of this image
  9. Enter the following code. This will set the cache size to 1. This is to see how query executions perform with statement caching on. You will execute a statement 10,000 times with the statement cache set to 1. This will give a comparative execution with statement cache as 0 and 1.

    con.ConnectionString = conString + "Statement Cache Size=1";
    Oracle OBE
    Description of this image
  10. Build the code. Select Build > Build Solution.

    Oracle OBE
    Description of this image
  11. Run the code. Select Debug > Start Without Debugging.

    Oracle OBE
    Description of this image
  12. Observe the output.

    Oracle OBE
    Description of this image
    As you can see, there can be a substantial performance improvement when using statement caching for SQL or PL/SQL statements that are repeatedly executed.

    Note: Do not press Enter. Close the command window. To continue program execution, add code in the program in subsequent topics.

Using FetchSize and RowSize

Controlling the amount of data fetched per database round trip can optimize application performance. For example, it is inefficient to retrieve five rows of data per round trip when the end user needs to use ten rows. Instead of making two round trips, one round trip should have been made for optimum performance. ODP.NET provides the query row size via the RowSize property, allowing developers to then specify a FetchSize using a set number of rows to be retrieved per round trip. This feature makes optimizing data retrieval much simpler for .NET programmers.

In this topic, you run a query that fetches one row at a time and then run the same query that fetches 100 rows at a time to see the performance improvement from making fewer round trips. Perform the following steps:

  1. Scroll down to Demo 2 section of the code. Set the DataReader FetchSize to retrieve 1 row of data per round trip using the following code, in the first for loop.

    reader.FetchSize = cmd.RowSize * 1;
    Oracle OBE
    Description of this image
  2. Set the DataReader FetchSize to retrieve up to 100 rows per round trip using the following code, in the second for loop.

    reader.FetchSize = cmd.RowSize * 100;
    Oracle OBE
    Description of this image
  3. Build and run the code. Observe the output.

    Oracle OBE
    Description of this image
    Note: Since RowSize is determined at runtime, the query or underlying table can change, but the same number of rows will still be retrieved per round trip. Compare the time difference between when FetchSize is set to 100 rows in this step and the time when FetchSize was set to one row.

Passing Array Parameters

In this topic, you learn how to pass array parameters between .NET and Oracle Database. Array parameters allow large amounts of data of the same type to be passed as a single parameter. This example uses a stored procedure that takes an input array parameter and returns an output array parameter.

  1. Open the array.sql file, in the Solution Explorer

    Oracle OBE
    Description of this image
  2. Review the procedure. Press Ctrl+A to select all and Ctrl+C to copy the contents to clipboard.

    Oracle OBE
    Description of this image
  3. Go to Server Explorer. Right click the Data Connections > Add Connection.

    Oracle OBE
    Description of this image
  4. In the Add Connection Dialog, enter the following details and click OK.

    • Data Source: Oracle Database (ODP.NET, Managed Driver)
    • User Name: hr
    • Password: hr
    • Data Source Name: ORCL

    This part of the tutorial does not require using the HR schema specifically. You may use any schema you'd like. Modify the Data Source value as needed for your specific deployment.

    Oracle OBE
    Description of this image
  5. Right click the newly created database connection (i.e. HR.ORCL) and select Query Window.

    Oracle OBE
    Description of this image
  6. Paste the procedure from array.sql in the Query window. Select all the contents inside the query window using Ctrl+A. Click the Execute Query button, which will create the stored procedure.

    CREATE or replace PACKAGE MYPACK AS
    TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
              PROCEDURE MYSP(
                Param1 IN     AssocArrayVarchar2_t,
                Param3    OUT AssocArrayVarchar2_t);
              END MYPACK;
    /
    CREATE or REPLACE package body MYPACK as 
      PROCEDURE MYSP(
      Param1 IN     AssocArrayVarchar2_t,
      Param3    OUT AssocArrayVarchar2_t)
      IS
      BEGIN
        Param3(1) := Param1(1);
        Param3(2) := Param1(2);
        Param3(3) := Param1(3);
      END MYSP;
    END MYPACK;
    /
    Oracle OBE
    Description of this image
  7. Observe the query output on the window below.

    Oracle OBE
    Description of this image
  8. Navigate to the Demo3 section in the cs file. Review the code.

    // Demo 3: Passing Array Parameters
    // Let's pass array parameters between .NET and Oracle
    // This stored procedure takes an input array and copies its
    // values to the output array
    
    cmd = new OracleCommand("MYPACK.MYSP", con);
    con.ConnectionString = conString;
    cmd.CommandType = CommandType.StoredProcedure;             
    OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2); 
    OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2); 
    param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    
    //Setup the parameter direction
    //Note that param2 is NULL
    
    param1.Direction = ParameterDirection.Input;
    param2.Direction = ParameterDirection.Output;
    param1.Value = new string[3] { "Oracle", "Database", "Rules" };
    param2.Value = null;
    
    //Specify the maximum number of elements in the arrays
    // and the maximum size of the varchar2
    param1.Size = 3;
    param2.Size = 3;
    param1.ArrayBindSize = new int[3] { 20, 20, 20 };
    param2.ArrayBindSize = new int[3] { 20, 20, 20 };
    
    //Execute the statement and output the results
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    
    for (int i = 0; i < 3; i++)
    {
        Console.Write((param2.Value as OracleString[])[i]);
        Console.WriteLine();
    }
    
    Console.WriteLine();
    Console.WriteLine("Press 'Enter' to continue");
    Console.ReadLine();
    
    //Demo 3 ends here
    Oracle OBE
    Description of this image
  9. Build and run the code. Observe the output.

    Oracle OBE
    Description of this image

Using Batch SQL, REF Cursors and Multiple Active Result Sets

Many times, you want to execute multiple SQL statements in a single batch to reduce database round trips. In ODP.NET, this is accomplished via anonymous PL/SQL syntax. Anonymous PL/SQL can be used to batch any number of queries, updates, inserts, and, deletes.

Most anonymous PL/SQL is just a string of text following PL/SQL syntax that contains a "BEGIN" keyword and terminates with an "END;". Within these keywords lie the SQL statements to execute in a single round trip.

In this topic, use anonymous PL/SQL to execute three queries in a single database round trip. To retrieve the results, use Oracle REF Cursors to show how data can be flexibly retrieved from the database. With REF Cursors, you can choose to retrieve only data you require from the result set and when you want to retrieve it. Lastly, observe that ODP.NET can maintain multiple active result sets open for the same connection. Perform the following steps:

  1. Navigate to the section 4 of the C# file. Observe the anonymous PL/SQL block with three queries to be batched.

    // Demo 4: Batch SQL, REF Cursors, and MARS
    // Anonymous PL/SQL block embedded in code - executes in one 
    // DB round trip
    // And why don't we try out MARS in Oracle as well 
    
    string cmdtxt = "BEGIN " + 
        "OPEN :1 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 10; " + 
        "OPEN :2 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 20; " + 
        "OPEN :3 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 30; " + 
        "END;";
    
    cmd = new OracleCommand(cmdtxt, con);
    cmd.CommandType = CommandType.Text;
    con.ConnectionString = conString;
    Oracle OBE
    Description of this image
  2. Observe the output REF cursor parameters bound to the command.

    // ODP.NET has native Oracle data types, such as Oracle REF 
    // Cursors, which can be mapped to .NET data types
    // Bind REF Cursor Parameters for each department
    OracleParameter p1 = cmd.Parameters.Add("refcursor1", OracleDbType.RefCursor);
    p1.Direction = ParameterDirection.Output;
    OracleParameter p2 = cmd.Parameters.Add("refcursor2", OracleDbType.RefCursor);
    p2.Direction = ParameterDirection.Output;
    OracleParameter p3 = cmd.Parameters.Add("refcursor3", OracleDbType.RefCursor);
    p3.Direction = ParameterDirection.Output;
    
    Oracle OBE
    Description of this image
  3. Observe the code. The loop is to test if MARS works.

    // Execute batched statement con.Open(); 
    con.Open();
    cmd.ExecuteNonQuery();
    
    // Let's retrieve data from the 2nd and 3rd parameter without 
    // having to fetch results from the first parameter
    // At the same time, we'll test MARS with Oracle
    
    OracleDataReader dr1 = ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();
    OracleDataReader dr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();
    
    // Let's retrieve both DataReaders at one time to test if 
    // MARS works
    while (dr1.Read() && dr2.Read())
    {
        Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " +
            "Employee Dept:" + dr1.GetDecimal(1));
        Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " +
            "Employee Dept:" + dr2.GetDecimal(1));
        Console.WriteLine();
    }
    con.Close();
    Console.WriteLine("Press 'Enter' to continue");
    Console.ReadLine();
    //Demo 4 ends here
    Oracle OBE
    Description of this image
    Note: The benefit of using REF Cursors is that you can retrieve data only when the client requires it. With REF Cursors, you can selectively choose data within a result set to fetch as well. With MARS, you can observe both OracleDataReaders active read data at the same time in the while loop.
  4. Build and run the code. Observe the output.

    Oracle OBE
    Description of this image

Want to Learn More?