Pseudocode expresses a calculation as short, ordered instructions without requiring a conversational explanation. In database prompting, it tells a chatbot how to transform values, apply conditions, and reduce redundant SQL. section 10.6.2
Memory calculation prompt
Request a SQL query that retrieves selected memory columns, converts values ending in M or G to megabytes, and calculates Total Memory in Use: when memory_max_target is not zero, use it; otherwise add sga_max_size and pga_aggregate_target. Ask for reduced redundancy. 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
A compact listing establishes the selected columns and table before the calculation logic is added. 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
A CTE-based listing shows how the prompted conversion and total-memory calculation can become executable SQL. section 10.6.2
