Nvidal’s Blog

Just another WordPress.com weblog

Oracle Data Provider for .NET Best Practices

Applies to:

Oracle Data Provider for .NET – Version: 9.2.0.1.0 to 11.1.0.7.0
Microsoft Windows 2000
Microsoft Windows XP (64-bit Itanium)
Microsoft Windows Server 2003 (64-bit Itanium)
Microsoft Windows XP (64-bit AMD64 and Intel EM64T)
Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)
Microsoft Windows (64-bit) on Intel Itanium
Microsoft Windows Vista (32-bit)
Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)
Microsoft Windows Server 2003 R2 (32-bit)
Microsoft Windows Vista x64 (64-bit)
Microsoft Windows Server 2008 (32-bit)
Microsoft Windows Server 2008 (64-bit Itanium)
Microsoft Windows Server 2008 x64 (AMD64/EM64T)
Microsoft Windows (32-bit)
Microsoft Visual Studio .Net 2003
Microsoft Visual Studio .Net 2005
Microsoft Visual Studio .Net 2008
.Net Framework 1.1
.Net Framework 2.0
.Net Framework 3.0

Purpose

Best Practices for getting the best performance and results from the Oracle Data Provider for .Net. It covers the following sections:

Connections and Connection Pooling

Command Objects – SQL Performance

Garbage Collector (GC) and Disposing

Miscellaneous Other Tips

Scope and Application

This is intended advanced programmers with expertise in Microsoft Visual Studio programming.

Oracle Data Provider for .Net Best Practices

Oracle ODP .Net Performance Best Practices

1. Connections and Connection Pooling

The following two connection pool parameters are available for configuring the size of the middle-tier ODP pool within your application pool.

Min pool size

Max pool size

It is recommended that you set the min pool size to the number of connections that represents your average work load or concurrent connections to ensure there are connections available for the application when requested. Connections are cheap and it is preferred to overestimate the min pool size then incur the overhead associated with physically creating new connections. Max pool size should be the maximum peak or maximum amount of concurrent connections to be serviced. Connections should always remain at a steady state once the application has ramped up to capacity. You want to avoid any overhead associated with creating and destroying a large number of connections.

The following two connection pool parameters are available for configuring how the ODP connection pooling facility will dynamically grow and shrink the pool.

Incr pool size

Decr pool size

The Incr and Decr pool size should be set to a value that will allow a gradual increase or decrease of connections to and from steady state as your application ramps up to capacity. These values should not be set too high as it will result in creating or destroying a large number of connections at a single point in time. The dynamic growth and shrinking of the pool occurs every three minutes. A worker thread will use an LRU algorithm to determine which connections are candidates for removal. The pool size is decreased by the number of candidates up to decr pool size that will not violate the min pool size setting.

Other Additional Information Regarding Pooling:

Connection pools are not shared across worker processes.

A pool is created based on a unique connection string property value. Be careful when dynamically creating connection pool property strings. Changing the order will not result in a new pool but modifying any of the values will result in another connection pool being created and used by your application.

The method Clear Pool should only be used when the pool appears to have become unstable. Handling ora-3113 exceptions would be a scenario where it would be appropriate to re-create the pool as this is an indication that connections in the pool have become stale due to events such as database down/maintenance.

Due to the nature of connection pooling, some connections can remain idle which will cause can cause a firewall that is set to kill idle connections to sever some connections in the pool. To help reduce possible sqlnet connection errors (ie. ORA-3135, ORA-3113, ORA-1012) “Validate Connection=true” CAN BE SET when using ODP connection pooling. This will validate connections coming out of the pool by causing a round trip to the server to validate the connection which will help to reduce passing a stale connection to the application. This does cause some performance penalty as a round trip occurs for every con.open call.

Due to the use of both managed and unmanaged resources by odp, it is very important that you always close and dispose resources. Not properly disposing connections, ref cursors, lobs, etc may result in leaking connections in the pool which can lead to poor application performance, and exceptions such as “ODP-1000 “Connection Request Timed Out”, “ORA-1000 Max Open Cursors Exceeded”, and memory leaks among other things. Please refer to the Garbage Collection section below for additional information. Other possible causes for ODP-1000 Exceptions are detailed in Technical Note 363026.1 ODP-1000 “Connection Request Timed Out” explained.

While the default values are generally adequate, the pool is highly configurable and determining optimal settings requires an understanding of the application usage, and may vary on a case by case basis. As of 11.1.0.6 ODP.NET, there are built in performance counters that can be used to help gauge the activity. More information on the ODP.NET performance counters please see the Oracle Data Provider for .Net Developer’s Guide, “Connection Pool Performance Counters” page 3-7.

The name-value pairs for the pooling attributes are case insensitive, with the exception of Username and Password if case sensitivity is preserved by code. Refer to the ODP documentation under in the OracleConnection Class section for a complete example that demonstrates when connection pools are created and when connections are drawn from an existing connection pool.

For more on the Connection Pooling settings please see Note 240997.1 Using Connection Pooling with ODP.Net.

2. Command Objects – SQL Performance

It is highly recommended that you use bind variables with your SQL and PLSQL statements/calls. This technique will allow the Oracle Server to re-use parsed statements only replacing the placeholders for the binds with the new values for execution. This best practice will help you void heavy contention on the shared pool and improve SQL response times as the statement will only incur a soft parse when executing the same statement again within your application.

In addition to using Bind Variables so that server resources are utilized more efficiently, ODP.NET uses Statement Caching so that the statement handles on the client side can be re-used. Statement Caching is on by default, and the Statement Cache Size defaults to 10, which can be tuned based on application usage. Increasing this value can improve performance, at the cost of increased memory usage.
The cache is maintained via a LRU algorithm, so in a case where a few statements are executed heavily and a large number of statements are executed infrequently, it may be beneficial to explicitly choose which statements are added to the cache. For example, you can set the cache size to 10 in the connection pool, then execute each of the Top 10 statements once. From then on, set AddStatementToCache to false for all remaining calls.

3. Garbage Collector (GC) and Disposing

It is very highly recommended that all the Oracle objects that have a close and dispose method be called as soon as they are done being used in the application. Dispose should be called explicitly in the application code, rather than relying on finalizes to do the cleanup. To ensure that Dispose is called under all circumstances, it should be done in a Finally block, or via the C# Using construct.

Oracle Support can not stress this enough. Since we have seen many issues occur, just because of the objects not being cleaned up in a timely manner. Here are reasons why to call the close/dispose methods on the Oracle objects:

ODP uses both managed and unmanaged code. Unmanaged code requires explicit cleanup, ie. Calling dispose. Which means not all the objects will be cleaned up by the gc, if dispose is not used.

ORA-1000 errors occur when objects are not close/disposed of correctly and in a timely manner. See Note 286707.1 Ora-01000 received in ODP.NET application using OracleDataReader for more information.

By waiting on the gc to try to clean up the objects it can, may cause the application to appear to have a memory leak or excessive memory usage. Instead use the close/dispose methods to free the memory at the appropriate time once the object is done being used. Instead of waiting on the gc.

Application can be slowed down when waiting on the gc to do a large amount of close/disposes at one time. By closing/disposing of the objects when they are finished being used will increase the speed of the application.

Applications may hang or crash due to the memory not being freed when objects are finished with them. By not calling close/dispose on the Oracle objects, the objects can build up used memory and reduce the available memory for the application to run effectively.

i. How to determine if close and or dispose is being called on the Oracle Object:

To help determine if the close and / or dispose methods are being called, turn on ODP tracing. For instructions on how to turn on odp tracing please see Note 216912.1. Then run the application for 5 – 10 minutes. This will give a good baseline to start the troubleshooting. Please note that ODP tracing does not trace every Oracle object. See chart below for details on which calls are traced. 

Once the trace files have been generated, parse them for each of the class method calls listed below in the Table 1. In the trace file it will show the class.method calls in the following format. 

Open/Close/Dispose of connections can be seen in the traces as follows:

This shows the opening of the connection:

(ENTRY) OracleConnection::Open()

This shows the closing of the connection:

(ENTRY) OracleConnection::Close()

This shows the disposing of the connection:

(ENTRY) OracleConnection::Dispose() 

 Here is a list of Oracle classes that have either a close and / or dispose method that need to be called once that class is done being used and if the information is contained in the ODP trace:

Table 1

Class

Close Method

Dispose Method

Traced In ODP

OracleConnection

Yes

Yes

Yes

OracleDataReader

Yes

Yes

No, Dispose not traced.

OracleBFile

Yes

Yes

No, Dispose not traced.

OracleBlob

Yes

Yes

No, Dispose not traced.

OracleClob

Yes

Yes

No, Dispose not traced.

OracleXmlStream

Yes

Yes

No, Dispose not traced.

OracleCommand

 

Yes

No, Dispose not traced.

OracleDataAdapter

 

Yes

No, Dispose not traced.

OracleRefCursor

 

Yes

No, Dispose not traced.

OracleParameter

 

Yes

Yes

OracleCommandBuilder

 

Yes

Yes

OracleTransaction

 

Yes

Yes

OracleXmlType

 

Yes

No, Dispose not traced.

OracleGlobalization

 

Yes

Yes

NOTE: As you can see that not all methods are traced. Unpublished Enhancement Request Bug 5035642 has been filed to provide more comprehensive ODP.Net Tracing. However, the listing above should help to analyze the trace files and identify which methods are not being called. Which will help with identifying which coding changes are needed to properly close and / or dispose of the Oracle objects.

4. Miscellaneous Other Tips

ThreadAborts should never be used in any application, including one that uses the Oracle Data Provider for .Net. This is stated in the Oracle Data Provider for .Net Release Notes:

Thread.Abort() should not be used as unmanaged resources may remain unreleased properly, which can potentially cause memory leaks and hangs. 

Typically, this would not be discovered until a problem occurs and a crash or hang dump of the application was taken. ThreadAbort may be call during Response.Redirect, Response.End, or Server.Transfer which is explained further at http://support.microsoft.com/kb/312629 or IIS times out the connection. For more assistance on troubleshooting why a ThreadAbort is being called, please contact Microsoft.

 

abril 13, 2009 - Posted by | Microsoft, Tecnologia

1 comentario »

  1. fantastic !!! great article !!! more of those toppics. thx

    Comentario por espinete | octubre 28, 2010 | Responder


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: