• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Monitor Process Memory Usage on Oracle Pluggable Databases

by admin

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

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. Warning: ORA-16829: fast-start failover configuration is lagging
  2. ORA-19554: error allocating device, device type: SBT_TAPE, device name:
  3. Operators in PL/SQL
  4. Error: ORA-16810: multiple errors or warnings detected for the database
  5. How To Find Creation Time of Oracle Pluggable Database (PDB)
  6. Oracle ASM – How ASM Disk Resync Works
  7. ORA-01506: missing or illegal database name
  8. How to Enable Fast-Start Failover using Enterprise Manager
  9. How to Verify if a Disk/Partition is in Use by Oracle ASM, was used by Oracle ASM or is never used by Oracle ASM
  10. Oracle Database – Measuring Network Capacity using oratcptest

You May Also Like

Primary Sidebar

Recent Posts

  • qemu-system-x86_64: command not found
  • timedatectl: command not found
  • mpirun.openmpi: command not found
  • startkde: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright