Obtaining LOB Data
When an OracleDataReader is created containing LOB column types, OracleDataReader determines whether the LOB column data needs to be fetched immediately or not by checking the value of the InitialLONGFetchSize property of the OracleCommand that created the OracleDataReader. By default, InitialLOBFetchSize is set to 0. If the InitialLOBFetchSize property value of the OracleCommand is left as 0, the entire LOB data retrieval is deferred until that data is explicitly requested by the application. If the InitialLOBFetchSize property is set to a nonzero value, the LOB data is immediately fetched up to the number of characters or bytes that the InitialLOBFetchSize property specifies.
By default, when InitialLOBFetchSize property is 0, GetOracleBlob() and GetOracleClob() can be invoked on the OracleDataReader to obtain OracleBlob and OracleClob objects. However, if the InitialLOBFetchSize is set to a nonzero value, GetOracleBlob() and GetOracleClob() methods are disabled. In this scenario, the BLOB and CLOB data needs to be fetched by using GetBytes() and GetChars(), respectively.
Setting InitialLOBFetchSize to a nondefault value can improve performance in certain cases. Using InitialLOBFetchSize in conjunction with GetBytes() and GetChars() can provide better performance than retrieving the underlying LOB data using OracleBlob or OracleClob objects in the following situation: If an application does not need to obtain OracleBlob and OracleClob objects from the OracleDataReader and the size the LOB column data is not very large. InitialLOBFetchSize is particularly useful in cases where the size of the LOB column data returned by query is approximately the same for all the rows.
It is generally recommended that InitialLOBFetchSize be set to a value larger than the size of the LOB data for more than 80% of the rows returned by the query. For example, if the size of the LOB data is less than 1KB in 80% of the rows and more than 1MB for 20% of the rows, set InitialLOBFetchSize to 1KB.
ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LOB data can be fetched in a random fashion.
To obtain data beyond InitialLOBFetchSize bytes or characters, one of the following must be in the select list:
- primary key
- ROWID
- unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it).
The requested data is fetched from the database when the appropriate typed accessor method is called on the OracleDataReader object. Note that the primary key column is not required if InitialLOBFetchSize is set to 0.
In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLOBFetchSize property on the OracleCommand object to an amount equal to or greater than the bytes or characters that need to be retrieved.
Controlling the Number of Rows Fetched in One Server Round-Trip
Application performance depends on the number of rows the application needs to fetch and the number of database round-trips that are needed to retrieve them.
Use of FetchSize
The FetchSize property represents the total memory size in bytes that ODP.NET allocates to cache the data fetched from a server round-trip.
The FetchSize property can be set either on the OracleCommand or the OracleDataReader depending on the situation. Additionally, the FetchSize property of the OracleCommand is inherited by the OracleDataReader and can be modified.
If the FetchSize property is set on the OracleCommand, then the newly created OracleDataReader inherits the FetchSize property of the OracleCommand. This inherited FetchSize can be left as is or modified to override the inherited value. The FetchSize property of the OracleDataReader object can be changed before the first Read method invocation, which allocates memory specified by the FetchSize. All subsequent fetches from the database use the same cache allocated for that OracleDataReader. Therefore, changing the FetchSize after the first Read method invocation has no effect.
Fine-Tuning FetchSize
By fine-tuning the FetchSize property, applications can control memory usage and the number of rows fetched in one server round-trip for better performance. For example, if a query returns 100 rows and each row takes 1024 bytes, then setting FetchSize to 102400 takes just one server round-trip to fetch the hundred rows. For the same query, if the FetchSize is set to 10240, it takes 10 server round-trips to retrieve 100 rows. If the application requires all the rows to be fetched from the result set, the first scenario is faster than the second. However, if the application requires just the first 10 rows from the result set, the second scenario can perform better since it only fetches 10 rows and not 100 rows.
Using the RowSize Property
The RowSize property of the OracleCommand object is populated with the row size (in bytes) after an execution of a SELECT statement. The FetchSize property can then be set to a value relative to the RowSize by setting it to the product of RowSize and the number of rows to fetch for each server round-trip.
For example, setting the FetchSize to RowSize * 10 forces the OracleDataReader to fetch exactly 10 rows for each server round-trip. Note that the RowSize does not change due to the data length in each individual columns. Instead, the RowSize is determined strictly from the metadata information of the database table(s) that the SELECT is executed against.
The RowSize property can be used to set the FetchSize at design time or at runtime as described in the following sections.
Setting FetchSize Value at Design Time
If the row size for a particular SELECT statement is already known from a previous execution, FetchSize of the OracleCommand can be set at design time to the product of that row size and the number of rows the application wishes to fetch for each server round-trip. The FetchSize value set on the OracleCommand object is inherited by the OracleDataReader that is created by the ExecuteReader method invocation on the OracleCommand. Rather than setting the FetchSize on the OracleCommand, the FetchSize can also be set on the OracleDataReader directly. In either case, the FetchSize is set at design time without accessing the RowSize property value at runtime.
Setting FetchSize Value at Runtime
Applications that do not know the row size at design time can use the RowSize property of the OracleCommand object to set the FetchSize property of the OracleDataReader object. The RowSize property provides a dynamic way of setting the FetchSize property based on the size of a row.
After an OracleDataReader object is obtained by invoking the ExecuteReader method on the OracleCommand, the RowSize property is populated with the size of the row (in bytes). By using the RowSize property, the application can dynamically set the FetchSize property of the OracleDataReader to the product of the RowSize property value and the number of rows the application wishes to fetch for each server round-trip. In this scenario, the FetchSize is set by accessing the RowSize property at runtime.
OracleCommand Object
The OracleCommand object represents SQL statements or stored procedures executed on the Oracle Database.
Transaction
The Oracle Database starts a transaction only in the context of a connection. Once a transaction starts, all the successive command execution on that connection run in the context of that transaction. Transactions can only be started on a OracleConnection object and the read-only Transaction property on the OracleCommand object is implicitly set by the OracleConnection object. Therefore, the application cannot set the Transaction property, nor does it need to.
Parameter Binding
ODP.NET allows applications to retrieve data as either a .NET Framework type or an ODP.NET type.
How the data is retrieved depends on whether application sets the OUT parameter to the DbType property (.NET type) or OracleDbType property (ODP.NET type) of the OracleParameter.
For example, if the output parameter is bound as an DbType.String, the output data is returned as a .NET String. On the other hand, if the parameter is bound as OracleDbType.Char, the output data is returned as OracleString type.
When the DbType of an OracleParameter is set, the OracleDbType of the OracleParameter changes accordingly, and vice versa. The parameter set last prevails.
Lastly, an application can simply bind the data and have ODP.NET infer both the DbType and OracleDbType from the .NET type of the parameter value.
ODP.NET populates InputOutput, Output, and ReturnValue parameters with the Oracle data, through the execution of the following OracleCommand methods:
- ExecuteReader
- ExecuteNonQuery
- ExecuteScalar
An application should not bind a value for output parameters; it is the responsibility of ODP.NET to create the value object and populate the OracleParameter Value property with the object.
Continue article...