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