I listened to a Good Presentation on Optimizing .NET access with Oracle database hosted by Oracle Product Management. I’ve captured Key learning from it below for my future reference:
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 22.214.171.124.20 and higher
- 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.
- 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 126.96.36.199.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.
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.
- You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
- You can use anonymous PL/SQL for disparate or similar statements
- 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.
Example from MSDN
// Database SetupCREATE OR REPLACE PACKAGE "HR"."GET_EMPLOYEES" AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);
CREATE OR REPLACE PACKAGE BODY "HR"."GET_EMPLOYEES" AS
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR)
OPEN cur_Employees FOR
SELECT * FROM EMPLOYEES;
OracleConnection conn = new OracleConnection(
"Data Source=orcl; User Id=HR; Password=password;");
OracleCommand cmd = new OracleCommand("GET_EMPLOYEES.GetEmployees", conn);
cmd.CommandType = CommandType.StoredProcedure;
// get the OracleRefCursor from the output parameter
OracleRefCursor refcur =
// get the DataReader using the OracleRefCursor
OracleDataReader dr = refcur.GetDataReader();
Console.WriteLine(dr["EMPLOYEE_ID"] + "; " + dr["FIRST_NAME"] + "; " +
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.
Query execution in an infinite loop: