dotConnect for Oracle Documentation
Performance Counters Support

When developing a data access layer, performance is almost always a crucial factor. In order to provide a wider set tools for analysing performance, dotConnect for Oracle offers support for performance counters that allow you to conveniently measure the frequency of connecting/disconnecting to the data source, the number of active connections, pooled connections, etc.

You can use these performance counters to monitor the connection resources your application uses. They can be monitored in Windows Performance Monitor or accessed programmatically using the PerformanceCounter class.

Activating Performance Counters

Please note that dotConnect for Oracle performance counters are disabled by default in order to not hamper performance. To enable them, set the Use Performance Monitor connection string parameter to true for all your dotConnect for Oracle connections you want to monitor. Only connections with this connection string parameter will be counted.

Available Performance Counters

Currently dotConnect for Oracle offers 12 different performance counters.

Performance counter

Description

HardConnectsPerSecond

The number of dotConnect for Oracle connections per second that are being made to Oracle.

HardDisconnectsPerSecond

The number of dotConnect for Oracle disconnects from Oracle per second.

NumberOfActiveConnectionPools

The total number of dotConnect for Oracle connection pools.

NumberOfActiveConnections

The number of active dotConnect for Oracle connections that currently are in use.

NumberOfFreeConnections

The number of free dotConnect for Oracle connections available for use in the connection pools.

NumberOfInactiveConnectionPools

The number of inactive dotConnect for Oracle connection pools that have not had any recent activity and are waiting to be disposed.

NumberOfNonPooledConnections

The number of active dotConnect for Oracle connections that are not pooled.

NumberOfPooledConnections

The total number of active dotConnect for Oracle connections in connection pools.

NumberOfReclaimedConnections

The number of dotConnect for Oracle connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application. Not explicitly closing or disposing connections hurts performance.

NumberOfStasisConnections

The number of dotConnect for Oracle connections currently awaiting completion of an action and which are therefore unavailable for use by the application.

SoftConnectsPerSecond

The number of active dotConnect for Oracle connections being pulled from the connection pool per second.

SoftDisconnectsPerSecond

The number of active dotConnect for Oracle connections that are being returned to the connection pool per second.

A typical usage of asynchronous methods looks like the following code.

IAsyncResult myResult = myCommand.BeginExecuteNonQuery(null, null);
...
rowsAffected = myCommand.EndExecuteNonQuery(myResult);
Dim myResult As IAsyncResult = myCommand.BeginExecuteNonQuery(Nothing, Nothing)
...
Dim rowsAffected As Int32 = myCommand.EndExecuteNonQuery(myResult)


First, application calls the "Begin" method and passes it all the parameters needed to accomplish the task. This method returns IAsyncResult object that represents the operation invoked and is used for later processing. After the process has been invoked, application can proceed to do its job.
Later, when the query has finished its execution, application calls the "End" method, passing it IAsyncResult object returned by the "Begin" method that has been invoked earlier.
If something goes wrong with the query, the exception is thrown only when application calls "End" method.

Completion Signalling

The common synchronous method calls do not return until the operation is finished. In the asynchronous cases, the begin call returns immediately, so there is a need to determine when the operation is actually complete. dotConnect for Oracle provides you with three ways to catch the query termination.

All of the methods listed in the table 1 accept at least two parameters. For example, DbCommandBase.BeginExecuteReader method is declared in the following way:

public IAsyncResult BeginExecuteReader(
   AsyncCallback callback,
   object stateObject,
   CommandBehavior behavior
);
Public Function BeginExecuteReader( _
   ByVal callback As AsyncCallback, _
   ByVal stateObject As Object, _
   ByVal behavior As CommandBehavior _
) As IAsyncResult

Samples

using Devart.Data.Oracle;
using System;
using System.Diagnostics;
using System.Runtime.InteropServices;

class Program
{
    PerformanceCounter[] PerfCounters = new PerformanceCounter[12];
    OracleConnection connection = new OracleConnection();

    static void Main()
    {
        Program prog = new Program();
        // Open a connection and create the performance counters.
        prog.connection.ConnectionString = GetConnectionString1();
        prog.SetUpPerformanceCounters();
        Console.WriteLine("Available Performance Counters:");

        // Create the connections and display the results.
        prog.CreateConnections();
        Console.WriteLine("Press Enter to finish.");
        Console.ReadLine();
    }

    private void CreateConnections()
    {
        // List the Performance counters.
        WritePerformanceCounters();

        // Create 4 connections and display counter information.
        OracleConnection connection1 = new OracleConnection(
              GetConnectionString1());
        connection1.Open();
        Console.WriteLine("Opened the 1st Connection:");
        WritePerformanceCounters();

        OracleConnection connection2 = new OracleConnection(
              GetConnectionString3());
        connection2.Open();
        Console.WriteLine("Opened the 2nd Connection:");
        WritePerformanceCounters();

        OracleConnection connection3 = new OracleConnection(
              GetConnectionString2());
        connection3.Open();
        Console.WriteLine("Opened the 3rd Connection:");
        WritePerformanceCounters();

        OracleConnection connection4 = new OracleConnection(
              GetConnectionString2());
        connection4.Open();
        Console.WriteLine("Opened the 4th Connection:");
        WritePerformanceCounters();

        connection1.Close();
        Console.WriteLine("Closed the 1st Connection:");
        WritePerformanceCounters();

        connection2.Close();
        Console.WriteLine("Closed the 2nd Connection:");
        WritePerformanceCounters();

        connection3.Close();
        Console.WriteLine("Closed the 3rd Connection:");
        WritePerformanceCounters();

        connection4.Close();
        Console.WriteLine("Closed the 4th Connection:");
        WritePerformanceCounters();
    }

    private enum DC_Ora_Performance_Counters
    {
        NumberOfActiveConnectionPools,
        NumberOfReclaimedConnections,
        HardConnectsPerSecond,
        HardDisconnectsPerSecond,
        NumberOfInactiveConnectionPools,
        NumberOfNonPooledConnections,
        NumberOfPooledConnections,
        NumberOfStasisConnections
        SoftConnectsPerSecond
        SoftDisconnectsPerSecond
        NumberOfActiveConnections
        NumberOfFreeConnections
    }

    private void SetUpPerformanceCounters()
    {
        connection.Close();
        this.PerfCounters = new PerformanceCounter[10];
        string instanceName = GetInstanceName();
        Type apc = typeof(ADO_Net_Performance_Counters);
        int i = 0;
        foreach (string s in Enum.GetNames(apc))
        {
            this.PerfCounters[i] = new PerformanceCounter();
            this.PerfCounters[i].CategoryName = "Devart dotConnect for Oracle";
            this.PerfCounters[i].CounterName = s;
            this.PerfCounters[i].InstanceName = instanceName;
            i++;
        }
    }

    [DllImport("kernel32.dll", SetLastError = true)]
    static extern int GetCurrentProcessId();

    private string GetInstanceName()
    {
        //This works for Winforms apps.
        string instanceName =
            System.Reflection.Assembly.GetEntryAssembly().GetName().Name;

        // Must replace special characters like (, ), #, /, \\
        string instanceName2 =
            AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(', '[')
            .Replace(')', ']').Replace('#', '_').Replace('/', '_').Replace('\\', '_');

        // For ASP.NET applications your instanceName will be your CurrentDomain's 
        // FriendlyName. Replace the line above that sets the instanceName with this:
        // instanceName = AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(','[')
        // .Replace(')',']').Replace('#','_').Replace('/','_').Replace('\\','_');

        string pid = GetCurrentProcessId().ToString();
        instanceName = instanceName + "[" + pid + "]";
        Console.WriteLine("Instance Name: {0}", instanceName);
        Console.WriteLine("---------------------------");
        return instanceName;
    }

    private void WritePerformanceCounters()
    {
        Console.WriteLine("---------------------------");
        foreach (PerformanceCounter p in this.PerfCounters)
        {
            Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue());
        }
        Console.WriteLine("---------------------------");
    }

    private static string GetConnectionString1()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        return @""User Id=Scott;Password=tiger;Data Source=Ora;"" +
          "Use Performance Monitor=True;";
    }
    private static string GetConnectionString2()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        return @"User Id=Scott;Password=tiger;Server=DBORACLE;Direct=True;Service Name=orcl1110" +
          "Use Performance Monitor=True;";
    }

    private static string GetConnectionString3()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        return @"User Id=Demobase;Password=test;Data Source=Ora;" +
          "Use Performance Monitor=True;";
    }
}
Option Explicit On
Option Strict On

Imports Devart.Data.Oracle
Imports System.Diagnostics
Imports System.Runtime.InteropServices

Class Program

    Private PerfCounters(9) As PerformanceCounter
    Private connection As OracleConnection = New OracleConnection

    Public Shared Sub Main()
        Dim prog As Program = New Program
        ' Open a connection and create the performance counters. 
        prog.connection.ConnectionString = _
           GetConnectionString1()
        prog.SetUpPerformanceCounters()
        Console.WriteLine("Available Performance Counters:")

        ' Create the connections and display the results.
        prog.CreateConnections()
        Console.WriteLine("Press Enter to finish.")
        Console.ReadLine()
    End Sub

    Private Sub CreateConnections()
        ' List the Performance counters.
        WritePerformanceCounters()

        ' Create 4 connections and display counter information.
        Dim connection1 As OracleConnection = New OracleConnection( _
           GetConnectionString1)
        connection1.Open()
        Console.WriteLine("Opened the 1st Connection:")
        WritePerformanceCounters()

        Dim connection2 As OracleConnection = New OracleConnection( _
           GetConnectionString3)
        connection2.Open()
        Console.WriteLine("Opened the 2nd Connection:")
        WritePerformanceCounters()

        Console.WriteLine("Opened the 3rd Connection:")
        Dim connection3 As OracleConnection = New OracleConnection( _
           GetConnectionString)
        connection3.Open()
        WritePerformanceCounters()

        Dim connection4 As OracleConnection = New OracleConnection( _
           GetConnectionString)
        connection4.Open()
        Console.WriteLine("Opened the 4th Connection:")
        WritePerformanceCounters()

        connection1.Close()
        Console.WriteLine("Closed the 1st Connection:")
        WritePerformanceCounters()

        connection2.Close()
        Console.WriteLine("Closed the 2nd Connection:")
        WritePerformanceCounters()

        connection3.Close()
        Console.WriteLine("Closed the 3rd Connection:")
        WritePerformanceCounters()

        connection4.Close()
        Console.WriteLine("Closed the 4th Connection:")
        WritePerformanceCounters()
    End Sub

    Private Enum ADO_Net_Performance_Counters
        NumberOfActiveConnectionPools
        NumberOfReclaimedConnections
        HardConnectsPerSecond
        HardDisconnectsPerSecond
        NumberOfInactiveConnectionPools
        NumberOfNonPooledConnections
        NumberOfPooledConnections
        NumberOfStasisConnections
        SoftConnectsPerSecond
        SoftDisconnectsPerSecond
        NumberOfActiveConnections
        NumberOfFreeConnections
    End Enum

    Private Sub SetUpPerformanceCounters()
        connection.Close()
        Me.PerfCounters(9) = New PerformanceCounter()

        Dim instanceName As String = GetInstanceName()
        Dim apc As Type = GetType(ADO_Net_Performance_Counters)
        Dim i As Integer = 0
        Dim s As String = ""
        For Each s In [Enum].GetNames(apc)
            Me.PerfCounters(i) = New PerformanceCounter()
            Me.PerfCounters(i).CategoryName = "Devart dotConnect for Oracle"
            Me.PerfCounters(i).CounterName = s
            Me.PerfCounters(i).InstanceName = instanceName
            i = (i + 1)
        Next
    End Sub

    Private Declare Function GetCurrentProcessId Lib "kernel32.dll" () As Integer

    Private Function GetInstanceName() As String
        'This works for Winforms apps. 
        Dim instanceName As String = _
           System.Reflection.Assembly.GetEntryAssembly.GetName.Name

        ' Must replace special characters like (, ), #, /, \\ 
        Dim instanceName2 As String = _
           AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
           .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")

        'For ASP.NET applications your instanceName will be your CurrentDomain's 
        'FriendlyName. Replace the line above that sets the instanceName with this: 
        'instanceName = AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
        '    .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")

        Dim pid As String = GetCurrentProcessId.ToString
        instanceName = (instanceName + ("[" & (pid & "]")))
        Console.WriteLine("Instance Name: {0}", instanceName)
        Console.WriteLine("---------------------------")
        Return instanceName
    End Function

    Private Sub WritePerformanceCounters()
        Console.WriteLine("---------------------------")
        For Each p As PerformanceCounter In Me.PerfCounters
            Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue)
        Next
        Console.WriteLine("---------------------------")
    End Sub

    Private Shared Function GetConnectionString1() As String
        ' To avoid storing the connection string in your code, 
        ' you can retrieve it from a configuration file. 
        Return ("User Id=Scott;Password=tiger;Data Source=Ora;" & _
          "Use Performance Monitor=True;")
    End Function

    Private Shared Function GetConnectionString2() As String
        ' To avoid storing the connection string in your code, 
        ' you can retrieve it from a configuration file. 
        Return ("User Id=Scott;Password=tiger;Server=DBORACLE;Direct=True;Service Name=orcl1110" & _
          "Use Performance Monitor=True;")
    End Function

    Private Shared Function GetConnectionString3() As String
        ' To avoid storing the connection string in your code, 
        ' you can retrieve it from a configuration file. 
        Return ("User Id=Demobase;Password=test;Data Source=Ora;" & _
          "Use Performance Monitor=True;")
    End Function
End Class

See Also

OracleCommand Class  | OracleConnection Class  | OracleDataTable Class