We have a METRICS table here that contains daily max values for our database fleet (CPU, Memory, I/O, etc, etc.)
I can query for MAX_CPU (for example) like:
SELECT
DATE_STAMP,
DB_NAME,
MAX_CPU
FROM
METRICS
WHERE
DATE_STAMP > (sysdate -4)
ORDER BY
DATE_STAMP
;
and get results such as:
DATE_STAMP DB_NAME MAX_CPU
---------- ---------------- -------
2023-07-22 STAG 2.43
2023-07-22 DEVL 1.02
2023-07-22 PROD 4.38
2023-07-22 TEST 1.08
2023-07-23 STAG 1.73
2023-07-23 PROD 4.10
2023-07-23 DEVL 1.97
2023-07-23 TEST 2.00
2023-07-24 TEST 1.08
2023-07-24 STAG 1.20
2023-07-24 DEVL 2.11
2023-07-24 PROD 4.01
( Note: We actually have about 50 databases, I've simplified this and renamed the DB's for security purposes. )
What I would like to do here, is pivot these results per date, per database, like this:
DATE_STAMP DEVL TEST PROD STAG
---------- ---- ---- ---- ----
2023-07-22 1.02 1.08 4.38 2.43
2023-07-23 1.97 2.00 4.10 1.73
2023-07-24 2.11 1.08 4.01 1.20
I guess the complicating factors here are:
1.) I don't want to hard-code the DB_NAMES in an "IN" clause like:
FOR DB_NAME IN ('DEVL', 'TEST', 'PROD', 'STAG')
because we have so many DB's, and the list grows and shrinks over time.
2.) I don't have or need an aggregate function in the PIVOT clause, because I'm not counting, summing, average, etc., any of this data.
This seems like it should be simple to do and maybe I'm overthinking it, but I feel like I'm missing a critical piece here.
I've been combing through stackoverflow, blog posts, and Oracle documentation all day trying to find that missing link, but keep coming up short.
I am not a PL/SQL developer, but have tried to figure this out on my own in order to learn. That said, I need to get back to my regular duties and can't keep messing around with this forever.
