Oracle performance programming: .NET

In Oracle SQL High Performance tuning, I included an appendix in which I outlined how to achieve good cursor management, bind variables and array processing in the major languages of the day.   I had intended to do the same in Oracle Performance Survival Guide, but I ran out of time and space in the book.  So the examples in the book are generally in Java or PL/SQL only.

I wanted to get up to date on the various languages, some of which (like Python) I haven't used for a while and others (Ruby for instance) I've never used with Oracle.  So I thought I'd kill two birds with one stone by writing a series of blog posts on how to program efficiently in the various languages.

There's lots of best practices in each language, but I think most of us would agree that you at least need to know how to do the following:

1. Use bind variables appropriately.
2. Ensure you are performing array fetch when retrieving more than one row
3. Ensure that you use array insert when doing bulk inserts 

The ways of doing this are different in each language.  In Chapter 6 of Oracle Performance Survival Guide,  I describe these techniques and their performance implications, using Java and sometimes PL/SQL examples.  In this series I'll go through the techniques for other languages. 

Let's start with ODP.NET, which is Oracle's ADO.NET driver for .NET languages such as C#, VB.NET and Powershell (see here for a posting on using Oracle with powershell). 

You can get the C# program which contains the code snippets below here. 

Bind variables

As in most languages, it's all to easy to omit bind variables in C#.  Here's an example where the variable value is simply concatenated into a SQL string.  The values to be fetched are obtained from an array "xvalueList":

Every value of xvalue will generate a unique SQL which will have to be parsed (unless the database parameter CURSOR_SHARING is set to FORCE or SIMILAR).

To use bind variables, we need to make 3 changes to the code.  The changes are shown in the code below:

• Firstly, we define the bind variable "xVal" in the text string that represents the SQL statement (line 231).
• Next, we create an OracleParameter object representing the bind variable and assign it a datatype (line 233).  We need to do this for every bind variable we use.
• Finally,  we assign the parameter value to the value of the program variable that we want to use (line 237). 

As with any programming language, using bind variables improves performance by reducing parse overhead and library cache latch or mutex contention.  The above SQL is about as simple as you can get, so parse overhead reductions should be modest.   As the only active session on my (local laptop) database,  library cache contention would not be expected either.  Nevertheless,    elapsed time even in this simple example reduced by about 2/3rds when bind variables were used. 

Array Select

Fetching rows in batches generally reduces elapsed time for bulk selects by up to a factor of 10.  Array fetch works to improve performance by reducing network round trips and by reducing logical reads. 

Array fetch is turned on by default in ODP.NET.  The size of the array is determined by the FetchSize property of the OracleCommand object.  It can also be set in the OracleDataReader object.   The value is specified as the number of bytes allocated to the array, not the size of the array.  This makes it tricky to set an exact array fetch size unless you know the row length.   However, you can get the row length once the statement is executed by examining the RowSize parameter.

The default array size is 64K which will generally be adequate for good performance.  Below we explicity set the fetch size to the size specified by the variable myFetchSize: 

You won't normally need to adjust FetchSize.  Below you can see the effect of adjusting the FetchSize to 64 - the size of a single row for this table - and to 6400 - the size of 100 rows: 

 Array insert

Array insert has similar performance implications as array fetch, but it harder to program and does not happen automatically.   Firstly, the rows to be inserted must exist within a .NET array variables.  The following declares and populates an array of two integer variables that we are going to insert:

The array is bound to bind variables as usual.  On lines 167-170 below we create the Oracle Parameter objects for the bind variables. and on lines 172-173 we assign the arrays to the bind variables.  On line 176 we set the number of rows to be inserted in the array insert;  we've set it to be equal to the number of values in the array.  

We just need to perform one insert - on line 177. 

Below we see the performance improvements between inserting in arrays and inserting row by row.  In this case, the database was my local laptop so the network latency was truly minimal.  Even bigger performance improvements can be observed when the database is across a slow network link:

 

Conclusion

There's lot's of advanced techniques for working with ADO.NET, but at the very least you should be familiar with bind variables and array processing.  Of the three, only array fetch is implemented by default and the performance issues that can result from a failure to use all three can be truly remarkable. 

Comments

[JeffS]

Guy, you should re-post this up on ToadWorld, it's great stuff!

[kiquenet]

A number of blogs and sites mention increasing FetchSize of OracleDataReader to improve performance when fetching big volumes of

data (e.g. thousands of rows). There are some documented experiments with exact numbers on this like:

http://metekarar.blogspot.com/2013/04/performance-improvement-for-odpnet.html

Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill

data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can

reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from

database. Here is how it's impletemented:

using (OracleConnection conn = new OracleConnection())
{
OracleCommand comm = new OracleCommand();
comm.Connection = conn;
comm.FetchSize = comm.FetchSize * 8;
comm.CommandText = "select * from some_table";

try
{
conn.Open();
OracleDataAdapter adap = new OracleDataAdapter(comm);
System.Data.DataTable dt = new System.Data.DataTable();
adap.Fill(dt);
}
finally
{
conn.Close();
}
}

Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net 11.2.0.3.

OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.

What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:

Fetch Size Timing (MI:SS.FF3)
Default (128 KB) 05:20.290
Default x 8 (1 MB) 00:52.941
Default x 32 (4 MB) 00:26.008
Default x 64 (8 MB) 00:12.409

http://metekarar.blogspot.com.es/2013/04/performance-improvement-for-odpnet.html

Strangely, unless the connection pooling is explicitly disabled (e.g. in the connection string), the increase/decrease of FetchSize

stops having any effect. When the pooling is disabled though, it's clear that the FetchSize can improve the performance (the more

records, the bigger the effect).

It turns out that this unexpected behavior is limited to the following conditions: 1) The SELECT statement is exactly the same 2)

The pooling is ON 3) The self-tuning is ON

Only in those conditions the first time the FetchSize is set, it gets somehow cached by ODP.NET and attempts to change it don't

work.

References Mark Williams
http://www.oracle.com/technetwork/issue-archive/2006/06-jul/o46odp-097508.html

string with_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true"

Connection Pooling Overview

Making specific connection pooling parameter recommendations is difficult, because workloads can vary greatly. One general

recommendation is to have a sufficiently high minimum pool size. I have often seen connection pools drained to the minimum during a

lull in activity. When activity picks up rapidly, the connection pool has to create connections quickly rather than use connections

in the pool. In most cases, the application server reduces the number of connections and remains relatively idle. Thus, there is no

reason to have a low minimum pool size.

Controlling Fetch Size

Retrieving data from the database tier to the middle tier (or client tier) is one of the most expensive operations with respect to

performance. If the end user consumes a lot of data, you will want your application to minimize the number of round-trip data

fetches.

By default, ODP.NET will read 64KB of data from a result set at a time. You change this value by setting the FetchSize attribute for

an OracleDataReader object. However, rather than arbitrarily setting the fetch size, ODP.NET provides the ability to control the

number of rows that are fetched from a result set per round trip. The two properties you use to do this are RowSize and FetchSize.

RowSize is a property of the OracleCommand object, and the value for this property is assigned by ODP.NET when the statement

associated with the command object is executed. You explicitly set the value of the FetchSize property. For example, if you want to

retrieve 100 rows at a time from the result set per round trip, you set the FetchSize property:

dr.FetchSize = cmd.RowSize * 100;

if you are tempted to fetch only a single row at a time to "save resources and increase performance" (I have sometimes heard this

advice), you will actually be dramatically reducing, rather than increasing, runtime performance. In addition, for this set of data,

there is not much performance increase when fetching more than 1,000 rows at a time. Of course, this is also a narrow data set (with

only two small columns). Apply this technique to a result set representative of your environment to determine optimal values for

setting your fetch size.

Of course, there is a cost if the fetch size is arbitrarily large. More client-side memory and processor cycles will be needed to

store and manage a larger amount of data. The goal is to find a high-performing balance between the number of round trips and the

amount of data retrieved per trip.

Statement Caching

You enable the statement caching feature by setting the Statement Cache Size connection string parameter. The default value of this

parameter is 0, meaning that statement caching is disabled by default. You set this parameter to the number of statements you plan

to keep cached during your application's lifetime. For example, if you plan to cache 16 statements, you will set this value to 16.

ODP.NET will then cache the 16 most recently used statements. The 17th most recently used statement is aged out of the cache by the

last 16 unique statements.

With statement caching enabled, ODP.NET will automatically cache any statement you execute. If you have enabled statement caching

and you do not want to place a statement in the cache, set the AddToStatementCache property on OracleCommand to false:

string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true;
Statement Cache Size=1";

http://oradim.blogspot.com.es/2007/05/odpnet-tip-ref-cursors-and-fetchsize.html

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;

// 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";

// 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);

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

// 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);

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

}

}

}