in Oracle

Parallel Execution — 3b Limiting PX Servers with Resource Manager

As demonstrated earlier, in the absence of CALIBRATE_IO, the “automatic” degree computed by a PARALLEL Hint is CPU_COUNT x PARALLEL_THREADS_PER_CPU

HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Now, I shall explore using the Resource Manager to place a limit.

HEMANT>connect system/oracle
Connected.
SYSTEM>BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END; 2 3
4 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
3 CONSUMER_GROUP=>'PX_QUERY_USER',
4 COMMENT=>'New Group for PX');
5 END;
6 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN(
3 PLAN=>'LIMIT_PX_TO_4',
4 COMMENT=>'Limit PQ/PX to 4 Server Processes');
5 END;
6 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 PLAN=>'LIMIT_PX_TO_4',
4 GROUP_OR_SUBPLAN=>'PX_QUERY_USER',
5 PARALLEL_DEGREE_LIMIT_P1=>4,
6 COMMENT=>'Directive to limit PQ/PX to 4 Server Processes');
7 END;
8 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan LIMIT_PX_TO_4
ORA-06512: at "SYS.DBMS_RMIN", line 444
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 809
ORA-06512: at line 2


SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
3 PLAN=>'LIMIT_PX_TO_4',
4 GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
5 MGMT_P1=>10,
6 COMMENT=>'Directive for OTHER_GROUPS (mandatory)');
7 END;
8 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>alter system set resource_manager_plan='LIMIT_PX_TO_4';

System altered.

SYSTEM>alter system flush shared_pool;

System altered.

SYSTEM>

I have now created 1 single consumer group and a plan.
Note 1 : The COMMENT is mandatory for the Consumer Group, the Plan and the Directives.
Note 2 : It is mandatory to specify Directives for OTHER_GROUPS in the Plan (even if I don’t explicitly define any other groups).
For the Group ‘PX_QUERY_USER’, I’ve set a PX Limit of 4 (and no CPU limit). For the ‘OTHER_GROUPS’, I’ve set a CPU Limit of 10%
I must now associate the Consumer Group with the User.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
3 ATTRIBUTE=>DBMS_RESOURCE_MANAGER.ORACLE_USER,
4 VALUE=>'HEMANT',
5 CONSUMER_GROUP=>'PX_QUERY_USER');
6 END;
7 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>alter system flush shared_pool;

System altered.

SYSTEM>
SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
3 GRANTEE_NAME=>'HEMANT',
4 CONSUMER_GROUP=>'PX_QUERY_USER',
5 GRANT_OPTION=>FALSE);
6 END;
7 /

PL/SQL procedure successfully completed.

SYSTEM>
SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>BEGIN
2 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
3 END;
4 /

PL/SQL procedure successfully completed.

SYSTEM>

Let me test the configuration now.

SYSTEM>connect hemant/hemant
Connected.
HEMANT>select username, resource_consumer_group
2 from v$session
3 where username='HEMANT'
4 /

USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
HEMANT PX_QUERY_USER

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 4 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Now, the same query executed with only 4 PX servers.

.
.
.