Forum Stats

  • 3,872,005 Users
  • 2,266,363 Discussions
  • 7,911,025 Comments

Discussions

Multitenant - Allow to limit memory consumption for specific PDBs

Pini Dibask
Pini Dibask Member Posts: 521 Gold Badge
edited Apr 3, 2017 12:41PM in Database Ideas - Ideas

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

Pini DibaskSven W.
2
2 votes

Delivered · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    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
    Pini Dibask Member Posts: 521 Gold Badge

    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.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Aug 22, 2016 12:51PM

    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
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    This functionality will be coming with 12.2.

    Sven W.berx
  • Pini Dibask
    Pini Dibask Member Posts: 521 Gold Badge

    This functionality will be coming with 12.2.

    Hi Gerlad,

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

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    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.

    Pini Dibask