243 lines
8.0 KiB
SQL
243 lines
8.0 KiB
SQL
-- pdb.sql - List all PDBs, then connect into one PDB
|
|
@@set.sql
|
|
COL cs_con_name NEW_V cs_con_name NOPRI;
|
|
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS cs_con_name FROM DUAL
|
|
/
|
|
-- @@cs_internal/&&cs_set_container_to_cdb_root.
|
|
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 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;
|
|
--
|
|
BREAK ON REPORT;
|
|
COMPUTE SUM OF cpus cpus_perc space_gb space_perc iops iops_perc mbps mbps_perc ON REPORT;
|
|
--
|
|
PRO
|
|
PRO Top 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,
|
|
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.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
|
|
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)
|
|
-- AND r.cpus > 0.1
|
|
-- AND r.space_gb > 0.1
|
|
-- AND r.iops > 0.001
|
|
-- AND r.mbps > 0.001
|
|
ORDER BY
|
|
r.cpus_perc DESC,
|
|
r.space_perc DESC,
|
|
r.iops_perc DESC,
|
|
r.mbps_perc DESC
|
|
/
|
|
--
|
|
CLEAR BREAK COMPUTE;
|
|
--
|
|
COL pdb_name FOR A30 HEA '.|.|PDB Name' PRI;
|
|
COL con_id FOR 990 HEA 'CON|ID' PRI;
|
|
COL running_sessions_limit FOR 9,990.000 HEA 'Running|Sessions|Limit' PRI;
|
|
COL avg_running_sessions FOR 9,990.000 HEA 'Average|Running|Sessions' PRI;
|
|
COL avg_waiting_sessions FOR 9,990.000 HEA 'Average|Waiting|Sessions' PRI;
|
|
COL available_headroom_sessions FOR 9,990.000 HEA 'Available|Headroom|Sessions' PRI;
|
|
-- COL sessions FOR A9 HEA 'Sessions|Parameter' PRI;
|
|
COL sessions FOR 99,990 HEA 'Sessions|Parameter' PRI;
|
|
COL total_size_gb FOR 999,990.000 HEA 'Disk Space|Size (GBs)' PRI;
|
|
COL kiev FOR 9990 HEA 'Kiev|PDB' PRI;
|
|
COL wf FOR 990 HEA 'WF|PDB' PRI;
|
|
COL cpus FOR 9,990.000 HEA 'CPUs' PRI;
|
|
COL iops FOR 999,990.000 HEA 'IOPS' PRI;
|
|
COL mbps FOR 999,990.000 HEA 'MBPS' PRI;
|
|
COL creation_time FOR A19 HEA 'Creation Time' PRI;
|
|
COL open_time FOR A19 HEA 'Open Time' PRI;
|
|
COL open_mode FOR A10 HEA 'Open Mode' PRI;
|
|
--
|
|
BREAK ON REPORT;
|
|
COMPUTE COUNT OF kiev wf con_id ON REPORT;
|
|
COMPUTE SUM OF running_sessions_limit avg_running_sessions avg_waiting_sessions sessions available_headroom_sessions sessions cpus iops mbps total_size_gb ON REPORT;
|
|
--
|
|
PRO
|
|
PRO ALL PDBs
|
|
PRO ~~~~~~~~
|
|
WITH
|
|
c AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
con_id, con_uid, name AS pdb_name, CASE restricted WHEN 'YES' THEN 'RESTRICTED' ELSE open_mode END AS open_mode, CAST(open_time AS DATE) AS open_time, total_size / POWER(10, 9) AS total_size_gb, creation_time -- creation_time does not exist on 12.1
|
|
FROM v$containers
|
|
WHERE 1 = 1
|
|
AND con_id > 2
|
|
AND ROWNUM >= 1 /* MATERIALIZE */
|
|
),
|
|
r AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
con_id, MAX(running_sessions_limit) AS running_sessions_limit, SUM(avg_running_sessions) AS avg_running_sessions, SUM(avg_waiting_sessions) AS avg_waiting_sessions,
|
|
GREATEST(MAX(running_sessions_limit) - SUM(avg_running_sessions), 0) AS available_headroom_sessions,
|
|
(SUM(cpu_consumed_time) / 1000) / (MAX(intsize_csec) / 100) AS cpus,
|
|
SUM(io_requests) / (MAX(intsize_csec) / 100) AS iops,
|
|
SUM(io_megabytes) / (MAX(intsize_csec) / 100) AS mbps,
|
|
ROW_NUMBER() OVER (PARTITION BY con_id ORDER BY SUM(avg_running_sessions) DESC NULLS LAST) AS rn
|
|
FROM v$rsrcmgrmetric
|
|
WHERE intsize_csec > 0
|
|
AND ROWNUM >= 1 /* MATERIALIZE */
|
|
GROUP BY
|
|
con_id
|
|
),
|
|
-- k AS (
|
|
-- SELECT /*+ OPT_PARAM('_px_cdb_view_enabled' 'FALSE') MATERIALIZE NO_MERGE */
|
|
-- con_id
|
|
-- FROM cdb_tables
|
|
-- WHERE table_name = 'KIEVDATASTOREMETADATA'
|
|
-- AND ROWNUM >= 1 /* MATERIALIZE */
|
|
-- GROUP BY
|
|
-- con_id
|
|
-- ),
|
|
-- for better performance:
|
|
k AS (
|
|
SELECT DISTINCT con_id
|
|
FROM CONTAINERS(obj$)
|
|
WHERE name = 'KIEVDATASTOREMETADATA'
|
|
AND namespace = 1
|
|
AND type# = 2
|
|
AND status = 1
|
|
),
|
|
-- w AS (
|
|
-- SELECT /*+ OPT_PARAM('_px_cdb_view_enabled' 'FALSE') MATERIALIZE NO_MERGE */
|
|
-- con_id
|
|
-- FROM cdb_tables
|
|
-- WHERE table_name = 'WORKFLOWINSTANCES'
|
|
-- AND ROWNUM >= 1 /* MATERIALIZE */
|
|
-- GROUP BY
|
|
-- con_id
|
|
-- ),
|
|
-- for better performance:
|
|
w AS (
|
|
SELECT DISTINCT con_id
|
|
FROM CONTAINERS(obj$)
|
|
WHERE name = 'WORKFLOWINSTANCES'
|
|
AND namespace = 1
|
|
AND type# = 2
|
|
AND status = 1
|
|
),
|
|
s AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
con_id, MAX(TO_NUMBER(value)) AS value
|
|
FROM v$system_parameter
|
|
WHERE name = 'sessions'
|
|
AND ROWNUM >= 1 /* MATERIALIZE */
|
|
GROUP BY
|
|
con_id
|
|
),
|
|
p AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
--pdb_uid, MAX(TO_NUMBER(value$)) AS value
|
|
pdb_uid, SUBSTR(MAX(value$), 1, INSTR(MAX(value$)||',', ',') - 1) AS value
|
|
FROM sys.pdb_spfile$
|
|
WHERE pdb_uid > 1
|
|
AND BITAND(NVL(spare2, 0), 1) = 0 -- or: and spare2=0 (as per wilko.edens@gmail.com)
|
|
AND LOWER(name) = 'sessions'
|
|
AND ROWNUM >= 1 /* MATERIALIZE */
|
|
GROUP BY
|
|
pdb_uid
|
|
)
|
|
SELECT /*+ ORDERED */
|
|
c.pdb_name, c.con_id,
|
|
r.running_sessions_limit,
|
|
r.avg_running_sessions, r.available_headroom_sessions, r.avg_waiting_sessions,
|
|
-- COALESCE(s.value, p.value) AS sessions,
|
|
-- LPAD(COALESCE(TO_CHAR(s.value), p.value), 9, ' ') AS sessions,
|
|
COALESCE(s.value, TO_NUMBER(REGEXP_REPLACE(p.value, '[^0-9]', ''))) AS sessions,
|
|
r.cpus, c.total_size_gb, r.iops, r.mbps,
|
|
CASE WHEN k.con_id IS NOT NULL THEN 1 END AS kiev,
|
|
CASE WHEN w.con_id IS NOT NULL THEN 1 END AS wf,
|
|
-- '|' AS "|",
|
|
-- c.creation_time, c.open_time,
|
|
c.open_mode
|
|
FROM c, r, k, w, s, p
|
|
WHERE r.con_id(+) = c.con_id
|
|
--AND r.rn(+) = 1 -- expecting only one row anyways!
|
|
AND k.con_id(+) = c.con_id
|
|
AND w.con_id(+) = c.con_id
|
|
AND s.con_id(+) = c.con_id
|
|
AND p.pdb_uid(+) = c.con_uid
|
|
ORDER BY
|
|
c.pdb_name
|
|
/
|
|
PRO
|
|
PRO Running Sessions Limit: Resource Manager Utilization Limit (CPU cap after which throttling stars.)
|
|
PRO Average Running Sessions: AAS on CPU.
|
|
PRO Available Headroom Sessions: Potential AAS slots available for sessions on CPU.
|
|
PRO Average Waiting Sessions: AAS wating on Scheduler (Resource Manager throttling.)
|
|
--
|
|
PRO
|
|
PRO 1. Enter PDB Name: [{&&cs_con_name.}|PDB Name]
|
|
DEF pdb_name = '&1.';
|
|
UNDEF 1 2 3 4 5 6 7 8 9 10 11 12;
|
|
SELECT COALESCE(TRIM('&&pdb_name.'), '&&cs_con_name.') AS cs_con_name FROM DUAL
|
|
/
|
|
--
|
|
-- @@cs_internal/&&cs_set_container_to_curr_pdb.
|
|
ALTER SESSION SET CONTAINER = &&cs_con_name.;
|
|
--
|
|
PRO
|
|
PRO Connected to: &cs_con_name.
|
|
PRO
|
|
--
|
|
CLEAR BREAK COLUMNS COMPUTE;
|