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