private async Task DisableSessionTracing(OracleSessionValues sessionData, CancellationToken cancellationToken)
{
const string commandText = @"BEGIN
dbms_monitor.session_trace_disable(session_id => :sid, serial_num => :serial);
END;";
await SetSessionTracing(sessionData, commandText, cancellationToken);
}
public async Task Initialize(DatabaseSession databaseSession, CancellationToken cancellationToken)
{
databaseSession = databaseSession.Owner ?? databaseSession;
var oracleSessionValues = (OracleSessionValues)databaseSession.ProviderValues;
if (_oracleSessionValues != null && _oracleSessionValues.Instance == oracleSessionValues.Instance && _oracleSessionValues.Id == oracleSessionValues.Id && String.Equals(_oracleSessionValues.SqlId, oracleSessionValues.SqlId) && _oracleSessionValues.ExecutionId == oracleSessionValues.ExecutionId)
{
await Refresh(cancellationToken);
return;
}
Shutdown();
DatabaseSession = databaseSession;
try
{
_oracleSessionValues = oracleSessionValues.Clone();
if (!String.IsNullOrEmpty(_oracleSessionValues.SqlId) && _oracleSessionValues.ExecutionId.HasValue)
{
var monitorDataProvider = new SqlMonitorDataProvider(_oracleSessionValues.Instance, _oracleSessionValues.Id, _oracleSessionValues.ExecutionStart.Value, _oracleSessionValues.ExecutionId.Value, _oracleSessionValues.SqlId, _oracleSessionValues.ChildNumber.Value);
await OracleDatabaseModel.UpdateModelAsync(OracleConnectionStringRepository.GetBackgroundConnectionString(_connectionString.ConnectionString), null, false, cancellationToken, monitorDataProvider);
_planItemCollection = monitorDataProvider.ItemCollection;
_planItemCollection.RefreshPeriod = DefaultRefreshPeriod;
//QueryCoordinatorLongOperations = _planItemCollection.QueryCoordinatorLongOperations;
if (_planItemCollection.RootItem != null)
{
ExecutionPlanTreeView.RootItem = _planItemCollection.RootItem;
SessionItems = _planItemCollection.SessionItems;
var collectionView = CollectionViewSource.GetDefaultView(SessionItems);
collectionView.SortDescriptions.Add(new SortDescription {
PropertyName = "SessionIdentifier.Instance"
});
collectionView.SortDescriptions.Add(new SortDescription {
PropertyName = "ParallelServerGroup"
});
collectionView.SortDescriptions.Add(new SortDescription {
PropertyName = "ParallelServerSet"
});
collectionView.SortDescriptions.Add(new SortDescription {
PropertyName = "ParallelServerNumber"
});
SummarySession.Inititalize(_planItemCollection);
await SafeRefresh();
}
}
}
finally
{
AutoRefreshEnabled = true;
}
}
public OracleSessionValues Clone()
{
var clone = new OracleSessionValues();
for (var i = 0; i < Values.Length; i++)
{
clone.Values[i] = Values[i];
}
return(clone);
}
private async Task EnableSessionTracing(OracleSessionValues sessionData, string traceIdentifier, bool waits, bool binds, CancellationToken cancellationToken)
{
traceIdentifier = traceIdentifier ?? String.Empty;
var commandText =
$@"BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = {OracleTraceIdentifier.Normalize(traceIdentifier)}';
dbms_monitor.session_trace_enable(session_id => :sid, serial_num => :serial, waits => {waits.ToString().ToUpperInvariant()}, binds => {binds.ToString().ToUpperInvariant()});
END;";
await SetSessionTracing(sessionData, commandText, cancellationToken);
}
public void Shutdown()
{
if (DatabaseSession != null)
{
DatabaseSession = null;
_oracleSessionValues = null;
}
AutoRefreshEnabled = false;
_planItemCollection = null;
SessionItems = null;
QueryCoordinatorLongOperations = null;
SummarySession.Clear();
IsParallel = false;
ExecutionPlanTreeView.RootItem = null;
}
private async Task SetSessionTracing(OracleSessionValues sessionData, string commandText, CancellationToken cancellationToken)
{
using (var connection = new OracleConnection(BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.BindByName = true;
command.CommandText = commandText;
command.AddSimpleParameter("SID", sessionData.Id);
command.AddSimpleParameter("SERIAL", sessionData.Serial);
await connection.OpenAsynchronous(cancellationToken);
await command.ExecuteNonQueryAsynchronous(cancellationToken);
await connection.CloseAsynchronous(cancellationToken);
}
}
}
private async Task KillSession(OracleSessionValues sessionData, CancellationToken cancellationToken)
{
var commandText = $"ALTER SYSTEM KILL SESSION '{sessionData.Id}, {sessionData.Serial}, @{sessionData.Instance}' IMMEDIATE";
await SetSessionTracing(sessionData, commandText, cancellationToken);
}
public async Task <DatabaseSessions> GetAllSessionDataAsync(CancellationToken cancellationToken)
{
var databaseSessions = new DatabaseSessions();
using (var connection = new OracleConnection(BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = OracleDatabaseCommands.SelectBasicSessionInformationCommandText;
await connection.OpenAsynchronous(cancellationToken);
connection.ModuleName = "Database monitor";
using (var reader = await command.ExecuteReaderAsynchronous(CommandBehavior.Default, cancellationToken))
{
databaseSessions.ColumnHeaders = DatabaseSessionColumnHeaders;
var sessions = new Dictionary <int, DatabaseSession>();
while (await reader.ReadAsynchronous(cancellationToken))
{
var oracleSession =
new OracleSessionValues
{
Process = OracleReaderValueConvert.ToString(reader["PROCESS"]),
ExecutionId = OracleReaderValueConvert.ToInt32(reader["SQL_EXEC_ID"]),
Type = (string)reader["TYPE"],
Id = Convert.ToInt32(reader["SID"]),
ExecutionStart = OracleReaderValueConvert.ToDateTime(reader["SQL_EXEC_START"]),
Action = OracleReaderValueConvert.ToString(reader["ACTION"]),
State = (string)reader["STATE"],
Status = (string)reader["STATUS"],
AuditingSessionId = Convert.ToInt64(reader["AUDSID"]),
ChildNumber = OracleReaderValueConvert.ToInt32(reader["SQL_CHILD_NUMBER"]),
ClientInfo = OracleReaderValueConvert.ToString(reader["CLIENT_INFO"]),
CurrentCommandText = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("CURRENT_COMMAND_TEXT"), cancellationToken)),
Event = (string)reader["EVENT"],
FailedOver = (string)reader["FAILED_OVER"],
FailoverMethod = (string)reader["FAILOVER_METHOD"],
FailoverType = (string)reader["FAILOVER_TYPE"],
LockWait = OracleReaderValueConvert.ToString(reader["LOCKWAIT"]),
LogonTime = (DateTime)reader["LOGON_TIME"],
Machine = OracleReaderValueConvert.ToString(reader["MACHINE"]),
Module = OracleReaderValueConvert.ToString(reader["MODULE"]),
OperatingSystemUser = OracleReaderValueConvert.ToString(reader["OSUSER"]),
ParallelDdlStatus = (string)reader["PDDL_STATUS"],
ParallelDmlEnabled = (string)reader["PDML_ENABLED"],
ParallelDmlStatus = (string)reader["PDML_STATUS"],
ParallelQueryStatus = (string)reader["PQ_STATUS"],
Parameter1 = Convert.ToDecimal(reader["P1"]),
Parameter1Text = OracleReaderValueConvert.ToString(reader["P1TEXT"]),
Parameter2 = Convert.ToDecimal(reader["P2"]),
Parameter2Text = OracleReaderValueConvert.ToString(reader["P2TEXT"]),
Parameter3 = Convert.ToDecimal(reader["P3"]),
Parameter3Text = OracleReaderValueConvert.ToString(reader["P3TEXT"]),
OwnerSessionId = OracleReaderValueConvert.ToInt32(reader["OWNER_SID"]),
Port = OracleReaderValueConvert.ToInt32(reader["PORT"]),
PrecedingChildNumber = OracleReaderValueConvert.ToInt32(reader["PREV_CHILD_NUMBER"]),
PrecedingCommandText = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("PRECEDING_COMMAND_TEXT"), cancellationToken)),
PrecedingExecutionId = OracleReaderValueConvert.ToInt32(reader["PREV_EXEC_ID"]),
PrecedingExecutionStart = OracleReaderValueConvert.ToDateTime(reader["PREV_EXEC_START"]),
PrecedingSqlId = OracleReaderValueConvert.ToString(reader["PREV_SQL_ID"]),
ProcessAddress = (string)reader["PADDR"],
Program = OracleReaderValueConvert.ToString(reader["PROGRAM"]),
RemainingTimeMicroseconds = OracleReaderValueConvert.ToInt64(reader["TIME_REMAINING_MICRO"]),
ResourceConsumeGroup = OracleReaderValueConvert.ToString(reader["RESOURCE_CONSUMER_GROUP"]),
SchemaName = OracleReaderValueConvert.ToString(reader["SCHEMANAME"]),
Serial = Convert.ToInt32(reader["SERIAL#"]),
Server = (string)reader["SERVER"],
ServiceName = OracleReaderValueConvert.ToString(reader["SERVICE_NAME"]),
Instance = Convert.ToInt32(reader["INSTANCE_ID"]),
SessionAddress = (string)reader["SADDR"],
SqlId = OracleReaderValueConvert.ToString(reader["SQL_ID"]),
SqlTrace = (string)reader["SQL_TRACE"],
TimeSinceLastWaitMicroseconds = OracleReaderValueConvert.ToInt64(reader["TIME_SINCE_LAST_WAIT_MICRO"]),
TransactionAddress = OracleReaderValueConvert.ToString(reader["TADDR"]),
UserName = OracleReaderValueConvert.ToString(reader["USERNAME"]),
WaitClass = (string)reader["WAIT_CLASS"],
WaitTime = OracleReaderValueConvert.ToInt64(reader["WAIT_TIME"]),
WaitTimeMicroseconds = OracleReaderValueConvert.ToInt64(reader["WAIT_TIME_MICRO"]),
ProcessIdentifier = OracleReaderValueConvert.ToInt32(reader["PID"]),
OperatingSystemIdentifier = OracleReaderValueConvert.ToInt32(reader["SOSID"]),
OperatingSystemProcessIdentifier = OracleReaderValueConvert.ToInt32(reader["SPID"]),
TraceId = OracleReaderValueConvert.ToString(reader["TRACEID"]),
TraceFile = OracleReaderValueConvert.ToString(reader["TRACEFILE"]),
ProgramGlobalAreaUsedMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_USED_MEM"]),
ProgramGlobalAreaAllocatedMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_ALLOC_MEM"]),
ProgramGlobalAreaFreeableMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_FREEABLE_MEM"]),
ProgramGlobalAreaMaximumMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_MAX_MEM"])
};
var databaseSession =
new DatabaseSession
{
Id = oracleSession.Id,
ProviderValues = oracleSession,
Type = String.Equals(oracleSession.Type, "User") ? SessionType.User : SessionType.System,
IsActive = Convert.ToString(oracleSession.Status) == "Active"
};
sessions.Add(databaseSession.Id, databaseSession);
}
foreach (var session in sessions.Values)
{
var ownerSid = ((OracleSessionValues)session.ProviderValues).OwnerSessionId;
if (ownerSid.HasValue && sessions.TryGetValue(ownerSid.Value, out var ownerSession))
{
session.Owner = ownerSession;
ownerSession.ChildSessions.Add(session);
}
}
databaseSessions.Rows = sessions.Values.ToArray();
}
}
await connection.CloseAsynchronous(cancellationToken);
}
return(databaseSessions);
}
private async Task SetSessionTracing(OracleSessionValues sessionData, string commandText, CancellationToken cancellationToken)
{
using (var connection = new OracleConnection(BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.BindByName = true;
command.CommandText = commandText;
command.AddSimpleParameter("SID", sessionData.Id);
command.AddSimpleParameter("SERIAL", sessionData.Serial);
await connection.OpenAsynchronous(cancellationToken);
await command.ExecuteNonQueryAsynchronous(cancellationToken);
await connection.CloseAsynchronous(cancellationToken);
}
}
}
private async Task DisableSessionTracing(OracleSessionValues sessionData, CancellationToken cancellationToken)
{
const string commandText = @"BEGIN
dbms_monitor.session_trace_disable(session_id => :sid, serial_num => :serial);
END;";
await SetSessionTracing(sessionData, commandText, cancellationToken);
}
private async Task EnableSessionTracing(OracleSessionValues sessionData, string traceIdentifier, bool waits, bool binds, CancellationToken cancellationToken)
{
traceIdentifier = traceIdentifier ?? String.Empty;
var commandText =
$@"BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = {OracleTraceIdentifier.Normalize(traceIdentifier)}';
dbms_monitor.session_trace_enable(session_id => :sid, serial_num => :serial, waits => {waits.ToString().ToUpperInvariant()}, binds => {binds.ToString().ToUpperInvariant()});
END;";
await SetSessionTracing(sessionData, commandText, cancellationToken);
}
private async Task KillSession(OracleSessionValues sessionData, CancellationToken cancellationToken)
{
var commandText = $"ALTER SYSTEM KILL SESSION '{sessionData.Id}, {sessionData.Serial}, @{sessionData.Instance}' IMMEDIATE";
await SetSessionTracing(sessionData, commandText, cancellationToken);
}
public async Task<DatabaseSessions> GetAllSessionDataAsync(CancellationToken cancellationToken)
{
var databaseSessions = new DatabaseSessions();
using (var connection = new OracleConnection(BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = OracleDatabaseCommands.SelectBasicSessionInformationCommandText;
await connection.OpenAsynchronous(cancellationToken);
connection.ModuleName = "Database monitor";
using (var reader = await command.ExecuteReaderAsynchronous(CommandBehavior.Default, cancellationToken))
{
databaseSessions.ColumnHeaders = DatabaseSessionColumnHeaders;
var sessions = new Dictionary<int, DatabaseSession>();
while (await reader.ReadAsynchronous(cancellationToken))
{
var oracleSession =
new OracleSessionValues
{
Process = OracleReaderValueConvert.ToString(reader["PROCESS"]),
ExecutionId = OracleReaderValueConvert.ToInt32(reader["SQL_EXEC_ID"]),
Type = (string)reader["TYPE"],
Id = Convert.ToInt32(reader["SID"]),
ExecutionStart = OracleReaderValueConvert.ToDateTime(reader["SQL_EXEC_START"]),
Action = OracleReaderValueConvert.ToString(reader["ACTION"]),
State = (string)reader["STATE"],
Status = (string)reader["STATUS"],
AuditingSessionId = Convert.ToInt64(reader["AUDSID"]),
ChildNumber = OracleReaderValueConvert.ToInt32(reader["SQL_CHILD_NUMBER"]),
ClientInfo = OracleReaderValueConvert.ToString(reader["CLIENT_INFO"]),
CurrentCommandText = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("CURRENT_COMMAND_TEXT"), cancellationToken)),
Event = (string)reader["EVENT"],
FailedOver = (string)reader["FAILED_OVER"],
FailoverMethod = (string)reader["FAILOVER_METHOD"],
FailoverType = (string)reader["FAILOVER_TYPE"],
LockWait = OracleReaderValueConvert.ToString(reader["LOCKWAIT"]),
LogonTime = (DateTime)reader["LOGON_TIME"],
Machine = OracleReaderValueConvert.ToString(reader["MACHINE"]),
Module = OracleReaderValueConvert.ToString(reader["MODULE"]),
OperatingSystemUser = OracleReaderValueConvert.ToString(reader["OSUSER"]),
ParallelDdlStatus = (string)reader["PDDL_STATUS"],
ParallelDmlEnabled = (string)reader["PDML_ENABLED"],
ParallelDmlStatus = (string)reader["PDML_STATUS"],
ParallelQueryStatus = (string)reader["PQ_STATUS"],
Parameter1 = Convert.ToDecimal(reader["P1"]),
Parameter1Text = OracleReaderValueConvert.ToString(reader["P1TEXT"]),
Parameter2 = Convert.ToDecimal(reader["P2"]),
Parameter2Text = OracleReaderValueConvert.ToString(reader["P2TEXT"]),
Parameter3 = Convert.ToDecimal(reader["P3"]),
Parameter3Text = OracleReaderValueConvert.ToString(reader["P3TEXT"]),
OwnerSessionId = OracleReaderValueConvert.ToInt32(reader["OWNER_SID"]),
Port = OracleReaderValueConvert.ToInt32(reader["PORT"]),
PrecedingChildNumber = OracleReaderValueConvert.ToInt32(reader["PREV_CHILD_NUMBER"]),
PrecedingCommandText = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("PRECEDING_COMMAND_TEXT"), cancellationToken)),
PrecedingExecutionId = OracleReaderValueConvert.ToInt32(reader["PREV_EXEC_ID"]),
PrecedingExecutionStart = OracleReaderValueConvert.ToDateTime(reader["PREV_EXEC_START"]),
PrecedingSqlId = OracleReaderValueConvert.ToString(reader["PREV_SQL_ID"]),
ProcessAddress = (string)reader["PADDR"],
Program = OracleReaderValueConvert.ToString(reader["PROGRAM"]),
RemainingTimeMicroseconds = OracleReaderValueConvert.ToInt64(reader["TIME_REMAINING_MICRO"]),
ResourceConsumeGroup = OracleReaderValueConvert.ToString(reader["RESOURCE_CONSUMER_GROUP"]),
SchemaName = OracleReaderValueConvert.ToString(reader["SCHEMANAME"]),
Serial = Convert.ToInt32(reader["SERIAL#"]),
Server = (string)reader["SERVER"],
ServiceName = OracleReaderValueConvert.ToString(reader["SERVICE_NAME"]),
Instance = Convert.ToInt32(reader["INSTANCE_ID"]),
SessionAddress = (string)reader["SADDR"],
SqlId = OracleReaderValueConvert.ToString(reader["SQL_ID"]),
SqlTrace = (string)reader["SQL_TRACE"],
TimeSinceLastWaitMicroseconds = OracleReaderValueConvert.ToInt64(reader["TIME_SINCE_LAST_WAIT_MICRO"]),
TransactionAddress = OracleReaderValueConvert.ToString(reader["TADDR"]),
UserName = OracleReaderValueConvert.ToString(reader["USERNAME"]),
WaitClass = (string)reader["WAIT_CLASS"],
WaitTime = OracleReaderValueConvert.ToInt64(reader["WAIT_TIME"]),
WaitTimeMicroseconds = OracleReaderValueConvert.ToInt64(reader["WAIT_TIME_MICRO"]),
ProcessIdentifier = OracleReaderValueConvert.ToInt32(reader["PID"]),
OperatingSystemIdentifier = OracleReaderValueConvert.ToInt32(reader["SOSID"]),
OperatingSystemProcessIdentifier = OracleReaderValueConvert.ToInt32(reader["SPID"]),
TraceId = OracleReaderValueConvert.ToString(reader["TRACEID"]),
TraceFile = OracleReaderValueConvert.ToString(reader["TRACEFILE"]),
ProgramGlobalAreaUsedMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_USED_MEM"]),
ProgramGlobalAreaAllocatedMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_ALLOC_MEM"]),
ProgramGlobalAreaFreeableMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_FREEABLE_MEM"]),
ProgramGlobalAreaMaximumMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_MAX_MEM"])
};
var databaseSession =
new DatabaseSession
{
Id = oracleSession.Id,
ProviderValues = oracleSession,
Type = String.Equals(oracleSession.Type, "User") ? SessionType.User : SessionType.System,
IsActive = Convert.ToString(oracleSession.Status) == "Active"
};
sessions.Add(databaseSession.Id, databaseSession);
}
foreach (var session in sessions.Values)
{
DatabaseSession ownerSession;
var ownerSid = ((OracleSessionValues)session.ProviderValues).OwnerSessionId;
if (ownerSid.HasValue && sessions.TryGetValue(ownerSid.Value, out ownerSession))
{
session.Owner = ownerSession;
ownerSession.ChildSessions.Add(session);
}
}
databaseSessions.Rows = sessions.Values.ToArray();
}
}
await connection.CloseAsynchronous(cancellationToken);
}
return databaseSessions;
}
public OracleSessionValues Clone()
{
var clone = new OracleSessionValues();
for (var i = 0; i < Values.Length; i++)
{
clone.Values[i] = Values[i];
}
return clone;
}
