When it comes to SGA usage the following query breaks down sga memory usage at the container level:
select con_id, ROUND(sum(bytes)/1024/1024) as Mbytes from v$sgastat group by con_id order by con_id;
Please keep in mind that con_id 0 and 1 are both for the root container.

According to document: http://docs.oracle.com/database/122/REFRN/INMEMORY_SIZE.htm#REFRN10348
“Unless this parameter is specifically set on a PDB, each PDB inherits the CDB value, which means they can use all of the available IM column store.
Typically this parameter should be set to at least the size needed to accommodate all the tables that will use the IM column store. It can be set higher to allow for growth of those tables or to accommodate other tables that will use the IM column store in the future.

How to Monitor SGA Memory on Pluggable Database ( Doc ID 1516229.1 )

How to Monitor SGA Memory on Pluggable Database ( Doc ID 1516229.1 )
Oracle Multitenant Option – 12c : Frequently Asked Questions ( Doc ID 1511619.1 )
http://docs.oracle.com/database/122/ADMIN/using-oracle-resource-manager-for-pdbs-with-sql-plus.htm#ADMIN-GUID-A3459A8B-A36A-44D4-9FCD-75CA0E3D3252
How to Control and Monitor the Memory Usage (Both SGA and PGA) Among the PDBs in Mutitenant Database- 12.2 New Feature ( Doc ID 2170772.1 )
http://docs.oracle.com/database/122/REFRN/INMEMORY_SIZE.htm#REFRN10348

Queries

set linesize 150
set pagesize 3000
set NUMWIDTH 15col 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;
show con_name
show con_id
show pdbs

SYSDATE
——————–
15-SEP-2017 09:52:41

show con_name

CON_NAME
——————————
CDB$ROOT
show con_id

CON_ID
——————————
1

CON_ID CON_NAME OPEN MODE RESTRICTED
————— —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 CCCCCC01 READ WRITE NO

 

This query provides the name of the root container database CDB. In this example, named CCC.

select name, cdb, con_id from v$database;

NAME CDB CON_ID
———————- — ——
CCC 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 4037079260 1 4700A987085A3DFAE05387E5E50A8C7B
PDB$SEED 2 READ ONLY NO 1445689088 1445689088 5171355F27DD6ED9E0538C0D630A69B2
CCCCCC01  3 READ WRITE NO 144389189 144389189 517145ED66507C7BE0538C0D630ACFF0

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 0
1 memory_target 0
1 pga_aggregate_limit 24,576
1 pga_aggregate_target 10,240
1 sga_max_size 88,064
1 sga_target 88,064

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 88,064M.

–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 time 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 30 15-SEP-2017 07:47:45 8,192
0 Data Transfer Cache 0 0 0
0 In Memory RO Extension Area 0 0 0
0 In Memory RW Extension Area 0 0 0
0 In-Memory Area 71,680 0 71,680
0 KEEP buffer cache 0 0 0
0 PGA Target 10,240 2 14-SEP-2017 13:56:59 10,240
0 RECYCLE buffer cache 0 0 0
0 SGA Target 88,064 0 88,064
0 Shared IO Pool 512 0 512
0 java pool 0 3 12-SEP-2017 22:05:05 256
0 large pool 0 0 768
0 shared pool 0 8 14-SEP-2017 22:26:01 3,840
0 streams pool 0 19 15-SEP-2017 07:47:45 2,560

20 rows selected.

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 0M. This is memory above the current sga_target size of 88,064M 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 19
Redo Buffers 237
Buffer Cache Size 8,704
In-Memory Area Size 71,680
Shared Pool Size 3,840
Large Pool Size 768
Java Pool Size 256
Streams Pool Size 2,560
Shared IO Pool Size 512
Data Transfer Cache Si 0
Granule Size 256
Maximum SGA Size 88,064
Startup overhead in Sh 354
Free SGA Memory Availa 0

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 19
Variable Size 7,424
Database Buffers 8,704
Redo Buffers 237
In-Memory Area 71,680

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
————
0

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
————
88,576
select ROUND(sum(value)/1024/1024) as Mbytes from v$sga;
      Mbytes
————
88,064

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
————
88,320
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
————
88,576

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 3 using 1,844M. 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 84,971
1 1,504
2 1
3 1,844

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

N.B. in this example I use > 100 because the SGA is so large.

break on con_id skip 4
COLUMN NAME FORMAT a48
compute sum of Mbytes on con_id
select con_id, pool, name, ROUND(bytes/1024/1024,2) as Mbytes from v$sgastat where ROUND(bytes/1024/1024.2) > 100 order by con_id,Mbytes;

CON_ID POOL NAME Mbytes
—— ————– ———————————————— ————
0 log_buffer 237
java pool free memory 256
shared_io_pool 512
large pool free memory 524
shared pool free memory 986
streams pool free memory 2,546
buffer_cache 8,192
in-memory pool metadata area 14,336
in-memory pool data area 57,344
****** ————
sum 84,933

1 large pool PX msg pool 204
shared pool SQLA 219
shared pool KGH: NO ACCESS 251
****** ————
sum 674

3 shared pool getl2emc-mem 210
shared pool SQLP 330
shared pool SQLA 336
shared pool KGLH0 659
****** ————
sum 1,535

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.

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
————
89,920

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
————
89,881