Optimize .NET Access with Oracle Database

 

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)

1. Ensure you have enough connections in CP – better to have many than too few.
2. OS-authenticated CP available with ODP.NET 11g
3. Keep a steady state of CP – never destroy or create large number of connections
4. Close/Dispose connections explicitly – don’t rely on garbage collector
5. You can monitor CP performance counters using ODP.NET 11.1.0.6.20 and higher

Performance Counters:

Bind Variables

1. 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

1. Using statement caching retains parsed statement in shared pool.
2. Cursor stays open on client side and Metadata remains on client
3. Best used/works with Bind Variables
4. Caching works with 10.2.0.2.20 and caches the last 10  executed statements.
5. 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

Note Performance:

Mass Data Movement with Arrays

1. PL/SQL associative arrays can be used to pass large amounts of data between .NET and DB of the same data type.
2. If you are executing the same statement multiple times, you could use a parameter array binding.

Statement Batching

1. You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
2. You can use anonymous PL/SQL for disparate or similar statements

ODP.NET DataTypes

1. Try and avoid unnecessary datatype conversions between .NET and the DB
2. 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 Setup

CREATE OR REPLACE  PACKAGE "HR"."GET_EMPLOYEES" AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);
END GET_EMPLOYEES;

CREATE OR REPLACE  PACKAGE BODY "HR"."GET_EMPLOYEES" AS
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR)
IS
BEGIN
    OPEN cur_Employees FOR
    SELECT * FROM EMPLOYEES;
END GetEmployees;
END GET_EMPLOYEE;

//C#
OracleConnection conn = new OracleConnection(
    "Data Source=orcl; User Id=HR; Password=password;");

OracleCommand cmd = new OracleCommand("GET_EMPLOYEES.GetEmployees", conn);   
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("cur_Employees", OracleDbType.RefCursor,
    ParameterDirection.Output);

conn.Open();
cmd.ExecuteNonQuery();
         
// get the OracleRefCursor from the output parameter
OracleRefCursor refcur =
    (OracleRefCursor)cmd.Parameters["cur_Employees"].Value;

// get the DataReader using the OracleRefCursor
OracleDataReader dr = refcur.GetDataReader();
while(dr.Read())
   Console.WriteLine(dr["EMPLOYEE_ID"] + "; " + dr["FIRST_NAME"] + "; " +
       dr["LAST_NAME"]);

conn.Close();

Oracle Performance Tuning in Visual Studio

1. You can tune ad-hoc SQLs in query window


2. Tune bad SQL using Oracle Performance Analyzer – requires:




1. SYSDBA privilege


2. database license for Oracle Diagnostic Pack


3. 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

1. Evolution of statspack


2. builtin repository


3. captures performance stats at regular intervals

ADDM – Automatic Database Diagnostic Monitor

1. Analyses AWR stats and generates recommendations

AWR and ADDM nodes are now available in Visual Studio.

Query execution in an infinite loop: