Database Software

Ressource manager profiles useful for all kind of managing ressources.

I think this makes sense for PGA settings, but not for SGA settings.

Inside one database / PDB we should be able to limit memory usage for certain consumer groups.

Since the SGA is all shared memory it seems not possible or sensible to limit that one. but the private workareas could need some resttrictions.

To manage SGA settings for a PDB I think the proper way would be to use the normal initialization parameters (SGA_target, ...).

And as far as I understood, it already works this way. You can set the SGA_TARGET for a PDB using something like

alter session set container=pdb1;

alter system set SGA_TARGET=... scope=spfile;

V$SYSTEM_PARAMETER should be able to show the differences between the containers.

Ressource manager profiles useful for all kind of managing ressources.

I think this makes sense for PGA settings, but not for SGA settings.

Inside one database / PDB we should be able to limit memory usage for certain consumer groups.

Since the SGA is all shared memory it seems not possible or sensible to limit that one. but the private workareas could need some resttrictions.

To manage SGA settings for a PDB I think the proper way would be to use the normal initialization parameters (SGA_target, ...).

And as far as I understood, it already works this way. You can set the SGA_TARGET for a PDB using something like

alter session set container=pdb1;

alter system set SGA_TARGET=... scope=spfile;

V$SYSTEM_PARAMETER should be able to show the differences between the containers.

Hi @"Sven W."

Thanks for you comment.

What you suggested is not possible - you cannot set the SGA_TARGET for a specific container. You can only set it at the instance level.

You can confirm it by looking at the ISPDB_MODIFIABLE column in V$SYSTEM_PARAMETER.

Hi @"Sven W."

Thanks for you comment.

What you suggested is not possible - you cannot set the SGA_TARGET for a specific container. You can only set it at the instance level.

You can confirm it by looking at the ISPDB_MODIFIABLE column in V$SYSTEM_PARAMETER.

You are correct. In that case I think the "idea" here should be to make this parameter (and a few others) to be ISPDB_MODIFIABLE=TRUE.

And for PGA consumption I still think a ressource profile would be fine. Just not for SGA consumption (you can not really separate which session did load a certain datablock into memory).

select name, ispdb_modifiable, description 
from V$SYSTEM_PARAMETER 
where regexp_like(name,'(sga|pga|memory)');

NAME                        ISPDB_MODIFIABLE    DESCRIPTION

sga_max_size         FALSE    max total SGA size

pre_page_sga         FALSE    pre-page sga for process

shared_memory_address    FALSE    SGA starting address (low order 32-bits on 64-bit platforms)

hi_shared_memory_address    FALSE    SGA starting address (high order 32-bits on 64-bit platforms)

lock_sga                   FALSE    Lock entire SGA in physical memory

pga_aggregate_limit    FALSE    limit of aggregate PGA memory consumed by the instance

sga_target              FALSE    Target size of SGA

memory_target         FALSE    Target size of Oracle SGA and PGA memory

memory_max_target    FALSE    Max size for Memory Target

unified_audit_sga_queue_size    FALSE    Size of Unified audit SGA Queue

pga_aggregate_target    FALSE    Target size for the aggregate PGA memory consumed by the instance

This functionality will be coming with 12.2.

This functionality will be coming with 12.2.

Hi Gerlad,

Is there an official note that you can share about this?

Hi Gerlad,

Is there an official note that you can share about this?

Hi Pini,

Not yet as the version including this is not yet GA.