Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Multitenant - Allow to limit memory consumption for specific PDBs

Pini DibaskAug 21 2016 — edited Apr 3 2017

In Oracle 12c, Oracle Resource Manager has been enhanced to support Multitenant by allowing to limit and prioritize CPU resources among competing PDBs.

Currently, it is not possible to prioritize memory resources, only CPU resources can be defined using Oracle Resource Manager.

My idea is to enhance Oracle Resource Manager to also limit and prioritize memory resources among competing PDBs.

Update: PDB-level memory controls are now provided with Oracle Database 12c Release 2

PDB-Level Memory Controls

In a CDB, PDBs may contend for SGA or PGA memory. Several initialization parameters can control the memory usage of a PDB, either guaranteeing memory or limiting memory. When you set the following initialization parameters with the PDB as the current container, the parameters control the memory usage of the current PDB.

Examples of important parameters include:

  • SGA_MIN_SIZE sets the minimum guaranteed SGA size of the PDB.
  • SGA_TARGET specifies the maximum SGA that the PDB can use at any time.
  • PGA_AGGREGATE_LIMIT sets the maximum PGA that the PDB can use at any time.

For more information please refer to: Overview of Oracle Resource Manager in a CDB

Comments

Sven W.

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.

Pini Dibask

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.

Sven W.

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

Gerald Venzl-Oracle

This functionality will be coming with 12.2.

2 people found this helpful
Pini Dibask

This functionality will be coming with 12.2.

Hi Gerlad,

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

Gerald Venzl-Oracle

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.

1 person found this helpful
1 - 6

Post Details

Added on Aug 21 2016
#database-ideas, #database-ideas-ideas, #delivered
6 comments
2,053 views