We can increase the performance of our ODP.NET applications by controlling the fetch size of either OracleCommand or OracleDataReader. By controlling the fetch size (i.e. the amount of data fetched during each round-trip to the database) you can dramatically decrease (or increase!) the amount of time taken to fetch the data.
However, if you are working with Ref Cursors rather than "plain" SQL statements, the ability to control the fetch size is not readily available. This is because the RowSize property of an OracleCommand object is set to 0 (zero) when working with PL/SQL code, as would be the case with Ref Cursor data.
However, if you have ever examined the members of the OracleDataReader in the debug window within Visual Studio, you may have noticed that the OracleDataReader does have a particularly non-public member named "m_rowSize" which just happens to represent the row size. If only you could access that value you could then easily set the FetchSize property on the OracleDataReader to a specific (meaningful) value rather than be forced to accept the default of 64KB (65536).
As "m_rowSize" is a Non-Public member, you can’t just reference "m_rowSize" in your application code to retrieve this value. However, using the "reflection" capabilities of the .NET Framework, you can access this value. Once you have done that, you can set the value of the FetchSize property to whatever value is appropriate for your application.
Please note that this technique is not a good coding approach, and, of course, if the "m_rowSize" member name should change, it would break any code using this technique.
The following sample code will illustrate the use of fetchsize in the case of Ref Cursors.
using(OracleConnection conn = new OracleConnection
(@"DataSource=source;User Id=username;Password=password;"))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "StoredProcedureName";
cmd.Parameters.Add("REF_CURSOR_SAMPLE",OracleType.RefCursor).Direction = ParameterDirection.Output;
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
FieldInfo fieldInfo = reader.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
int cmdRowSize = (int)fieldInfo.GetValue(reader);
reader.FetchSize = cmdRowSize * noOfRowsExpected;
while(reader.Read())
{
// do your logic here
}
conn.Close();
} |
|
Are we using the ref cursor here? Is there any way to fetch the batches of rows?
ReplyDelete
ReplyDeletehttp://grow-n-shine.blogspot.com.es/2011/05/optimize-net-access-with-oracle.html
Connection Pool (CP)
Ensure you have enough connections in CP – better to have many than too few.
OS-authenticated CP available with ODP.NET 11g
Keep a steady state of CP – never destroy or create large number of connections
Close/Dispose connections explicitly – don’t rely on garbage collector
You can monitor CP performance counters using ODP.NET 11.1.0.6.20 and higher
Bind Variables
Always use Bind Variables. Using Bind Variables will prevent reparsing of frequently executed statements. Literal value changes
in commands force a reparse. Reparsing is fairly expensive CPU operation and requires shared pool locks.
Statement Caching
Using statement caching retains parsed statement in shared pool.
Cursor stays open on client side and Metadata remains on client
Best used/works with Bind Variables
Caching works with 10.2.0.2.20 and caches the last 10 executed statements.
Developer can choose which statement to cache.
Statement Cache Size=0 (no caching)
Statement Cache Size=1 (caching)
With ODP.NET 11.1.0.7.20 cache size dynamically changes at runtime and provides automatic optimization/self-tuning. Self-tuning is
enabled by default and no code changes are required.
Data Retrieval
You can control how much data is retrieved from the database per round-trip. Too much data can cause excessive client-side memory
used and too little may cause additional round-trips. You can use OracleCommand.RowSize and OracleDataReader.FetchSize to control
the result. FetchSize can be set as multiple of RowSize and RowSize can be dynamicall populated after statement execution.
FetchSize = RowSize X 1
FetchSize = RowSize X 100
Mass Data Movement with Arrays
PL/SQL associative arrays can be used to pass large amounts of data between .NET and DB of the same data type.
If you are executing the same statement multiple times, you could use a parameter array binding.
Statement Batching
You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
You can use anonymous PL/SQL for disparate or similar statements
ODP.NET DataTypes
Try and avoid unnecessary datatype conversions between .NET and the DB
Use OracleParameter.Dbtype
public void CreateOracleDbParameter()
{
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "pDName";
parameter.DbType = DbType.String;
parameter.Value = "ENGINEERING";
parameter.SourceColumn = "DName";
}
Use REF Cursors
With REF Cursors, you can retrieve data as needed and control data retrieved via FetchSize. You can fill a DataSet with just a
portion of the REF cursor result. You can pass REF cursors back as input stored procedure parameters. Use OracleRefCursor class.
Oracle Performance Tuning in Visual Studio
You can tune ad-hoc SQLs in query window
Tune bad SQL using Oracle Performance Analyzer – requires:
SYSDBA privilege
database license for Oracle Diagnostic Pack
database license for Oracle Tuning Pack
AWR and ADDM
These are built into Oracle Database 10g and are invaluable for diagnosing Performance issues.
AWR – Automatic Workload Repository
Evolution of statspack
builtin repository
captures performance stats at regular intervals
ADDM – Automatic Database Diagnostic Monitor
Analyses AWR stats and generates recommendations
AWR and ADDM nodes are now available in Visual Studio.