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.
On RAC configuration, the scripts should be run on each instance since only V$ views are used to provide an accurate memory usage for individual database instance.
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.
To make the query output more presentable and easy understanding, use the following SQL* plus functions:
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 FORMAT a8
COLUMN pid FORMAT 999999
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 999999
COLUMN 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 FORMAT a12
COLUMN program FORMAT a24
COLUMN 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.
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_BB READ WRITE NO
4 PDB1 READ WRITE NO
5 PDB_COPY MOUNTED
6 PDB2 READ WRITE NO
This query provides the name of the root container database CDB.
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 FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID OPEN_MODE RES DBID CON_UID
———————- —— ———- — ————— —————
CDB$ROOT 1 READ WRITE NO 873329198 1
PDB$SEED 2 READ ONLY NO 5142245629 5142245629
PDB_SS 3 READ WRITE NO 2667312971 2667312971
PDB1 4 READ WRITE NO 4307280986 4307280986
PDB_COPY 5 MOUNTED 2778550228 2778550228
PDB2 6 READ WRITE NO 4969863818 4969863818
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 800
1 memory_target 800
1 pga_aggregate_limit 4,096
1 pga_aggregate_target 0
1 sga_max_size 800
1 sga_target 0
This 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;
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;
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’);
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;
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