Skip to content

Restricting the PGA Usage Per Session Level Using Resource Manager

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2 BETA1 and later
Information in this document applies to any platform.

PURPOSE

This document explains the Per-Process PGA Limits introduced in 12cR2.

SCOPE

For DBAs who want to restrict the PGA usage per session. Knowledge of Resource Manager feature is required.

DETAILS

A runaway query using excessive amounts of PGA can affect other sessions or pluggable databases . Hence, from 12cR2, Program global area (PGA) can be restricted per session. This is implemented using Resource Manager.

Resource Manager can be employed to limit the amount of PGA memory that can be allocated to each session in a particular consumer group.

To limit the PGA resources for each session in a consumer group, set the session_pga_limit parameter in the package procedure DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE.

The value of this parameter is the maximum amount of PGA memory, in megabytes, allowed for each session in the consumer group. If a session exceeds the limit set for its consumer group, then error ORA-10260 is raised. This limit includes parallel query slaves and job queue processes also.

PGA usage of a whole instance with the PGA_AGGREGATE_LIMIT initialization parameter.

Example:

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN => ‘DAYTIME’,

GROUP_OR_SUBPLAN => ‘REPORTING’,

COMMENT => ‘Reporting group’,

MGMT_P1 => 15,

PARALLEL_DEGREE_LIMIT_P1 => 8,

ACTIVE_SESS_POOL_P1 => 4,

SESSION_PGA_LIMIT => 20);

Please note the session_pga_limit parameter can be set in the  only in plan directives that refer to resource consumer groups, not other resource plans.

An example to create a resource plan to set per process pga limit.

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => ‘erp_plan’,

COMMENT => ‘Resource plan/method for ERP Database’);

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘oltp’,

COMMENT => ‘Resource consumer group/method for OLTP jobs’);

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘batch’,

COMMENT => ‘Resource consumer group/method for BATCH jobs’);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘erp_plan’,

GROUP_OR_SUBPLAN => ‘oltp‘, COMMENT => ‘OLTP sessions’, MGMT_P1 => 60,

SWITCH_GROUP => ‘batch’, SWITCH_TIME => 3, UNDO_POOL => 200,

SWITCH_FOR_CALL => TRUE, SESSION_PGA_LIMIT => 20);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘erp_plan’,

GROUP_OR_SUBPLAN => ‘batch‘, COMMENT => ‘BATCH sessions’, MGMT_P1 => 30,

PARALLEL_SERVER_LIMIT => 8, PARALLEL_QUEUE_TIMEOUT => 600,

MAX_EST_EXEC_TIME => 3600);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘erp_plan’,

GROUP_OR_SUBPLAN => ‘OTHER_GROUPS‘, COMMENT => ‘mandatory’, MGMT_P1 => 10);

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

/

In the above example, the sessions mapped to the consumer group OLTP has the SESSION_PGA_LIMIT set to 20MB.
The PGA limit set for the various consumer groups can be checked from the dictionary view DBA_RSRC_PLAN_DIRECTIVES.

select PLAN,SESSION_PGA_LIMIT from DBA_RSRC_PLAN_DIRECTIVES;

The V$RSRC_CONS_GROUP_HISTORY and DBA_HIST_RSRC_CONSUMER_GROUP lists the number of times the sessions that were killed because their PGA allocation exceeded the PGA limit specified in the Resource Plan’s SESSION_PGA_LIMIT directive.

select SNAP_ID,CONSUMER_GROUP_NAME,PGA_LIMIT_SESSIONS_KILLED from DBA_HIST_RSRC_CONSUMER_GROUP;