The purpose of this post is to demonstrate how to determine process memory usage of pluggable databases.
Pre requisites
– On RAC configuration, the scripts should be run on each instances since only V$ views are used to provide a accurate memory usage for individual database instance.
– The scripts must be run in SQL*Plus session since specific SQL*Plus functions are used.
– Scripts must be run as sysdba or database user account having access to CDB*, DBA* and V$ tables.
– Example scripts and their output are provided to demonstrate that purpose.
– A file containing all sql is included as an attachment as well as example spool output.
– The queries are intended to be run on the root container database (CDB) containing pluggable databases, since many of the queries refer to container specific views or columns.
Monitoring Process Memory Usage on Pluggable Databases
The CDB and all pluggable databases (PDBs) share a single database instance composed of the system global area (SGA) and background processes. Because of this sharing of memory resources, it would be desirable to distinguish resource usage among databases sharing the instance. When possible, a query will be provided that breaks down process memory (PGA) associated with each pluggable database.
The ROUND function is used to make query results more presentable in terms of MegaBytes. Because of this rounding of values, results may not match exactly when comparing against different views such as V$PROCESS and V$SESSTAT.
set linesize 150 set pagesize 3000 set NUMWIDTH 15 col Parameter format a30 col component format a28 COLUMN DEFAULT_ATTR FORMAT A7 COLUMN OWNER FORMAT A15 COLUMN OBJECT_NAME FORMAT A15 COLUMN ALL_CONTAINERS FORMAT A3 COLUMN CONTAINER_NAME FORMAT A10 COLUMN CON_ID FORMAT 999 COLUMN pdb_name FORMAT A20 COLUMN memory Format A25 COLUMN spid HEADING 'OSpid' FORMAT a8 COLUMN pid HEADING 'Orapid' FORMAT 999999 COLUMN sid HEADING 'Sess id' FORMAT 99999 COLUMN serial# HEADING 'Serial#' FORMAT 999999 COLUMN status HEADING 'Status' FORMAT a8 COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 999,999,999 COLUMN pga_used_mem HEADING 'PGA used' FORMAT 999,999,999 COLUMN pga_max_mem HEADING 'PGA Max' FORMAT 999,999,999 COLUMN username HEADING 'oracleuser' FORMAT a12 COLUMN osuser HEADING 'OS user' FORMAT a12 COLUMN program HEADING 'Program' FORMAT a24 COLUMN Mbytes Heading 'Mbytes' FORMAT 999,999,999 COLUMN name FORMAT A22
--Set date formats in session alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; --Date/time of script run select sysdate from dual;
This query will indicate if the scripts are being run on the root container database. The results from con_name should indicate the container name CDB$ROOT and container id 1 for con_id.
The command show pdbs will show all PDBs associated with the CDB, their open status and if database is opened in restricted mode. This can be used to identify the PDB name associated with the container id (identified by con_id) which is used in many of the queries to breakdown memory usage to a specific container. If issued on a non-CDB Database, these commands will return NULL.
These example results shown below indicate four additional PDBs in addition to the seed PDB associated with this CDB. The PDB id 5 named PDB_COPY is not open. All other PDBs are open.
show con_name show con_id show pdbs CON_NAME ------------------------------ CDB$ROOT CON_ID ------------------------------ 1
CON_ID CON_NAME OPEN MODE RESTRICTED
--------------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_SS READ WRITE NO
4 PDB1 READ WRITE NO
5 PDB_COPY MOUNTED
6 PDB2 READ WRITE NO
This query provides name of the root container database CDB. In this example, named CDB1.
select name, cdb, con_id from v$database; NAME CDB CON_ID ---------------------- --- ------ CDB1 YES 0
This query shows additional id information about each container as well as database status.
--Information About Each Container SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID; NAME CON_ID OPEN_MODE RES DBID CON_UID GUID ---------------------- ------ ---------- --- --------------- --------------- -------------------------------- CDB$ROOT 1 READ WRITE NO 762218087 1 C40F9B49FC9D19E0E0430BAAE80AFF01 PDB$SEED 2 READ ONLY NO 4031134518 4031134518 C40F9B49FC9C19E0E0430BAAE80AFF01 PDB_SS 3 READ WRITE NO 1556201860 1556201860 C4109F71E0095A2FE0430BAAE80A6619 PDB1 4 READ WRITE NO 3296179875 3296179875 C4AFBF825964352DE04362F519904F91 PDB_COPY 5 MOUNTED 1667449117 1667449117 D14DA20BBD781142E0430100007FBAFE PDB2 6 READ WRITE NO 3868752707 3868752707 D14DA20BBD7C1142E0430100007FBAFE
This query on V$SESSTAT shows both the current process size indicated by “session pga memory” and maximum process size during life of that process indicated by “session pga memory max”.
The AND clause “s.value > 20000000” is limiting the results to process sizes over 20MB. If you want to see all processes, remove this clause or alter value to restrict on different pga size.
These example results below shows container 4 had OS pid 4356 process reach 1,163MB. Container 6 had OS pid 8367 process reach 1,386MB. The CDB had OS pid 7303 process reach 940MB. Currently, only one process on this instance is at least 20MB in size. All other processes are currently less than 20MB size including these other processes listed that at one time in their life grew very large as indicated by the “session pga memory max”.
REM v$sesstat pga memory over 20MB size break on spid skip 1 SELECT p.spid, s.sid, p.con_id, substr(n.name,1,25) memory, ROUND(s.value/1024/1024) as MBytes FROM v$sesstat s, v$statname n, v$process p, v$session vs WHERE s.statistic# = n.statistic# AND n.name LIKE '%pga memory%' AND s.sid=vs.sid AND vs.paddr=p.addr AND s.value > 20000000 /* --remove this line to view all process size */ order by spid,memory; break on off
OSpid Sess id CON_ID MEMORY Mbytes
-------- ------- ------ ------------------------- ------------
3727 246 0 session pga memory 20
246 0 session pga memory max 30
4356 22 4 session pga memory max 1,163
7303 257 1 session pga memory max 940
8367 237 6 session pga memory max 1,386
This query on V$PROCESS shows the current largest process and associated container id. The example results below show the largest process currently has 11MB allocated indicated in column PGA_ALLOC and at one time in its life reached 16MB as indicated in column PGA_MAX.
List largest process based on v$process:
/* Do NOT eliminate all background process because certain background processes do need to be monitored at times */
SELECT pid, spid, con_id, substr(username,1,13) username, program, ROUND(pga_used_mem/1024/1024) pga_used, ROUND(pga_alloc_mem/1024/1024) pga_alloc, ROUND(pga_freeable_mem/1024/1024) pga_freeable, ROUND(pga_max_mem/1024/1024) pga_max
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
FROM v$process
WHERE program NOT LIKE '%LGWR%');
Orapid OSpid CON_ID oracleuser Program PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX
------- -------- ------ ------------ ------------------------ --------------- --------------- --------------- ---------------
19 3724 0 oracle oracle@localhost.localdo 4 11 7 16
main (MMON)
This query based on V$PROCESS sums the current allocated pga for all processes associated with the instance or CDB. This value includes all background processes and foreground processes for both CDB and all PDBs. This value is a good indicator of how much private memory the CDB and all PDBs are using.
--Summation of ALL PGA based on v$process: REM allocated includes free PGA memory not yet released to the operating system by the server process SELECT ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used" FROM v$process;
Mbytes allocated Mbytes used
---------------- ---------------
83 58
This query based on V$PROCESS sums the current allocated pga and breaks down memory usage to the container level. This value includes all background processes and foreground processes for both CDB and all PDBs. PGA memory usage is indicated for each container id.
This example shows CDB id 0 and 1 using total of 76MB allocated, pluggable db id 4 using 3MB, and pluggable db id 6 using 3MB.
--Summation of each container PGA based on v$process:
REM allocated includes free PGA memory not yet released to the operating system by the server process
compute sum of "Mbytes allocated" on report
break on report
SELECT con_id, ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used"
FROM v$process
group by con_id
order by con_id;
break on off
CON_ID Mbytes allocated Mbytes used
------ ---------------- ---------------
0 72 51
1 4 2
4 3 2
6 3 2
----------------
sum 82
This query based on V$SESSTAT sums current allocated memory for all processes associated with the instance or CDB. This value includes all background processes and foreground processes for both CDB and all PDBs. This is another way of looking at total PGA usage as shown in above queries against V$PROCESS.
--Summation of ALL PGA memory based on V$SESSTAT:
SELECT ROUND(SUM(value)/1024/1024) AS Mbytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory';
Mbytes
------------
53
This query based on V$SESSTAT sums current allocated memory and breaks down memory usage to the container level. This value includes all background processes and foreground processes for both CDB and all PDBs. PGA memory usage is indicated for each container id. This example shows CDB id 0 and 1 using 51MB, pluggable db id 4 using 2M and pluggable id 6 using 2MB.
--Summation each container PGA memory based on V$SESSTAT:
compute sum of MBYTES on report
break on report
select con_id, ROUND(sum(bytes)/1024/1024) as MBYTES from (SELECT p.con_id, s.value as bytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name = 'session pga memory'
AND s.sid=vs.sid
AND vs.paddr=p.addr)
group by con_id
order by con_id;
break on off
CON_ID Mbytes
------ ------------
0 47
1 4
4 2
6 2
------------
sum 55
This query indicates the value of pga_aggregate_target as identified by “aggregate PGA target parameter”, and the current dynamically adjusted value of pga_aggregate_target identified by “aggregate PGA auto target”.
In example below, the pga_aggregate_target is set at 208MB and the current adjusted value of pga_aggregate_target is 136MB. The query will also indicate the maximum Total PGA usage of the instance at any time since instance startup. This can be used to identify how high the Total PGA usage reached for the instance at any time in the past since startup.
The example below shows the maximum PGA allocated reached 1,453MB at some point since instance startup.
--PGA stats from V$PGASTAT:
--show max total pga allocated since instance startup
select name, ROUND(value/1024/1024) as Mbytes from v$pgastat
where name in ('maximum PGA allocated','aggregate PGA target parameter','aggregate PGA auto target');
NAME Mbytes
---------------------- ------------
aggregate PGA target p 208
arameter
aggregate PGA auto tar 136
get
maximum PGA allocated 1,453
This query against CDB_HIST_PGASTAT will show the workload repository(AWR) historical maximum pga memory and corresponding snapshot id. This query could be used to identify when the PGA usage may have reached a high value. This snap_id can then be used to help identify the time frame for which an AWR report can be generated.
This example below shows that pga memory increased to 1,453MB as of snapshot 211.
--show max pga allocated from history select * from (select name,SNAP_ID, ROUND(VALUE/1024/1024) Mbytes from CDB_HIST_PGASTAT where name='maximum PGA allocated' order by Mbytes desc,snap_id desc) where rownum <11; NAME SNAP_ID Mbytes ---------------------- --------------- ------------ maximum PGA allocated 211 1,453 maximum PGA allocated 211 1,453 maximum PGA allocated 211 1,453 maximum PGA allocated 211 1,453 maximum PGA allocated 211 1,453 maximum PGA allocated 204 595 maximum PGA allocated 204 595 maximum PGA allocated 204 595 maximum PGA allocated 204 595 maximum PGA allocated 204 595 10 rows selected.
This query against V$SESSION and V$PROCESS provides a summary of information on all processes on the CDB and PDBs including OS and oracle ids, session status as defined in v$session, both oracle and OS login user name, and program information.
The results are grouped on the container id and a sum of total pga currently allocated is provided for each container. Results are sorted on current allocated memory with largest size listed at the bottom near the summary. To prevent listing of background processes, uncomment the AND clause "AND p.background is null".
The example results below show the largest size a pga has reached is 24MB; the MMON process. PDB 4 is using 3MB and PDB 6 is currently using 4MB.
break on con_id skip 4
compute sum of pga_alloc_mem on con_id
SELECT p.con_id,
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
ROUND(p.pga_alloc_mem/1024/1024) as pga_alloc_mem,
ROUND(p.pga_used_mem/1024/1024) as pga_used_mem,
ROUND(p.PGA_MAX_MEM/1024/1024) as pga_max_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
--AND p.background is null /* Remove prevent listing background processes */
ORDER BY con_id,pga_alloc_mem;
break on off
CON_ID OSpid Orapid Sess id Serial# Status PGA alloc PGA used PGA Max PNAME oracleuser OS user Program
------ -------- ------- ------- ------- -------- ------------ ------------ ------------ ----- ------------ ------------ ------------------------
0 1 0 0 0
3809 33 1 1 1 P002
3673 6 3 1 ACTIVE 1 1 1 MMAN oracle oracle@localhost.localdo
main (MMAN)
3681 8 4 1 ACTIVE 1 1 1 DIAG oracle oracle@localhost.localdo
main (DIAG)
3655 2 1 1 ACTIVE 1 1 1 PMON oracle oracle@localhost.localdo
main (PMON)
3663 4 2 1 ACTIVE 1 1 1 VKTM oracle oracle@localhost.localdo
main (VKTM)
3813 34 1 1 1 P003
3821 36 1 1 1 P005
3741 22 1 1 1 S000
3737 21 1 1 1 D000
3817 35 1 1 1 P004
3773 24 1 0 4 P000
3777 25 1 0 4 P001
3829 38 1 1 1 P007
3825 37 1 1 1 P006
9983 43 282 3795 ACTIVE 1 1 1 Q002 oracle oracle@localhost.localdo
main (Q002)
4132 75 268 7 ACTIVE 1 1 1 SMCO oracle oracle@localhost.localdo
main (SMCO)
3709 14 7 1 ACTIVE 1 1 1 LG00 oracle oracle@localhost.localdo
main (LG00)
3725 18 9 1 ACTIVE 1 1 1 LREG oracle oracle@localhost.localdo
main (LREG)
3733 20 10 1 ACTIVE 1 1 1 MMNL oracle oracle@localhost.localdo
main (MMNL)
3781 26 11 5 ACTIVE 1 1 1 TMON oracle oracle@localhost.localdo
main (TMON)
3797 30 14 1 ACTIVE 1 1 1 QM01 oracle oracle@localhost.localdo
main (QM01)
3659 3 238 1 ACTIVE 1 1 1 PSP0 oracle oracle@localhost.localdo
main (PSP0)
3669 5 239 1 ACTIVE 1 1 1 GEN0 oracle oracle@localhost.localdo
main (GEN0)
3685 9 240 3 ACTIVE 1 1 1 OFSD oracle oracle@localhost.localdo
main (OFSD)
3705 13 243 1 ACTIVE 1 1 1 CKPT oracle oracle@localhost.localdo
main (CKPT)
3713 15 244 1 ACTIVE 1 1 1 LG01 oracle oracle@localhost.localdo
main (LG01)
3785 27 249 5 ACTIVE 1 1 1 TT00 oracle oracle@localhost.localdo
main (TT00)
3793 29 251 1 ACTIVE 1 1 1 AQPC oracle oracle@localhost.localdo
main (AQPC)
3801 31 253 1 ACTIVE 1 1 1 Q001 oracle oracle@localhost.localdo
main (Q001)
10354 23 263 5027 ACTIVE 1 1 1 W000 oracle oracle@localhost.localdo
main (W000)
3717 16 8 1 ACTIVE 2 1 2 SMON oracle oracle@localhost.localdo
main (SMON)
3693 10 5 1 ACTIVE 2 2 2 DIA0 oracle oracle@localhost.localdo
main (DIA0)
3721 17 245 1 ACTIVE 2 1 2 RECO oracle oracle@localhost.localdo
main (RECO)
3689 7 241 1 ACTIVE 2 1 2 DBRM oracle oracle@localhost.localdo
main (DBRM)
3789 28 12 5 ACTIVE 4 3 4 FBDA oracle oracle@localhost.localdo
main (FBDA)
3873 32 17 9 ACTIVE 7 2 8 CJQ0 oracle oracle@localhost.localdo
main (CJQ0)
3697 11 242 1 ACTIVE 7 7 7 DBW0 oracle oracle@localhost.localdo
main (DBW0)
3701 12 6 1 ACTIVE 11 11 11 LGWR oracle oracle@localhost.localdo
main (LGWR)
3729 19 246 1 ACTIVE 24 14 24 MMON oracle oracle@localhost.localdo
main (MMON)
****** ------------
sum 91
1 4488 42 51 7 ACTIVE 3 3 4 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
****** ------------
sum 3
4 4510 40 19 9 INACTIVE 3 2 7 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
****** ------------
sum 3
6 4592 44 47 23 INACTIVE 4 2 13 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
****** ------------
sum 4
The following query provides parameter settings related to the SGA and PGA. If values for sga_target and pga_aggregate_target are zero, then these values are sized dynamically according to AMM when memory_target > 0.
--user defined parameters
select con_id, name as Parameter, value/1024/1024 as Mbytes from v$parameter
where name in ('pga_aggregate_target','memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_limit')
order by name;
CON_ID PARAMETER Mbytes
------ ------------------------------ ------------
1 memory_max_target 600
1 memory_target 600
1 pga_aggregate_limit 4,096
1 pga_aggregate_target 0
1 sga_max_size 600
1 sga_target 0