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

The Geek Diary

CONCEPTS | BASICS | HOWTO

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

How to Monitor SGA Memory on Oracle Pluggable Databases

By admin

This post describes how to monitor SGA memory usage on pluggable databases using queries against database views. Pre-requisites to using the sample code provided below are:

  • On RAC configuration, the scripts should be run on each instance since only V$ views are used to provide an accurate memory usage for the 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. 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 SGA memory usage 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 SGA views.

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

Below query provides the 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

The following 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;

Output

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

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;

Output

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

The below query provides the current size of dynamic SGA components as well as other basic information related to sizing of each component. The query results below show all sga components are associated with the root container id 0. The current value of sga_target is 392M.

--show current size of dynamic components
select con_id, component, ROUND(USER_SPECIFIED_SIZE/1024/1024) as Mbytes, OPER_COUNT, LAST_OPER_TIME as "Last op time", ROUND(current_size/1024/1024) as Mbytes from v$memory_dynamic_components
order by component;

Output

CON_ID COMPONENT                          Mbytes      OPER_COUNT Last op t       Mbytes
------ ---------------------------- ------------ --------------- --------- ------------
     0 ASM Buffer Cache                        0               0                      0
     0 DEFAULT 16K buffer cache                0               0                      0
     0 DEFAULT 2K buffer cache                 0               0                      0
     0 DEFAULT 32K buffer cache                0               0                      0
     0 DEFAULT 4K buffer cache                 0               0                      0
     0 DEFAULT 8K buffer cache                 0               0                      0
     0 DEFAULT buffer cache                    0               4 31-DEC-12           68
     0 Data Transfer Cache                     0               0                      0
     0 KEEP buffer cache                       0               0                      0
     0 PGA Target                              0               0                    208
     0 RECYCLE buffer cache                    0               0                      0
     0 SGA Target                              0               0                    392
     0 Shared IO Pool                         20               0                     20
     0 java pool                               0               0                      4
     0 large pool                              0               3 31-DEC-12           12
     0 shared pool                             0               1 31-DEC-12          276
     0 streams pool                            0               0                      0

The following query against V$SGAINFO provides sga component sizes, the granule size, and free sga memory. The example below shows free sga memory of 208M. This is memory above the current sga_target size of 392M as shown in above query results. This is memory that can be allocated to the sga for increase of the sga_target.

--Determine SGA Memory usage from database views
select name, ROUND(bytes/1024/1024) as Mbytes from v$sgainfo;

Output

NAME                            Mbytes
------------------------- ------------
Fixed SGA Size                       2
Redo Buffers                         7
Buffer Cache Size                   88
Shared Pool Size                   276
Large Pool Size                     12
Java Pool Size                       4
Streams Pool Size                    0
Shared IO Pool Size                 20
Data Transfer Cache Size             0
Granule Size                         4
Maximum SGA Size                   597
Startup overhead in Share          107
d Pool
Free SGA Memory Available          208

The following query against V$SGA provides basic sga size information. The variable size indicated includes various sga components and free sga memory.

select name, ROUND(value/1024/1024) as Mbytes from v$sga;
NAME                            Mbytes
------------------------- ------------
Fixed Size                           2
Variable Size                      500
Database Buffers                    88
Redo Buffers                         7

The following query from V$SGA_DYNAMIC_FREE_MEMORY shows available free memory that can be allocated to the sga for increase of the sga_target. This should correspond closely with the value shown in V$SGAINFO.

-shows available free sga memory for growth above sga_target
select ROUND(current_size/1024/1024) Mbytes from V$SGA_DYNAMIC_FREE_MEMORY;
      Mbytes
------------
         208

The next two queries below provide sum of sga components and free sga memory. These queries basically determine the worst case sga memory usage scenario, since this summation includes any free sga memory above sga_target. The amount of free can be determined from prior query.

These results will show the maximum size the sga could ever be and can be used for planning purposes for determining how much ram would be required to handle sga requirements. The pga memory usage then needs to be considered to determine total memory usage.

Depending on how the OS allocates memory, not all this memory may actually be resident in physical ram. Portions of the memory could possibly be in a virtual state on disk. You would need to use appropriate OS utilities to determine actual physical memory usage of sga. However, it is good practice to assume majority of this sga memory will be in physical memory due to the nature of the database and block access.

If lock_sga is set true, then all the sga memory is in physical memory. When memory_target is set, lock_sga cannot be used.

--these two queries assume scenario of max sga size allocated in shared memory including the free sga memory
--depending on the configuration and OS, the actual pinned/resident shared memory can only be determined using OS commands
--If lock_sga is used you know this sga is resident in memory
select ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgainfo
where name not in ('Maximum SGA Size','Startup overhead in Shared Pool','Granule Size');

Output

      Mbytes
------------
         617
select ROUND(sum(value)/1024/1024) as Mbytes from v$sga;
      Mbytes
------------
         597

The difference in the sum of above two queries is due to the Shared IO Pool Size currently listed in v$sgainfo and not currently accounted for in v$sga.

The following two queries do not include the free sga memory. These queries can be used to determine the actual sga memory currently allocated:

--when memory_target is used(lock_sga cannot be used), depending on db configuration and OS, the shared memory may be shrunk to satisfy pga requirements
--these queries show the sum of actual sga allocated which does not include free sga memory above sga_target
--still possible not all this sga memory is resident, some in virtual
select ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgastat;

Output

      Mbytes
------------
         389
select ROUND(sum(bytes)/1024/1024,1) as Mbytes from v$sgainfo
where name not in ('Maximum SGA Size','Startup overhead in Shared Pool','Granule Size','Free SGA Memory Available');
      Mbytes
------------
         409

Again, there is a difference in the sum of above two queries due to Shared IO Pool Size not currently accounted for in v$sga.

The following query breaks down sga memory usage at the container level. The query results below show container id 4 using 19M and container id 6 using 11M. The majority of sga memory usage is used by the root container as indicated by container id 0 and 1.

select con_id, ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgastat
group by con_id
order by con_id;
CON_ID       Mbytes
------ ------------
     0          136
     1          213
     2            3
     3            7
     4           19
     5            0
     6           11

The following query breaks down sga memory usage at container level and further to individual sga components.

break on con_id skip 4
compute sum of Mbytes on con_id
select con_id, pool, name, ROUND(bytes/1024/1024,2) as Mbytes from v$sgastat
order by con_id,Mbytes;

break on off
CON_ID POOL         NAME                            Mbytes
------ ------------ ------------------------- ------------
     0 large pool   session heap                         0
                    fixed_sga                            2
       java pool    free memory                          4
       large pool   free memory                          6
                    log_buffer                           7
                    shared_io_pool                      20
       shared pool  free memory                         29
                    buffer_cache                        68
******                                        ------------
sum                                                    136

     1 shared pool  KCFIS SGA                            0
       shared pool  KTC txn rsrc cnt                     0
       shared pool  kdlxdup swapp                        0
....
       large pool   session heap                         5
       shared pool  event statistics per sess            7
       shared pool  private strands                      7
       shared pool  row cache                            7
       shared pool  PLMCD                               10
       shared pool  XDBSC                               14
       shared pool  KGLH0                               17
       shared pool  SQLA                                23
******                                        ------------
sum                                                    212

     2 shared pool  KQR M PO                             0
       shared pool  monitoring column usage e            0
       shared pool  keomg: entry list                    0
...
       shared pool  work area table entry                0
       shared pool  KGLS                                 0
       shared pool  KGLH0                                0
       shared pool  PDB Heap                             1
******                                        ------------
sum                                                      3

     3 shared pool  KQR S SO                             0
       shared pool  kpscad: kpscscon                     0
       shared pool  object queue header free             0
...
       shared pool  KQR L PO                             0
       shared pool  PDB Heap                             1
       shared pool  SQLA                                 2
       shared pool  KGLH0                                2
******                                        ------------
sum                                                      7

     4 shared pool  ktli log buffer pools                0
       shared pool  ktli log open descs                  0
       shared pool  KGLNA                                0
...
       shared pool  KGLS                                 1
       shared pool  PDB Heap                             1
       shared pool  SQLA                                 2
       shared pool  KGLH0                                3
       shared pool  XDBSC                               10
******                                        ------------
sum                                                     19

     5 shared pool  PDB Heap                             0
******                                        ------------
sum                                                      0

     6 shared pool  ktli log bkts                        0
       shared pool  kwqmncgr: allocate buffer            0
       shared pool  kkcnRstatIni                         0
...
       shared pool  KQR L PO                             1
       shared pool  KGLS                                 1
       shared pool  KQR X SO                             1
       shared pool  PDB Heap                             1
       shared pool  ktli log buf s                       2
       shared pool  SQLA                                 2
       shared pool  KGLH0                                3
******                                        ------------
sum                                                     11

The following query is same as above, but includes a where clause to limit the results to only larger memory allocations over 10M. This can be altered for even larger allocation sizes and can be used to show only the very large memory allocations in the containers. If you are getting sga memory errors such as ORA-4031, then this query may indicate one container using majority of resources and could be a candidate to be moved out of the root container into its own non-container database.

--list only large memory allocations over 10M
break on con_id skip 4
select con_id, pool, name, ROUND(bytes/1024/1024,1) as Mbytes from v$sgastat
where bytes/1024/1024 > 10 --alter to see smaller/larger allocations
order by con_id;
break on off
CON_ID POOL         NAME                            Mbytes
------ ------------ ------------------------- ------------
     0 shared pool  free memory                         29
                    shared_io_pool                      20
                    buffer_cache                        68
******                                        ------------
sum                                                    117

     1 shared pool  SQLA                                23
       shared pool  XDBSC                               14
       shared pool  KGLH0                               17
******                                        ------------
sum                                                     54

The following query provides summation of sga and pga, and gives a value of total memory usage by the oracle instance. This query should always be used when sga is locked in memory and can be used for determining memory usage with largest possible sga allocated. The sum on v$sga assumes scenario of max sga size allocated in shared memory by including the free sga memory.

SELECT ROUND(SUM(bytes)/1024/1024) AS Mbytes
FROM (SELECT value AS bytes
      FROM v$sga
      UNION ALL
      SELECT value AS bytes
      FROM v$sesstat s, v$statname n
      WHERE n.STATISTIC# = s.STATISTIC#
      AND n.name = 'session pga memory');
      Mbytes
------------
         663

The following query should be used when “memory_target > 0”. Sum on v$sgastat does not include the free sga memory available. Depending on the configuration and OS, the actual pinned/resident shared memory can only be determined using OS commands. Use above query if you want to calculate full sga size including free sga available.

SELECT round(sum(Mbytes)) as Mbytes from (select sum(bytes)/1024/1024 as Mbytes from v$sgastat
      UNION ALL
      SELECT 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
------------
         465

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. Oracle Database: How To Use PROFILES To Limit User Resources
  2. How To Create Device Alias For ASM Disks Using mknod On Linux/Unix
  3. Dynamic Oracle Net Server Tracing
  4. Understanding Flashback Table Feature in Oracle Database
  5. How to Split a Partition Into Multiple Partitions in Oracle 12c
  6. How To Add New Disk to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
  7. What Happens and What to Do when the SPFILE has been Manually Modified
  8. How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
  9. Step By Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN
  10. Oracle Database : Performing Incomplete Recovery from a missing archivelog file (Change-Based, Cancel-Based, Time-Based)

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary