90 lines
2.9 KiB
SQL
90 lines
2.9 KiB
SQL
-- pdb_move_list.sql - List all PDBs and highligh as "MOVE" the 1/3 at the middle in terms of CPU
|
|
@@set.sql
|
|
ALTER SESSION SET container = CDB$ROOT;
|
|
--
|
|
COL pdb_name FOR A30 HEA 'PDB Name' PRI;
|
|
COL con_id FOR 990 HEA 'CON|ID' PRI;
|
|
COL cpus FOR 9,990.000 HEA 'CPUs' PRI;
|
|
COL cpus_perc FOR 990.0 HEA 'CPUs|Perc%' PRI;
|
|
COL cpus_rank FOR 990 HEA 'CPUs|Rank' PRI;
|
|
COL position FOR 990 HEA 'Pos' PRI;
|
|
COL space_gb FOR 99,990.000 HEA 'Space|GBs' PRI;
|
|
COL space_perc FOR 990.0 HEA 'Space|Perc%' PRI;
|
|
COL space_rank FOR 990 HEA 'Space|Rank' PRI;
|
|
COL iops FOR 9,999,990.000 HEA 'IOPS' PRI;
|
|
COL iops_perc FOR 990.0 HEA 'IOPS|Perc%' PRI;
|
|
COL iops_rank FOR 990 HEA 'IOPS|Rank' PRI;
|
|
COL mbps FOR 999,990.000 HEA 'MBPS' PRI;
|
|
COL mbps_perc FOR 990.0 HEA 'MBPS|Perc%' PRI;
|
|
COL mbps_rank FOR 990 HEA 'MBPS|Rank' PRI;
|
|
COL move_pdb FOR A12 HEA 'MOVE PDB' PRI;
|
|
--
|
|
BREAK ON REPORT;
|
|
COMPUTE SUM OF cpus cpus_perc space_gb space_perc iops iops_perc mbps mbps_perc ON REPORT;
|
|
--
|
|
PRO
|
|
PRO All PDBs
|
|
PRO ~~~~~~~~
|
|
WITH
|
|
rsrcmgrmetric AS (
|
|
SELECT r.con_id,
|
|
c.name AS pdb_name,
|
|
(SUM(r.cpu_consumed_time) / 1000) / (MAX(r.intsize_csec) / 100) AS cpus,
|
|
MAX(c.total_size) / POWER(2,30) AS space_gb,
|
|
SUM(r.io_requests) / (MAX(r.intsize_csec) / 100) AS iops,
|
|
SUM(r.io_megabytes) / (MAX(r.intsize_csec) / 100) AS mbps
|
|
FROM v$rsrcmgrmetric r,
|
|
v$containers c
|
|
WHERE r.con_id > 2 -- exclude CDB$ROOT
|
|
AND r.intsize_csec > 0
|
|
AND c.con_id = r.con_id
|
|
GROUP BY
|
|
r.con_id, -- needed since there are multiple consumer groups (usually 3) per time slice
|
|
c.name
|
|
),
|
|
rsrcmgrmetric_ext AS (
|
|
SELECT con_id,
|
|
pdb_name,
|
|
cpus,
|
|
100 * cpus / NULLIF(SUM(cpus) OVER(), 0) AS cpus_perc,
|
|
RANK() OVER(ORDER BY cpus DESC) AS cpus_rank,
|
|
100 * ROW_NUMBER() OVER(ORDER BY cpus DESC)/COUNT(*) OVER() AS position,
|
|
space_gb,
|
|
100 * space_gb / NULLIF(SUM(space_gb) OVER(), 0) AS space_perc,
|
|
RANK() OVER(ORDER BY space_gb DESC) AS space_rank,
|
|
iops,
|
|
100 * iops / NULLIF(SUM(iops) OVER(), 0) AS iops_perc,
|
|
RANK() OVER(ORDER BY iops DESC) AS iops_rank,
|
|
mbps,
|
|
100 * mbps / NULLIF(SUM(mbps) OVER(), 0) AS mbps_perc,
|
|
RANK() OVER(ORDER BY mbps DESC) AS mbps_rank
|
|
FROM rsrcmgrmetric
|
|
)
|
|
SELECT r.cpus,
|
|
r.cpus_perc,
|
|
r.cpus_rank,
|
|
-- r.position,
|
|
r.space_gb,
|
|
r.space_perc,
|
|
r.space_rank,
|
|
r.iops,
|
|
r.iops_perc,
|
|
r.iops_rank,
|
|
r.mbps,
|
|
r.mbps_perc,
|
|
r.mbps_rank,
|
|
r.pdb_name,
|
|
r.con_id,
|
|
CASE WHEN r.position BETWEEN 33 AND 66 THEN '*** MOVE ***' END AS move_pdb
|
|
FROM rsrcmgrmetric_ext r
|
|
-- WHERE (r.cpus_perc > 1 OR r.space_perc > 1 OR r.iops_perc > 1 OR r.mbps_perc > 1)
|
|
-- AND (r.cpus > 0.1 OR r.space_gb > 0.1 OR r.iops > 0.001 OR r.mbps > 0.001)
|
|
ORDER BY
|
|
r.cpus_perc DESC,
|
|
r.space_perc DESC,
|
|
r.iops_perc DESC,
|
|
r.mbps_perc DESC
|
|
/
|
|
--
|
|
CLEAR BREAK COLUMNS COMPUTE;
|