public override async Task MapReaderData(OracleDataReader reader, CancellationToken cancellationToken)
{
while (await reader.ReadAsynchronous(cancellationToken))
{
var degreeOfParallelismRaw = (string)reader["DEGREE"];
var indexDetails =
new IndexDetailsModel
{
Owner = (string)reader["OWNER"],
Name = (string)reader["INDEX_NAME"],
Type = TextInfo.ToTitleCase(((string)reader["INDEX_TYPE"]).ToLowerInvariant()),
IsUnique = (string)reader["UNIQUENESS"] == "UNIQUE",
Compression = TextInfo.ToTitleCase(((string)reader["COMPRESSION"]).ToLowerInvariant()),
PrefixLength = OracleReaderValueConvert.ToInt32(reader["PREFIX_LENGTH"]),
Logging = OracleReaderValueConvert.ToString(reader["LOGGING"]) == "LOGGING",
ClusteringFactor = OracleReaderValueConvert.ToInt64(reader["CLUSTERING_FACTOR"]),
Status = TextInfo.ToTitleCase(((string)reader["STATUS"]).ToLowerInvariant()),
Rows = OracleReaderValueConvert.ToInt64(reader["NUM_ROWS"]),
SampleRows = OracleReaderValueConvert.ToInt64(reader["SAMPLE_SIZE"]),
DistinctKeys = OracleReaderValueConvert.ToInt64(reader["DISTINCT_KEYS"]),
LastAnalyzed = OracleReaderValueConvert.ToDateTime(reader["LAST_ANALYZED"]),
Blocks = OracleReaderValueConvert.ToInt32(reader["BLOCKS"]),
LeafBlocks = OracleReaderValueConvert.ToInt32(reader["LEAF_BLOCKS"]),
Bytes = OracleReaderValueConvert.ToInt64(reader["BYTES"]),
DegreeOfParallelism = degreeOfParallelismRaw == "DEFAULT" ? (int?)null : Convert.ToInt32(degreeOfParallelismRaw.Trim()),
TablespaceName = OracleReaderValueConvert.ToString(reader["TABLESPACE_NAME"])
};
DataModel.IndexDetails.Add(indexDetails);
}
}
public override async Task MapReaderData(OracleDataReader reader, CancellationToken cancellationToken)
{
if (!await reader.ReadAsynchronous(cancellationToken))
{
return;
}
DataModel.LargeObjectBytes = OracleReaderValueConvert.ToInt64(reader["LOB_BYTES"]);
DataModel.AllocatedBytes = OracleReaderValueConvert.ToInt64(reader["IN_ROW_BYTES"]) + (DataModel.LargeObjectBytes ?? 0);
}
public override async Task MapReaderData(OracleDataReader reader, CancellationToken cancellationToken)
{
if (!await reader.ReadAsynchronous(cancellationToken))
{
return;
}
DataModel.InMemoryCompression = OracleReaderValueConvert.ToString(reader["INMEMORY_COMPRESSION"]);
DataModel.SetInMemoryAllocationStatus(
OracleReaderValueConvert.ToInt64(reader["INMEMORY_SIZE"]),
OracleReaderValueConvert.ToInt64(reader["BYTES"]),
OracleReaderValueConvert.ToInt64(reader["BYTES_NOT_POPULATED"]),
OracleReaderValueConvert.ToString(reader["POPULATE_STATUS"]));
}
private static void MapPartitionSegmentData(IDataRecord reader, PartitionDetailsModelBase model)
{
var highValue = OracleReaderValueConvert.ToString(reader["HIGH_VALUE"]);
model.HighValue = highValue.Length > HighValueMaxLength ? $"{highValue.Substring(0, HighValueMaxLength)}{CellValueConverter.Ellipsis}" : highValue;
model.TablespaceName = OracleReaderValueConvert.ToString(reader["TABLESPACE_NAME"]);
model.Logging = (string)reader["LOGGING"] == "YES";
model.Compression = TextInfo.ToTitleCase(((string)reader["COMPRESSION"]).ToLowerInvariant());
model.RowCount = OracleReaderValueConvert.ToInt64(reader["NUM_ROWS"]);
model.SampleRows = OracleReaderValueConvert.ToInt64(reader["SAMPLE_SIZE"]);
model.LastAnalyzed = OracleReaderValueConvert.ToDateTime(reader["LAST_ANALYZED"]);
model.BlockCount = OracleReaderValueConvert.ToInt32(reader["BLOCKS"]);
model.AverageRowSize = OracleReaderValueConvert.ToInt32(reader["AVG_ROW_LEN"]);
model.InMemoryCompression = OracleReaderValueConvert.ToString(reader["INMEMORY_COMPRESSION"]);
}
private async Task <TItem> CreatePlanItem(OracleDataReader reader, ICollection <int> inactiveMap, CancellationToken cancellationToken)
{
var time = OracleReaderValueConvert.ToInt32(reader["TIME"]);
var otherData = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("OTHER_XML"), cancellationToken));
var objectName = OracleReaderValueConvert.ToString(reader["OBJECT_NAME"]);
var item =
new TItem
{
Id = Convert.ToInt32(reader["ID"]),
ParentId = OracleReaderValueConvert.ToInt32(reader["PARENT_ID"]),
Depth = Convert.ToInt32(reader["DEPTH"]),
Operation = (string)reader["OPERATION"],
Options = OracleReaderValueConvert.ToString(reader["OPTIONS"]),
Optimizer = OracleReaderValueConvert.ToString(reader["OPTIMIZER"]),
ObjectOwner =
objectName.StartsWith(":TQ")
? String.Empty
: OracleReaderValueConvert.ToString(reader["OBJECT_OWNER"]),
ObjectName = objectName,
ObjectAlias = OracleReaderValueConvert.ToString(reader["OBJECT_ALIAS"]),
ObjectType = OracleReaderValueConvert.ToString(reader["OBJECT_TYPE"]),
Cost = OracleReaderValueConvert.ToInt64(reader["COST"]),
Cardinality = OracleReaderValueConvert.ToInt64(reader["CARDINALITY"]),
Bytes = OracleReaderValueConvert.ToInt64(reader["BYTES"]),
PartitionStart = OracleReaderValueConvert.ToString(reader["PARTITION_START"]),
PartitionStop = OracleReaderValueConvert.ToString(reader["PARTITION_STOP"]),
Distribution = OracleReaderValueConvert.ToString(reader["DISTRIBUTION"]),
CpuCost = OracleReaderValueConvert.ToInt64(reader["CPU_COST"]),
IoCost = OracleReaderValueConvert.ToInt64(reader["IO_COST"]),
TempSpace = OracleReaderValueConvert.ToInt64(reader["TEMP_SPACE"]),
AccessPredicates = OracleReaderValueConvert.ToString(reader["ACCESS_PREDICATES"]),
FilterPredicates = OracleReaderValueConvert.ToString(reader["FILTER_PREDICATES"]),
Time = time.HasValue ? TimeSpan.FromSeconds(time.Value) : (TimeSpan?)null,
QueryBlockName = OracleReaderValueConvert.ToString(reader["QBLOCK_NAME"]),
Other = String.IsNullOrEmpty(otherData) ? null : XElement.Parse(otherData)
};
ResolveInactiveNodes(item, inactiveMap);
FillData(reader, item);
return(item);
}
public override async Task MapReaderData(OracleDataReader reader, CancellationToken cancellationToken)
{
if (!await reader.ReadAsynchronous(cancellationToken))
{
return;
}
var loggingRaw = reader["LOGGING"];
DataModel.RowCount = OracleReaderValueConvert.ToInt32(reader["NUM_ROWS"]);
DataModel.LastAnalyzed = OracleReaderValueConvert.ToDateTime(reader["LAST_ANALYZED"]);
DataModel.AverageRowSize = OracleReaderValueConvert.ToInt32(reader["AVG_ROW_LEN"]);
DataModel.BlockCount = OracleReaderValueConvert.ToInt32(reader["BLOCKS"]);
DataModel.Compression = OracleReaderValueConvert.ToString(reader["COMPRESSION"]);
DataModel.Organization = OracleReaderValueConvert.ToString(reader["ORGANIZATION"]);
DataModel.ParallelDegree = OracleReaderValueConvert.ToString(reader["DEGREE"]);
DataModel.ClusterName = OracleReaderValueConvert.ToString(reader["CLUSTER_NAME"]);
DataModel.TablespaceName = OracleReaderValueConvert.ToString(reader["TABLESPACE_NAME"]);
DataModel.SampleRows = OracleReaderValueConvert.ToInt64(reader["SAMPLE_SIZE"]);
DataModel.Logging = loggingRaw == DBNull.Value ? (bool?)null : String.Equals((string)loggingRaw, "YES");
DataModel.IsTemporary = (string)reader["TEMPORARY"] == "Y";
}
protected override void FillData(IDataRecord reader, ExecutionStatisticsPlanItem item)
{
item.Timestamp = Convert.ToDateTime(reader["TIMESTAMP"]);
item.Executions = Convert.ToInt32(reader["EXECUTIONS"]);
item.LastStarts = OracleReaderValueConvert.ToInt32(reader["LAST_STARTS"]);
item.TotalStarts = OracleReaderValueConvert.ToInt32(reader["STARTS"]);
item.LastOutputRows = OracleReaderValueConvert.ToInt64(reader["LAST_OUTPUT_ROWS"]);
item.TotalOutputRows = OracleReaderValueConvert.ToInt64(reader["OUTPUT_ROWS"]);
item.LastConsistentReadBufferGets = OracleReaderValueConvert.ToInt64(reader["LAST_CR_BUFFER_GETS"]);
item.TotalConsistentReadBufferGets = OracleReaderValueConvert.ToInt64(reader["CR_BUFFER_GETS"]);
item.LastCurrentReadBufferGets = OracleReaderValueConvert.ToInt64(reader["LAST_CU_BUFFER_GETS"]);
item.TotalCurrentReadBufferGets = OracleReaderValueConvert.ToInt64(reader["CU_BUFFER_GETS"]);
item.LastDiskReads = OracleReaderValueConvert.ToInt64(reader["LAST_DISK_READS"]);
item.TotalDiskReads = OracleReaderValueConvert.ToInt64(reader["DISK_READS"]);
item.LastDiskWrites = OracleReaderValueConvert.ToInt64(reader["LAST_DISK_WRITES"]);
item.TotalDiskWrites = OracleReaderValueConvert.ToInt64(reader["DISK_WRITES"]);
var lastElapsedMicroseconds = OracleReaderValueConvert.ToInt64(reader["LAST_ELAPSED_TIME"]);
item.LastElapsedTime = lastElapsedMicroseconds.HasValue ? TimeSpan.FromMilliseconds(lastElapsedMicroseconds.Value / 1000d) : (TimeSpan?)null;
var totalElapsedMicroseconds = OracleReaderValueConvert.ToInt64(reader["ELAPSED_TIME"]);
item.TotalElapsedTime = totalElapsedMicroseconds.HasValue ? TimeSpan.FromMilliseconds(totalElapsedMicroseconds.Value / 1000d) : (TimeSpan?)null;
item.WorkAreaSizingPolicy = TextInfo.ToTitleCase(OracleReaderValueConvert.ToString(reader["POLICY"]));
item.EstimatedOptimalSizeBytes = OracleReaderValueConvert.ToInt64(reader["ESTIMATED_OPTIMAL_SIZE"]);
item.EstimatedOnePassSizeBytes = OracleReaderValueConvert.ToInt64(reader["ESTIMATED_ONEPASS_SIZE"]);
item.LastMemoryUsedBytes = OracleReaderValueConvert.ToInt64(reader["LAST_MEMORY_USED"]);
item.LastExecutionMethod = TextInfo.ToTitleCase(OracleReaderValueConvert.ToString(reader["LAST_EXECUTION"]));
item.LastParallelDegree = OracleReaderValueConvert.ToInt32(reader["LAST_DEGREE"]);
item.TotalWorkAreaExecutions = OracleReaderValueConvert.ToInt32(reader["TOTAL_EXECUTIONS"]);
item.OptimalWorkAreaExecutions = OracleReaderValueConvert.ToInt32(reader["OPTIMAL_EXECUTIONS"]);
item.OnePassWorkAreaExecutions = OracleReaderValueConvert.ToInt32(reader["ONEPASS_EXECUTIONS"]);
item.MultiPassWorkAreaExecutions = OracleReaderValueConvert.ToInt32(reader["MULTIPASSES_EXECUTIONS"]);
var activeTime = OracleReaderValueConvert.ToInt64(reader["ACTIVE_TIME"]);
item.ActiveWorkAreaTime = activeTime.HasValue ? TimeSpan.FromMilliseconds(activeTime.Value * 10) : (TimeSpan?)null;
item.MaxTemporarySizeBytes = OracleReaderValueConvert.ToInt64(reader["MAX_TEMPSEG_SIZE"]);
item.LastTemporarySizeBytes = OracleReaderValueConvert.ToInt64(reader["LAST_TEMPSEG_SIZE"]);
}
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);
}