Memory usage measures how much physical memory a system or workload occupies. In database administration, it can be calculated from Oracle memory settings, converted into megabytes, compared with available physical memory, and filtered to highlight instances above 75% utilization. section 10.6.2
A practical calculation follows these steps:
- Convert values ending in M directly to megabytes and values ending in G by multiplying by 1024. section 10.6.2
- Use memory_max_target when it is nonzero; otherwise add sga_max_size and pga_aggregate_target. section 10.6.2
- Divide total memory in use by physical memory and retain results above 75%. section 10.6.2
SELECT
[SERVERNAME], [DB_NAME], [memory_target], [memory_max_target],
[pga_aggregate_target], [sga_max_size], [PhysicalMemory]
FROM
[dbo].[vOracleMemoryConfigs]
server1 DB1 0 0 2000M 6G 15297
Use the sample SELECT statement and row to establish the configuration columns and the mixed M/G value format before generating calculations. section 10.6.2
-- CTE for converting memory settings to MB and calculating total memory
in use
WITH MemoryCalculations AS (
SELECT
[SERVERNAME],
[DB_NAME],
[PhysicalMemory],
CASE
WHEN RIGHT([memory_max_target], 1) = 'M' THEN CAST(SUBSTRING(
[memory_max_target], 1, LEN([memory_max_target]) - 1) AS INT)
WHEN RIGHT([memory_max_target], 1) = 'G' THEN CAST(SUBSTRING(
[memory_max_target], 1, LEN([memory_max_target]) - 1)
AS INT) * 1024
ELSE [memory_max_target]
END AS [memory_max_target_MB],
...
),
-- Second CTE for calculating total memory in use
FinalCalculations AS (
SELECT
[SERVERNAME],
[DB_NAME],
[PhysicalMemory],
CASE
WHEN [memory_max_target_MB] != 0 THEN [memory_max_target_MB]
ELSE [sga_max_size_MB] + [pga_aggregate_limit_MB]
END AS [TotalMemoryInUse_MB]
FROM
MemoryCalculations
)
SELECT
*,
([TotalMemoryInUse_MB] * 1.0 / [PhysicalMemory]) * 100
AS [TotalMemoryInUse_Percentage]
FROM
FinalCalculations
WHERE
([TotalMemoryInUse_MB] * 1.0 / [PhysicalMemory]) * 100 > 75
Use the CTE pattern to convert memory settings and calculate total memory in use in separate, inspectable stages. section 10.6.2
