8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Resource Manager : Per-Process PGA Limits in Oracle Database 12c Release 2 (12.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 (described here). 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.
Related articles.
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>
This time the session used 100 MB of PGA before the process was terminated.
For more information see:
- Resource Manager : Program Global Area (PGA)
- DBMS_RESOURCE_MANAGER
- Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles
Hope this helps. Regards Tim...