Tuesday, 6 June 2017

Resource Manager : Per-Process PGA Limits in Oracle Database 12c Release 2

Oracle has a long history of improving the management of the Process Global Area (PGA). Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to automate the management of the PGA and set a soft limit for its size. Oracle 11g introduced Automatic Memory Management (AMM), which you should probably avoid. Oracle 12c Release 1 introduced the PGA_AGGREGATE_LIMIT parameter to define a hard limit for PGA size.
Oracle Database 12c Release 2 (12.2) has introduced two new features related to management of the PGA. First, the PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT parameters can now be set at the PDB level to limit the amount of PGA used by the PDB. Second, Resource Manager can limit the amount of PGA used by a session, based on the session's consumer group. This article focusses on this second feature.

  • SESSION_PGA_LIMIT Parameter


The SESSION_PGA_LIMIT parameter has been added to the CREATE_PLAN_DIRECTIVE and UPDATE_PLAN_DIRECTIVE procedures of the DBMS_RESOURCE_MANAGER package. This new parameter specifies the upper limit in MB for PGA usage by a session assigned to the consumer group. If a session exceeds this limit, an ORA-10260 error is raised.

This parameter can be used in conjunction with other resource limits for a plan directive, but in this article it will be discussed in isolation. It can be used in non-CDB architecture also, but here it will only be considered inside a PDB.

  • Create a Plan to Limit Session PGA


The following example creates a new resource plan using the SESSION_PGA_LIMIT parameter. The plan includes two main consumer groups, one allowing high PGA usage and one limited to low PGA usage. It also includes a consumer group for maintenance tasks and a catch all group.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area();
  DBMS_RESOURCE_MANAGER.create_pending_area();

  -- Create plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'pga_plan',
    comment => 'Plan for a combination of high and low PGA usage.');

  -- Create consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'high_pga_cg',
    comment        => 'High PGA usage allowed');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'low_pga_cg',
    comment        => 'Low PGA usage allowed');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'maint_subplan',
    comment        => 'Low PGA usage allowed');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan              => 'pga_plan',
    group_or_subplan  => 'high_pga_cg',
    session_pga_limit => 100);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan              => 'pga_plan',
    group_or_subplan  => 'low_pga_cg',
    session_pga_limit => 20);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan              => 'pga_plan',
    group_or_subplan  => 'maint_subplan',
    session_pga_limit => NULL);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan              => 'pga_plan',
    group_or_subplan  => 'OTHER_GROUPS',
    session_pga_limit => NULL);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

Enable the plan by setting the RESOURCE_MANAGER_PLAN parameter in the PDB.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = pga_plan;

Assign the TEST user to the LOW_PGA_CG consumer group.

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area();
  DBMS_RESOURCE_MANAGER.create_pending_area();

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
    attribute      => DBMS_RESOURCE_MANAGER.oracle_user,
    value          => 'test',
    consumer_group => 'low_pga_cg');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

COLUMN username FORMAT A30
COLUMN initial_rsrc_consumer_group FORMAT A30

SELECT username, initial_rsrc_consumer_group
FROM   dba_users
WHERE  username = 'TEST';

USERNAME     INITIAL_RSRC_CONSUMER_GROUP
-------------    ------------------------------
TEST                    LOW_PGA_CG

1 row selected.

SQL>

  • Test It


The following code connects to the test user and artificially tries to allocate excessive amounts of PGA using recursion.

CONN test/test@pdb1

DECLARE
  PROCEDURE grab_memory AS
    l_dummy VARCHAR2(4000);
  BEGIN
    grab_memory;
  END;
BEGIN
  grab_memory;
END;
/
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-10260: PGA limit (20 MB) exceeded - process terminated

SQL>

Notice the process was terminated once the session tried to use more than 20 MB of PGA. Assign the TEST user to the HIGH_PGA_CG consumer group.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area();
  DBMS_RESOURCE_MANAGER.create_pending_area();

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
    attribute      => DBMS_RESOURCE_MANAGER.oracle_user,
    value          => 'test',
    consumer_group => 'high_pga_cg');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

Test it again.

CONN test/test@pdb1

DECLARE
  PROCEDURE grab_memory AS
    l_dummy VARCHAR2(4000);
  BEGIN
    grab_memory;
  END;
BEGIN
  grab_memory;
END;
/
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-10260: PGA limit (100 MB) exceeded - process terminated

SQL>