Configuring Oracle Data Provider for .NET

The settings for specific versions of ODP.NET can be configured in several ways for specific effects on precedence:

The Windows registry entries are machine-wide settings for a particular version of ODP.NET.
The machine.config settings are .NET framework-wide settings that override the Windows registry values.
The application or web config file settings are application-specific settings that override the machine.config settings and the Windows registry settings.
Any attribute settings that are equivalent to the connection string override everything.

The application or web config file can be useful and sometimes essential in scenarios where more than one application on a computer use the same version of ODP.NET, but each application needs a different ODP.NET configuration. The Windows registry value settings for a given version of ODP.NET affect all the applications that use that version of ODP.NET. However, having ODP.NET configuration values in the application or web config file assure that these settings are applied only for that application, thus providing more granularity.

For example, if the application or web.config file has a StatementCacheSize configuration setting of 100, this application-specific setting forces the version of ODP.NET that is loaded by that application to use 100 for the StatementCacheSize and overrides any setting in the machine.config and in the registry. Note that for any setting that does not exist in a config file (machine.config or application/web config), the value in the registry for a loaded version of ODP.NET is used, as in previous releases.

Note that ODP.NET reads the machine.config files from the version of the .NET Framework on which ODP.NET runs, not from the version of ODP.NET.

ODP.NET only reads the Windows Registry and the XML configuration file when it is loaded into memory, thus any configuration changes made after that are not read or used until the application is re-started.

Supported Configuration Settings

ODP.NET supports the configuration of an attribute as follows:

In the Windows registry.
In an XML file.
Through a different mechanism such as a connection string or programmatically through an ODP.NET class, if applicable.

Table 2-1 describes each configurable attribute that is supported by ODP.NET. In the table, the term Configuration Support is followed by the types of configuration support (Windows registry, XML file, and so on) that are available for that attribute.

The table describes valid values as well as the default for each attribute.

Note:

The default values shown are the values used for an attribute if the registry key does not exist or if it is not configured anywhere.

Table 2-1 Configuration Attributes

Attribute/Setting Name	Description
`CheckConStatus`	Specifies whether the status of the connection is checked or not before putting the connection back into the connection pool. This registry entry is not created by the installation of ODP.NET. However, the default value `1` is used. Configuration Support: Windows Registry and XML file Valid Values: `1`: Check the status of the connection. `0`: Do not check the status of the connection. Default: `1`
`DbNotificationPort`	Specifies the port number which ODP.NET listens to, for all notifications sent by the database for change notification, HA, or RLB features. ODP.NET does not throw any errors if an invalid or used port number is specified. The port can also be set to override the Windows registry and XML configuration file by setting the `OracleDependency.Port` static field. Configuration Support: XML file, and ODP.NET class Valid Values: `-1`: Open a random unused port to listen to. `n` `>` `=` `0`: Listen on port `n`. Default: `-1`
`DemandOraclePermission`	Specifies whether ODP.NET demands `OraclePermission` from the .NET application that is trying to access the database using ODP.NET. Configuration Support: Windows Registry and XML file Valid Values: 0: Disables demands for `OraclePermission`. 1: Enables demands for `OraclePermission` Default: 0
`DllPath`	Specifies the location where dependent unmanaged Oracle Client binaries load from. Configuration Support: Windows Registry and XML file Valid Values: The path where dependent unmanaged Oracle Client binaries reside. Default: `ORACLE_BASE\\ORACLE_HOME\bin`
`DynamicEnlistment`	Specifies whether the application enlists in distributed transactions explicitly after an `OracleConnection.Open` method invocation through `EnlistTransaction()` or `EnlistDistributedTransaction()`. To configure ODP.NET to enable dynamic enlistment programmatically, the connection string must contain `"enlist=dynamic"`. Configuration Support: Windows Registry, XML file, and `"enlist"` connection string attribute Valid Values: `0`: Disables ability to explicitly enlist in distributed transactions. `1`: Enables ability to explicitly enlist in distributed transactions. Default: 0
`FetchSize`	Specifies the total memory size, in bytes, that ODP.NET allocates to cache the data fetched from a database round-trip. This value can be set on the `OracleCommand` and the `OracleDataReader` `FetchSize` property as well. Configuration Support: Windows Registry, XML file, and ODP.NET class Valid Values: `0 <=` `n` `<= int.MaxValue`: `n` is the size of the cache in bytes. Default: `131072`
`MaxStatementCacheSize`	Specifies the maximum number of statements that can be cached when self-tuning is enabled. Configuration Support: Windows Registry and XML file Valid Values: 0 to `System.Int32.MaxValue`. Default: `100`
`MetaDataXml`	Specifies the name of the XML file that customizes the queries to obtain the metadata the ADO.NET 2.0 `GetSchema` method returns. `MetaDataXml` can only be set in a configuration file. Configuration Support: XML file only Valid Values: A complete file name for the XML file. Default: none
`PerformanceCounters`	Enables or disables publishing performance counters for connection pooling. Multiple performance counters can be obtained by adding the valid values. Configuration Support: Windows Registry and XML file Valid Values: `0`: Not Enabled `1`: Number of sessions being established with Oracle Database every second. `2`: Number of sessions being severed from Oracle Database every second. `4`: Number of active connections originating from connection pools every second. `8`: Number of active connections going back to the connection pool every second. `16`: Total number of active connections. `32`: Number of inactive connection pools. `64`: Total number of connections in use. `128`: Total number of connections available for use in all the connection pools. `256`: Number of pooled active connections. `1024`: Number of non-pooled active connections. `2048`: Number of connections that will be soon available in the pool. User has closed these connections, but they are currently awaiting actions, such transaction completion, before they can be placed back into the pool as free connections. `4095`: All the above Default: `0`
`PromotableTransaction`	Specifies the type of transaction to use when the first connection participates in the `TransactionScope` object. Configuration Support: Windows Registry, XML file, and promotable transaction connection string attribute Valid Values: `local`: The first connection opened in the `TransactionScope` object uses a local transaction. `promotable`: The first connection and all subsequent connections opened in the same `TransactionScope` object enlist in the same distributed transaction. Default: `promotable`
`SelfTuning`	Specifies whether self-tuning is enabled for an ODP.NET application. Configuration Support: Windows Registry, XML file, and `Self Tuning` connection string attribute Valid Values: 0: Self Tuning is disabled. Used in the registry or XML file. `false`: Self Tuning is disabled. Used for the `Self Tuning` connection string attribute. 1: Self Tuning is enabled. Used in the registry or XML file. `true`: Self Tuning is enabled. Used for the `Self Tuning` connection string attribute. Default: 1
`StatementCacheSize`	Specifies the number of cursors or statements to be cached on the database for each connection. This setting corresponds to Statement Cache Size attribute in the connection string. A value greater than zero also enables statement caching. Configuration Support: Windows Registry, XML file, and Statement Cache Size connection string attribute Valid Values: `0 <=` `n` `<=` the value of `OPEN_CURSORS` parameter set in `init.ora` database config file. `n` is the number to set. Default: `0`
`StatementCacheWithUdts`	Specifies whether or not Oracle UDTs retrieved by executing a `SELECT` statement are cached along with the statement in the statement cache. This setting affects the memory usage and performance of the application. Configuration Support: Windows Registry and XML file Valid Values: `0`: Oracle UDTs are not cached with statements. `1`: Oracle UDTs are cached along with statements. Default: `1`
`ThreadPoolMaxSize`	Specifies the default maximum size of worker threads for each available processor in a process. This value may affect the performance of ODP.NET connection creation, command execution timeout, and external procedures (`extproc`) that use the thread pool. However, unnecessarily increasing thread pool maximum size can also cause performance problems. Configuration Support: Windows Registry and XML file Valid Values: `0 <=` `n` `<= int.MaxValue`: Allows ODP.NET to reset thread pool maximum size with the value `n`. The ODP.NET reset operation may be ignored if the value is invalid. For example, if n is less than the number of available processors of the system. In this case, the result is the same as the value `-1`. `-1`: Leave the thread pool max size as is. Default: `-1` (this registry entry is not created by default) Note that prior to ODAC 2007 or version 11.1.0.6.20, ODP.NET resets the thread pool maximum size to `int.MaxValue` when the `OracleCommand.CommandTimeout` property is set to a value greater than 0. This erroneous behavior has been corrected. `OracleCommand.CommandTimeout` does not change thread pool maximum size.
`TraceFileName`	Specifies the file name to be used for logging trace information. Configuration Support: Windows Registry and XML file Valid Values: Any valid directory location and file name. Default: `c:\odpnet2.trc` (for .NET Framework 2.0)
`TraceLevel`	Specifies the level of tracing in ODP.NET. Because tracing all the entry and exit calls for all the objects can be excessive, `TraceLevel` is provided to limit tracing to certain areas of the provider. Each valid value indicates a possible tracing level. Compounded tracing levels can be obtained by adding the valid values. Configuration Support: Windows Registry and XML file Valid Values: `0`: None `1`: Entry, exit, and SQL statement information `2`: Connection pooling statistics `4`: Distributed transactions (enlistment and delistment) `8`: User-mode dump creation upon unmanaged exception `16`: HA Event Information `32`: Load Balancing Information `64`: Self Tuning Information `127`: All the above Default: 0 Note: ODP.NET does bit-wise checking on the value. When tracing is enabled, logging to the trace file can affect ODP.NET performance. Note: The user-mode dump creation requires `dbghelp.dll` version 5.1.2600.0 or later.
`TraceOption`	Specifies whether to log trace information in single or multiple files for different threads. If a single trace file is specified, the file name specified in `TraceFileName` is used. If the multiple trace files option is requested, a Thread ID is appended to the file name provided to create a trace file for each thread. Configuration Support: Windows Registry and XML file Valid Values: `0`: Single trace file `1`: Multiple trace files Default: `0`
`UdtCacheSize`	Specifies the size of the object cache for each connection in kilobytes (KB) that ODP.NET uses to retrieve and manipulate Oracle UDTs. Configuration Support: Windows Registry and XML file Valid Values: `0 <=` `n` `<= 4194303`, `n` is the number to set. Default: `4096`
`UDT Mapping`	Specifies a mapping between a custom type and an Oracle UDT in the database. The mappings can be specified in configuration files and custom type factories. However, if the mapping is specified in both places, mappings specified in the configuration files takes precedence over mappings specified using custom type factories. Configuration Support: XML file and Custom Type Factory Classes Valid Values: Any valid mapping. Default: none

Windows Registry

Upon installation, ODP.NET creates entries for configuration and tracing within the Windows Registry. Configuration and tracing registry values apply across all ODP.NET applications running in that Oracle client installation. Individual ODP.NET applications can override some of these values by configuring them within the ODP.NET application itself (for example, FetchSize). Applications can also use the .NET configuration files to override some of the ODP.NET Windows Registry values.

The ODP.NET registry values are located under HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\version\. There is one key for .NET Framework 2.0, 3.0, and 3.5, and one key for .NET Framework 4 and higher.

Note:

32-bit applications running on an x64-based version of Windows use the registry subkey, HKEY_LOCAL_MACHINE\Software\WOW6432node in place of HKEY_LOCAL_MACHINE\Software. If such applications use Oracle Data Provider for .NET (32-bit), then the ODP.NET registry values are located under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Oracle\ODP.NET\version\.

Configuration File Support

For customers who have numerous applications on a computer that depends on a single version of ODP.NET, the Windows Registry settings for a given version of ODP.NET may not necessarily be applicable for all applications that use that version of ODP.NET. To provide more granular control, ODP.NET Configuration File Support allows developers to specify ODP.NET configuration settings in an application config, web.config, or a machine.config file.

If a computer does not require granular control beyond configuration settings at the ODP.NET version level, there is no need to specify ODP.NET configuration settings through configuration files.

The following is an example of a web.config file for .NET Framework 2.0 and higher:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.dataaccess.client>
    <settings>
      <add name="DllPath"               value="C:\oracle\bin"/>
      <add name="FetchSize"             value="131072"/>
      <add name="PromotableTransaction" value="promotable"/>
      <add name="StatementCacheSize"    value="10"/>
      <add name="TraceFileName"         value="C:\odpnet2.trc"/>
      <add name="TraceLevel"            value="63"/>
      <add name="TraceOption"           value="1"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

Sample Configuration Files

The following is an example of app.config for ODP.NET using .NET Framework 2.0, which sets some additional attributes as well as two UDT type mappings:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <oracle.dataaccess.client>
   <settings>
     <add name="DbNotificationPort" value="-1"/>
     <add name="DllPath" value="C:\app\user\product\11.1.0\client_1\bin"/>
     <add name="DynamicEnlistment" value="0"/>
     <add name="FetchSize" value="131072"/>
     <add name="MetaDataXml" value="CustomMetaData.xml"/>
     <add name="PerformanceCounters" value="4095"/>
     <add name="PromotableTransaction" value="promotable"/>
     <add name="StatementCacheSize" value="50"/>
     <add name="ThreadPoolMaxSize" value="30"/>
     <add name="TraceFileName" value="c:\odpnet2.trc"/>
     <add name="TraceLevel" value="0"/>
     <add name="TraceOption" value="0"/>
     <add name="Person" value="udtMapping factoryName='PersonFactory, Sample,
       Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' typeName='PERSON'
       schemaName='SCOTT' dataSource='oracle'"/>
     <add name="Student" value="udtMapping factoryName='StudentFactory, Sample,
       Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' typeName='STUDENT'
       schemaName='SCOTT'"/>
   </settings>
 </oracle.dataaccess.client>
</configuration>