Optimizing Data Access Performance with ODP.NET

Purpose

This tutorial describes how to optimize the performance of your .NET application using ODP.NET.

Time to Complete

Approximately 20 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Using Statement Caching
 Using FetchSize and RowSize
 Passing Array Parameters
 Using Batch SQL, REF Cursors, and Multiple Active Result Sets (MARS)
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

The Oracle database has numerous methods to optimize query execution and data retrieval from 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 performance tuning methods for ODP.NET developers.

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. 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 FetchSize and RowSize properties in ODP.NET. 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. At times, real-world results may be better or worse than the demonstration's results, depending on differences in set up, hardware, database configuration, etc. 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.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Microsoft Visual Studio 2008

2.

Install Oracle Database 10g or later or Oracle Database XE

3.

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.1.0.6.20 or later from OTN

4.

Download and unzip the codesnippets.zip file into your working directory.

Back to Topic List

Using Statement Caching

Statement caching is beneficial when you need to repeatedly execute the same SQL or PL/SQL statement. ODP.NET will cache the most recently used statements. The developer decides the number and which of the most recently statements to cache. Cached statements improve performance by retaining the statement's parse tree and allowing it to be quickly looked up. To use ODP.NET statement caching, perform the following steps:

1.

Open Visual Studio. Select File > Open Project. Navigate to the directory where you unzipped the files in the prerequisites, select the ODP.NET Performance Best Practices solution and click Open.

 

2.

Select View > Solution Explorer.

 

3.

Double-click ODP.NET Best Practices.cs to open the code for this tutorial.

 

4.

Review your connection string to allow the ODP.NET application to connect to Oracle's sample HR schema. You may have to modify the Password and Data Source values for the conString String variable.

Note: If you are accessing a remote Oracle Database instance, then you need to specify the Data Source in //<hostname>/<SID> format.

Scroll down to the Demo 1 section of the code. Note, the code file is interspersed with comments to mark areas where you need to enter code in the following steps. So, make sure you enter the relevant code under the relevant comment section. This is done to ensure the code is entered in proper sequence and to avoid mixing up of code in the file.

 

5.

To see how statement caching works, you first want to execute a statement 10000 times with Statement Cache Size set to 0.

Set the Statement Cache Size to 0. Append the following code to the con1.ConnectionString statement, as shown in the screenshot below:

"Statement Cache Size=0";

 

6.

Further down in the same file, the same code is executed, but with statement caching on. Set Statement Cache Size to 1 and re-execute the statement 10000 times. Scroll down and append the following code to the con2.ConnectionString statement, as shown in the screenshot below:

"Statement Cache Size=1";

The application calculates the performance percentage improvement with caching enabled for the statement.

  

 

7.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

The output window should not show any errors or warnings. The code should compile successfully.

 

8.

Select Debug > Start Without Debugging or press Ctrl+F5.

A command window opens. Wait for the output. Do not close the window.

 

9.

Note that your results may differ slightly from the results in the screenshot.

  

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. To enable statement caching across all your ODP.NET applications, you can enable statement caching in the Windows Registry as well.

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

 

Back to Topic List

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 only half a row of data per round trip when the end user needs to use the entire row. Instead of making two round trips, one round trip should have been made for optimum performance. ODP.NET allows developers to discover automatically the query row size and then specify the 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.

You first execute a query with FetchSize set to 1 row on the OracleDataReader.

Note: When ODP.NET executes an OracleCommand, it first retrieves metadata on how large the row size is and populates the RowSize property. A developer can then set the number of rows to retrieve from the database per round trip by multiplying RowSize by that number. In this example, you are only retrieving one row per round trip.

Scroll down to the Demo 2 section of the code. Locate the for loop. Enter the following code:

reader.FetchSize = cmd.RowSize * 1;

  

 

2.

Since RowSize is determined at runtime, the query or underlying schema can change, but the same number of rows will still be retrieved per round trip. You can compare time difference between when FetchSize is set to 100 rows in this step and the time when FetchSize was set to one row.

Scroll down to the next for loop. Enter the following code:

reader.FetchSize = cmd.RowSize * 100;

  

 

3. Note: You can comment out the for loops in the Demo 1 section you ran in the previous topic to avoid spending time on the demo you already executed. Commenting out just the loops will not affect the results for the remaining tutorial topics. It saves you time from repeatedly executing the loops again.

If you do not comment the for loops in the Demo 1 section, then press Enter after you get Demo 1 section's results to continue program execution for this section.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

 

4.

Select Debug > Start Without Debugging or press Ctrl+F5.

 

5.

Note that your results may differ slightly from the results in the screenshot.

  

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

Back to Topic List

Passing Array Parameters

In this topic, you learn how to pass array parameters between .NET and Oracle. Array parameters allow large amounts of data 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.

In the Solution Explorer, right-click array.sql and select Open.

  

 

2.

Review the procedure. Note that a PL/SQL package MYPACK is created with a PL/SQL stored procedure MYSP. PL/SQL must be used since a PL/SQL associative array is used. The stored procedure has a PL/SQL associative array input parameter that just passes its values back as an output PL/SQL associative array. Select all the code in the file and select ctrl+c to copy it to the clipboard.

  

 

3.

Select View > Server Explorer.

  

 

4.

You need to create a data connection for the HR user. You can review how to do this in Oracle Developer Tools for Visual Studio tutorial. Right-click the HR.ORCL connection and select Query Window. In this example, ORCL is the database alias, which may be different from the database alias on your system.

  

 

5.

Press [Ctrl]+V to paste the code in the Query Window. Select all the text in the Query Window and click Execute.

  

 

6.

Once the stored procedure has executed successfully, switch back to ODP.NET Best Practices.cs code to review the code.

  

 

7.

Scroll down to Demo 3 section. Enter the following code:

                                 
                                   
cmd = new OracleCommand("MYPACK.MYSP", con);
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;
                                  
                                
                              

Note: ODP.NET sets up the call to the MYPACK.MYSP stored procedure and binds its array parameters. The arrays' elements contain values of data type Varchar2. To indicate that arrays are passed in and out of the stored procedure, ODP.NET sets the parameters as PL/SQL associative arrays, which is an Oracle database collection data type.

  

 

8.

Now, setup the parameter direction (input or output) and then specify the value for each parameter. Set the param2 to NULL.

Enter the following code:

param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Output;
param1.Value = new string[3]{"Oracle", "Database", "Rules"};
param2.Value = null;

  

 

9.

Then you specify the maximum number of elements in the arrays (in this case, 3) and the maximum size of the varchar2 (in this case, 20 characters).

Enter the following code:

param1.Size = 3;
param2.Size = 3;
param1.ArrayBindSize = new int[3]{20,20,20};
param2.ArrayBindSize = new int[3]{20,20,20};

To execute the stored procedure and output the results, enter the following code:

cmd.ExecuteNonQuery();

                                 
                                   
for(int i=0; i<3; i++)
{
Console.Write((param2.Value as OracleString[])[i]);
Console.WriteLine();
}
                                
                              

 

10. Note: You can comment out the for loops in the Demo 1 and Demo 2 sections you ran in the previous topics to avoid spending time on the demos you already executed. Commenting out just the loops will not affect the results for the remaining tutorial topics. It saves you time from repeatedly executing the loops again.

If you do not comment the for loops in the Demo 1 and Demo 2 sections, then press Enter twice(after results for previous demos are shown) to continue program execution for this section.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

 

11.

Select Debug > Start Without Debugging or press Ctrl+F5.

 

12.

Examine the results.

Do not press Enter. To continue program execution, you add code in the program in the subsequent topic. Close the command window.

Back to Topic List

Using Batch SQL, REF Cursors, and Multiple Active Result Sets (MARS)

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

In this topic, you use anonymous PL/SQL to execute three queries in a single database round trip. To retrieve the results, you 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. Lastly, you see that ODP.NET can maintain multiple active result sets open for the same connection. Perform the following steps:

1.

For ODP.NET to batch SQL, anonymous PL/SQL is used.

Note: Anonymous PL/SQL is just a string that starts with a "BEGIN" and terminates with an "END;". In between lies the SQL that needs to be executed in a single round trip.

In this example, you batch three queries to return three result sets. Parameters are bound so that ODP.NET can return the query results to the client. Oracle executes anonymous PL/SQL like any other text command. As such, the command is bound as CommandType.Text.

Scroll down to the Demo 4 section of the code. Enter the following code:

                                 
                                   
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;

 

2.

You then bind three output REF Cursors to the command. In this case you select employees in department 10, 20 and 30 each in a different REF Cursor.

Enter the following code:

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;



3.

Execute the batched statements and then retrieve the data from the 2nd and 3rd parameter without having to fetch the results from the first parameter. This is one of the benefits of using REF Cursors whereby 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 that both OracleDataReaders are actively reading data at the same time.

Enter the following code after the relevant comments:

cmd.ExecuteNonQuery();

OracleDataReader dr1 = ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();
OracleDataReader dr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();

Add a while loop to retrieve both DataReaders at one time to test if MARS works . Enter the following code to output the results of the two REF Cursors:

                                 
                                   
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();
}
                                
                              

 

4.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

 

5.

Select Debug > Start Without Debugging or press Ctrl+F5.

 

6.

Examine the results.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Use statement caching
 Use fetchsize and rowsize
 Pass array parameters
 Use batch SQL, REF Cursors, and Multiple Active Result Sets (MARS)

Back to Topic List

 Move your mouse over this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document