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.

.
.
.