ODP.NET Performance Improvement
Performance-related features include statement caching, connection pooling and control over fetch size and some other features.
Oracle Data Provider for .NET (ODP.NET) has proved to be the most effective and efficient solution for connecting Oracle Databases with .NET apps.
The provider has several embedded features that enable the applications to reduce resource consumption and thus run better and have better scalability. Here we will discuss three of these features, namely connection pooling, controlling fetch size, and statement caching.
I have performed a series of tests to find out if the mentioned features really affect application performance. All these tests were performed on Oracle Database and ODP.NET version 10.2.0.2. It should be noted that the statement caching feature was introduced in ODP.NET v. 10.1.0.3.
General Information on Connection Pooling
In some cases it turns out to be more effective for an application to have a number of precreated connections that can be brought up whenever needed than to establish such connections on demand, for the process of establishing a physical connection to the database is quite expensive being examined from the viewpoint of resources and time consumption. This approach is obviously more effective during the busiest time. The connection pooling feature of the ODP.NET enables applications to form and maintain a ready-to-use pool of connections.
It is possible to turn connection pooling on or off in the provider (it is on by default), simply change the “Pooling” connection string attribute value to TRUE for connection pooling on and to FALSE to switch connection pooling off. Detailed information on all other connection string parameters that are responsible for the configuration of connection pooling can be found in ODP.NET documentation.
With connection pooling switched on the provider creates the pool of connections basing on the connection string signature, which is formed according to the values of the connection string parameters. A new pool of connections is formed by ODP.NET for the application each time the connection string parameters are changed and a new connection is requested.
When several connection requests occur subsequently they use a connection from the pool instead of establishing a new one for each request. When the connection is not needed anymore, the Close of Dispose method is applied to it, resulting in the connection being returned to the pool instead of physical connection closure and destruction. It is important that the usage of the Dispose method is preferable for the non-pooled connections, for it explicitly frees the system resources.
A Practical Example of Connection Pooling
In this example we will use a special technique to evaluate the performance benefit of using connection pooling. In the code provided in the example available for download as JulyAug2006.cs and in Listing 1 two connection strings are created – one uses connection pooling, another does not. The code opens and loses connections in a loop, and the number of iterations is passed to the test method as a parameter.
The first connection string (connection pooling off, Pooling = FALSE) uses the sample username (HR) and the default password:
string no_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=false"
The second connection string (connection pooling on, Pooling = TRUE) also uses the sample username (HR) and the default password:
string with_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true"
The test mechanism works as follows: the main method calls the member methods, where one member method – ConnectionPoolTest – registers the current system time, starts the testing loop and registers the system time after the test loop is over. After that the difference between the two time marks is calculated.
I have performed the test with 100 iterations and received the following result:
Connection Pool Test with 100 iterations...
No Pooling: 7.8312608 total seconds.
With Pooling: 0.1301872 total seconds.
The results are impressive, the loop ran about 60 times faster with connection pooling on than it did with connection pooling off.
There can hardly exist some universal recommendations on the usage of connection pooling, for the workflow intensity varies greatly depending on numerous factors. Generally it should be noted that there’s no need to set low minimum pool sizes, for in the case when a rapid increase in application activity takes place many connections are to be created quickly. Many connection pools are configured to maintain a minimal set of connections during inactivity periods. Therefore, when the above mentioned activity burst occurs, the performance may suffer because of the pool not being able to create connections as fast as desired. Moreover in most cases application servers reduce the number of their connections and stay relatively idle. So, the minimal connection pool size is to be kept sufficiently high.
Fetch Size Control
One of the operations that affect performance most is retrieval of data from the database level to the middle (or client) level. Minimization of the number of round-trip data fetches is very important, especially if the end-user is consuming large amounts of data.
The ODP.NET default size of a data portion that is read from a result set at a time equals to 64KB. This value is stored in the FetchSize attribute of the OracleDataReader object and can be changed. ODP.NET provides yet another control capability that enables the developer to set the number of rows that are fetched from the result set per round trip. Two properties are used to control these parameters: FetchSize and RowSize.
The value of the RowSize property of the OracleCommand object is set by ODP.NET once the statement associated with this object is executed. The FetchSize property value can be explicitly set. So, 100 rows at a time from the result set per round trip can be retrieved if the FetchSize property is set as follows:
dr.FetchSize = cmd.RowSize * 100;
where dr stands for OracleDataReader and cmd stands for the OracleCommand object.
Benefits of Fetch Size Control
The benefits of controlling fetch size are demonstrated in the example below. Here a new table titled “fetch_test” was created using SQL*Plus in the sample HR schema:
create table fetch_test
(
id number primary key,
data varchar2(32)
);
and populated it with 100,000 rows:
begin
for i in 1..100000 loop
insert into fetch_test
values (i, to_char(i, '000000'));
end loop;
end;
/
This could also be done using a tool suite like Oracle Developer Tools for Visual Studio .NET. Remember that some tools will not issue COMMIT after populating the table so be sure to do this if you are using such a tool.
When the table was populated with data I gathered statistics for the optimizer so that it could figure out the most effective way to access the data. I used the dbms_stats package.
begin
dbms_stats.gather_table_stats(
ownname => 'HR',
tabname => 'FETCH_TEST',
estimate_percent => null,
method_opt => 'for all indexed columns size 1',
cascade => TRUE);
end;
/
The mechanism of the test I performed next is similar to the one of the test I used for connection pooling: the system time was registered, after that the appropriate value was set for the FetchSize property, data was read from the result set and the time was registered again. Next step was to calculate the difference between the two registered time values. A special method (FetchTest) was created which was responsible for reading the data and setting fetch size. The method accepted one integer type parameter – the number of rows to be fetched. This is how I read the data and set the fetch size:
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from fetch_test";
OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRows;
while (dr.Read())
{
rowsFetched++;
}
My next step was to execute the test method with different row values. The results are shown below:
ROWS TIME
________ __________________________
1 5.4378192 seconds
10 1.1115984 seconds
100 0.2703888 seconds
1000 0.200288 seconds
10000 0.1902736 seconds
The results show, that in the case when only one row at a time is fetched “to reduce resource consumption and increase the overall performance” (this is sometimes heard as an advice for developers), the performance is, in fact, very low. Along with that the results show that for this data set there is now significant performance increase after fetching more than 1000 rows. This method can be applied to your result set to figure out the optimal fetch size for it.
It is obvious, that the more fetch size is, the more memory and processor cycles will be needed to store and manage this amount of data. The main issue here is to find reasonable balance between the number of round-trips and the amount of data retrieved per trip.
Statement Caching
The usage of bind variables in applications has proved to be very effective. They enable the SQL statements to be reused by the Oracle Database in your session or in some other sessions that can use the same statement. (The advantages of bind variables are described in detail in Sep/Oct issue of Oracle Magazine, the ODP.NET column). It is possible to achieve an even greater performance increase from bind variables by using the Statement caching ODP.NET feature (for the applications, for which it is possible to predict on design stage which statements are going to be reused).
When statement caching is used Oracle Database keeps a copy of the statement that is to be reused in this session in the memory on the server. Thus when this statement is executed subsequently Oracle Database can perform faster. It should be noted, that the runtime performance of the statement itself is not affected by statement caching, i.e. it is neither increased nor decreased. Using statement caching enables Oracle Database to avoid the need to reparse the statement. Instead Oracle finds it in the memory area associated with the needed session (not all sessions). Note that the latest data is returned after execution of the cached statement, for the only thing cached is the statement itself, not the data.
Statement caching can be switched off or on by setting the string parameter named Statement Cache Size. The default state is off, which corresponds to the parameter value of 0. This value tells Oracle Database the number statements to be cached during application’s lifetime, i.e. to have 16 statements cached you need to set the parameter value of 16. In this case ODP.NET will capture the 16 most recently used statements and cache them. The 17th most recently used statement will be dropped down from the cache by the last 16 statements.
Sometimes it may be needed not to put this or that statement to statements cache, while ODP.NET caches all the statements executed. This can be done by setting the AddToStatementCache property of the OracleCommand object to FALSE:
OracleCommand cmd =
new OracleCommand();
cmd.Connection = con;
cmd.AddToStatementCache = false;
cmd.CommandText = "select data from fetch_test where id = :1";
SQL and PL/SQL statements can be stored in the statement cache.
Statements Caching Effect
The method I used to figure out the results of statements caching is similar to the one I used for connection pooling and fetch size control. I organized iterative data selection from the fetch_test table, first with statements caching disabled and than with statements caching enabled.
The connection string I used for testing with statements caching off is provided below:
string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true";
And here is the connection string for the test with statement caching on:
string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true;
Statement Cache Size=1";
I set the cache size to 1, because knew I will be caching only a single statement. The statement will remain cached irrespective of the change of the bind variable. Note the difference between the usage of bind and literal values: in the case with literal value Oracle Database would have to reparse the statement each time this value changed instead of taking it out of the cache. Additionally the new statement replaces the one previously put in the cache.
I have performed 1000 executions of the sample statement with statement caching disabled and the same number of executions with statement caching enabled. The results were as follows:
No Statement Caching:
0.6409216 total seconds.
With Statement Caching:
0.3905616 total seconds.
The most recent versions of ODP.NET turn statements caching on by default by using a special Windows Registry parameter. The cache size is set to 10. This setting affects all the applications using this instance of ODP.NET. However, this setting may be overridden by setting the appropriate value of the Statement Cache Size for each pool of connections.
Conclusion
The tests described in this article were performed on my laptop and both the client application and the database resided on this laptop. Such environment is very convenient for testing purposes; still it is better to perform tests in the environment with conditions similar to your production environment where the application will be functioning. Along with that I predict an even greater performance increase in the case the database and the client are hosted on separate systems. Obviously your tests are almost sure to demonstrate the results quite different from mine.
Performance benefits of the usage of ODP.NET features described in this article are not limited to reduced time consumption. Along with that traffic consumption and parsing in the database are reduced. It might be reasonable to work out testing and measurement techniques according to the peculiarities of your environment. ODP.NET features enable the developer to optimize the performance of the applications with minimum of effort.