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); }
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; }