dotConnect for Oracle Documentation
In This Topic
    Performance Counters Support
    In This Topic

    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