Search My Oracle Blog

Custom Search

20 April, 2015

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.

.
.
.


No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016