89 lines
2.5 KiB
MySQL
89 lines
2.5 KiB
MySQL
|
----------------------------------------------------------------------------------------
|
||
|
|
--
|
||
|
|
-- File name: cs_sqlarea_per_pdb.sql
|
||
|
|
--
|
||
|
|
-- Purpose: SQL Area per PDB
|
||
|
|
--
|
||
|
|
-- Author: Carlos Sierra
|
||
|
|
--
|
||
|
|
-- Version: 2021/10/30
|
||
|
|
--
|
||
|
|
-- Usage: Execute connected to CDB or PDB.
|
||
|
|
--
|
||
|
|
-- Example: $ sqlplus / as sysdba
|
||
|
|
-- SQL> @cs_sqlarea_per_pdb.sql
|
||
|
|
--
|
||
|
|
-- Notes: Developed and tested on 19c.
|
||
|
|
--
|
||
|
|
---------------------------------------------------------------------------------------
|
||
|
|
--
|
||
|
|
@@cs_internal/cs_primary.sql
|
||
|
|
@@cs_internal/cs_set.sql
|
||
|
|
@@cs_internal/cs_def.sql
|
||
|
|
@@cs_internal/cs_file_prefix.sql
|
||
|
|
--
|
||
|
|
DEF cs_script_name = 'cs_sqlarea_per_pdb';
|
||
|
|
--
|
||
|
|
SELECT '&&cs_file_prefix._&&cs_script_name.' cs_file_name FROM DUAL;
|
||
|
|
--
|
||
|
|
@@cs_internal/cs_spool_head.sql
|
||
|
|
PRO SQL> @&&cs_script_name..sql
|
||
|
|
@@cs_internal/cs_spool_id.sql
|
||
|
|
--
|
||
|
|
@@cs_internal/&&cs_set_container_to_cdb_root.
|
||
|
|
--
|
||
|
|
COL pdb_name FOR A30;
|
||
|
|
COL sharable_mem FOR 999,999,999,990;
|
||
|
|
COL persistent_mem FOR 999,999,999,990;
|
||
|
|
COL runtime_mem FOR 999,999,999,990;
|
||
|
|
COL statements FOR 999,999,999,990;
|
||
|
|
COL version_count FOR 999,999,999,990;
|
||
|
|
COL loaded_versions FOR 999,999,999,990;
|
||
|
|
COL open_versions FOR 999,999,999,990;
|
||
|
|
COL users_opening FOR 999,999,999,990;
|
||
|
|
COL loads FOR 999,999,999,990;
|
||
|
|
COL invalidations FOR 999,999,999,990;
|
||
|
|
COL executions FOR 999,999,999,990;
|
||
|
|
COL parse_calls FOR 999,999,999,990;
|
||
|
|
--
|
||
|
|
BREAK ON REPORT;
|
||
|
|
COMPUTE SUM OF statements sharable_mem persistent_mem runtime_mem version_count loaded_versions open_versions users_opening users_executing loads invalidations executions parse_calls ON REPORT;
|
||
|
|
--
|
||
|
|
PRO
|
||
|
|
PRO SQL Area per PDB
|
||
|
|
PRO ~~~~~~~~~~~~~~~~
|
||
|
|
SELECT c.name AS pdb_name,
|
||
|
|
COUNT(*) AS statements,
|
||
|
|
SUM(a.sharable_mem) AS sharable_mem,
|
||
|
|
SUM(a.persistent_mem) AS persistent_mem,
|
||
|
|
SUM(a.runtime_mem) AS runtime_mem,
|
||
|
|
SUM(a.version_count) AS version_count,
|
||
|
|
SUM(a.loaded_versions) AS loaded_versions,
|
||
|
|
SUM(a.open_versions) AS open_versions,
|
||
|
|
SUM(a.users_opening) AS users_opening,
|
||
|
|
SUM(a.users_executing) AS users_executing,
|
||
|
|
SUM(a.loads) AS loads,
|
||
|
|
SUM(a.invalidations) AS invalidations,
|
||
|
|
SUM(a.executions) AS executions,
|
||
|
|
SUM(a.parse_calls) AS parse_calls
|
||
|
|
FROM v$sqlarea a, v$containers c
|
||
|
|
WHERE a.con_id > 2
|
||
|
|
AND c.con_id = a.con_id
|
||
|
|
GROUP BY
|
||
|
|
c.name
|
||
|
|
ORDER BY
|
||
|
|
c.name
|
||
|
|
/
|
||
|
|
--
|
||
|
|
CLEAR BREAK COMPUTE;
|
||
|
|
--
|
||
|
|
PRO
|
||
|
|
PRO SQL> @&&cs_script_name..sql
|
||
|
|
--
|
||
|
|
@@cs_internal/cs_spool_tail.sql
|
||
|
|
--
|
||
|
|
@@cs_internal/&&cs_set_container_to_curr_pdb.
|
||
|
|
--
|
||
|
|
@@cs_internal/cs_undef.sql
|
||
|
|
@@cs_internal/cs_reset.sql
|
||
|
|
--
|