DBA Hub

📋Steps in this guide1/3

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

Oracle Resource Manager can limit the amount of PGA used by a session in Oracle Database 12c Release 2 (12.2).

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

SESSION_PGA_LIMIT Parameter

The parameter has been added to the and procedures of the 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.
2

Create a Plan to Limit Session PGA

The following example creates a new resource plan using the 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. Enable the plan by setting the parameter in the PDB. Assign the user to the consumer group.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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;
/

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pga_plan';

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>
3

Test It

The following code connects to the test user and artificially tries to allocate excessive amounts of PGA using recursion. Notice the process was terminated once the session tried to use more than 20 MB of PGA. Assign the user to the consumer group. Test it again. This time the session used 100 MB of PGA before the process was terminated. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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>

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;
/

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!