并行 parallel DOP 受 Resource Manager 限制

本文主要是介绍并行 parallel DOP 受 Resource Manager 限制，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

监控

Script

select s.SID, s.SERIAL#, s.username ,rpd.plan,        s.RESOURCE_CONSUMER_GROUP,        rpd.PARALLEL_DEGREE_LIMIT_P1  from   v$session s,         DBA_RSRC_CONSUMER_GROUPS rcg,        DBA_RSRC_PLAN_DIRECTIVES rpd ,        V$RSRC_CONSUMER_GROUP vcg where  s.RESOURCE_CONSUMER_GROUP is not null    and rcg.CONSUMER_GROUP = s.RESOURCE_CONSUMER_GROUP   -- and rcg.status = 'ACTIVE'    and rpd.GROUP_OR_SUBPLAN = rcg.CONSUMER_GROUP    --and rpd.status = 'ACTIVE'    and vcg.name = s.RESOURCE_CONSUMER_GROUP;

APPLIES TO:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

PURPOSE

The database may not have sufficient resources to run all queries at the same time at their ideal degree-of-parallelism.  When this happens, parallel queries are downgraded or waiting in the parallel statement queue depending on the configuration.

There are several methods to limit the maximum DOP. If Database Resource Manager (DBRM) is enabled, then it is the ultimate deciding factor
in determining the maximum degree of parallelism (DOP).

DETAILS

A user in a consumer group using a specific resource plan will not be able to run a statement with higher DOP than the maximum DOP specified in the resource plan.

For example, if maximum DOP of 4 is set in the resource plan, and you request a DOP of 16 via a hint, your SQL will run with a DOP of 4 at most, that is, 4 is a maximum upper limit on the degree of parallelism.

The image below shows an Enterprise Manager Database Control screenshot below restricting parallel execution to a DOP of 4 for resource plan named 'REPORTS_PLAN' for consumer group OTHER_GROUPS. The value is specified in the Max Degree of Parallelism column.

The screenshot is the manifestation of the example provided in the Oracle documentation.

Alternatively to the EM GUI, you can use SQLPlus to create a resource plan that limits the maximum degree-of-parallelism for users in a particular consumer group, as the example shows below. The dop should not exceed the value given by the maximum DOP directive.
 

-- create  MY_PLAN resource plan
-- setting maximum degree-of-parallelism for users in a consumer group other_groups
-- other_groups is a pre-defined default consumer group for all sessions that do not have an explicit initial consumer group
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.create_plan('MY_PLAN', 'MY_PLAN');
exec dbms_resource_manager.create_plan_directive('MY_PLAN', 'other_groups', 'other_groups',  parallel_degree_limit_p1 => 4, parallel_target_percentage => 50, parallel_queue_timeout => 14400);
exec dbms_resource_manager.submit_pending_area();
-- enable resource plan
alter system set resource_manager_plan = 'MY_PLAN';

This note is primarily written for single instance database. In case of multiple instances you may want to specify which instances the plan should be turned on:

e.g.

alter system set resource_manager_plan = 'MY_PLAN' sid='*' ;

CREATE_PLAN_DIRECTIVE procedure is to create resource plan directives. Parameter parallel_degree_limit_p1 is for setting the upper limit for DOP.

In the example above we specify values for parallel_target_percentage, parallel_queue_timeout parameters as well. The functionality associated with these parameters is available starting with Oracle Database 11g Release 2 (11.2.0.2).
For more information about these parameters see

• Oracle Database PL/SQL Packages and Types Reference (for 11.2 click here)
• Document 1359043.1 Configuring Parallel Statement Queuing to Manage Mixed Priority Workloads in a Data Warehouse

Starting in 18.1, there is an additional plan directive of PQ_TIMEOUT_ACTION (values RUN or CANCEL), which is the action to take when PARALLEL_QUEUE_TIMEOUT has been reached.  The default is CANCEL.  See Oracle® Database PL/SQL Packages and Types Reference/DBMS_RESOURCE_MANAGER/Table 134-10 CREATE_PLAN_DIRECTIVE Procedure Parameters for a list of all plan directives.

In case you want to clean up the above, do the following:

alter system set resource_manager_plan = '';
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.delete_plan('MY_PLAN');
exec dbms_resource_manager.submit_pending_area();

Note

• The max DOP limit is honored regardless of the value of parallel_degree_policy.

• The DOP is for slave set, so for an SQL that uses the producer/consumer model with 2 slave sets, there could be 4 (dop) x 2 (slave sets) = 8 parallel slave processes.

• In case the Max Number of Active Sessions is specified to be other than UNLIMITED e.g. 8, then this means its possible for the "OTHER_USERS" to have a maximum resource consumption of 8 (sessions) x 4 (DOP) x 2 (slave sets) = 64 parallel server processes.
   
• The value of the Max DOP directive of the resource plan that is active on the parsing instance is the final cap on the DOP.

• px_trace file shows if/when the Resource Manager limited the DOP:
  e.g.
  2012-11-20 21:18:24.208113*:PX_Messaging:kxfp.c@17785:kxfpclinfo():     inst(load:user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff
  2012-11-20 21:18:24.208113*:PX_Messaging:kxfp.c@17791:kxfpclinfo():     1   (5   :0   :100:40  :32    :0     :0      :0      :0     :0     )
          load adapt num servers requested to = 6 (from kxfpAdaptDOP())
          Ressource Manager reduced num server to maxdop = 4

The following use case demonstrates that the query with 1 slave group having 1 slave set is requested dop=8 by statement level parallel hint.
The query runs with DOP 8 having 8 parallel slaves.
Then DOP is limited by resource plan to 4 and resource manager caps the DOP to 4.
 

SH@dw23> conn sh
Connected.
SH@dw23> select /*+ PARALLEL(8) monitor*/ count(*) from sales;
    783327

SH@dw23> SET LONG 1000000
SH@dw23> SET LONGCHUNKSIZE 1000000
SH@dw23> SET LINESIZE 1000
SH@dw23> SET PAGESIZE 0
SH@dw23> SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(8) monitor*/ count(*) from sales

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SH (392:1421)
 SQL ID              :  0md7m3v2sh21s
 SQL Execution ID    :  16777216
 Execution Started   :  10/24/2013 14:46:05
 First Refresh Time  :  10/24/2013 14:46:05
 Last Refresh Time   :  10/24/2013 14:46:05
 Duration            :  .013903s
 Module/Action       :  SQL*Plus/-
 Service             :  dw23.uk.oracle.com
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|    0.05 |    0.02 |     0.00 |     0.02 |     1 |   1514 |  102 |  11MB |
===========================================================================

Parallel Execution Details (DOP=8 , Servers Allocated=8)
====================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |  Other   | Buffer | Read | Read  | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | (sample #)  |
====================================================================================================================
| PX Coordinator | QC    |         |    0.01 |    0.01 |          |     0.01 |     58 |      |     . |             |
| p000           | Set 1 |       1 |    0.00 |         |     0.00 |     0.00 |    200 |   13 |   1MB |             |
| p001           | Set 1 |       2 |    0.01 |    0.01 |     0.00 |          |    194 |   13 |   1MB |             |
| p002           | Set 1 |       3 |    0.00 |    0.00 |     0.00 |          |    179 |   13 |   1MB |             |
| p003           | Set 1 |       4 |    0.00 |         |     0.00 |     0.00 |    173 |   12 |   1MB |             |
| p004           | Set 1 |       5 |    0.00 |         |     0.00 |     0.00 |    186 |   13 |   1MB |             |
| p005           | Set 1 |       6 |    0.00 |         |     0.00 |     0.00 |    161 |   12 |   1MB |             |
| p006           | Set 1 |       7 |    0.00 |         |     0.00 |     0.00 |    189 |   13 |   1MB |             |
| p007           | Set 1 |       8 |    0.00 |         |     0.00 |     0.00 |    174 |   13 |   1MB |             |
====================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1763145153)
=================================================================================================================================================
| Id |        Operation         |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                          |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=================================================================================================================================================
|  0 | SELECT STATEMENT         |          |         |      |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  1 |   SORT AGGREGATE         |          |       1 |      |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  2 |    PX COORDINATOR        |          |         |      |         1 |     +0 |     9 |        8 |      |       |          |                 |
|  3 |     PX SEND QC (RANDOM)  | :TQ10000 |       1 |      |         1 |     +0 |     8 |        8 |      |       |          |                 |
|  4 |      SORT AGGREGATE      |          |       1 |      |         1 |     +0 |     8 |        8 |      |       |          |                 |
|  5 |       PX BLOCK ITERATOR  |          |    783K |   68 |         1 |     +0 |     8 |     783K |      |       |          |                 |
|  6 |        TABLE ACCESS FULL | SALES    |    783K |   68 |         1 |     +0 |   102 |     783K |  102 |  11MB |          |                 |
=================================================================================================================================================


SH@dw23> conn sys as sysdba
Connected.
SYS@dw23> -- create  MY_PLAN resource plan
SYS@dw23> -- setting maximum degree-of-parallelism for users in a consumer group other_groups
SYS@dw23> -- other_groups is a pre-defined default consumer group for all sessions that do not have an explicit initial consumer group
SYS@dw23> exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SYS@dw23> exec dbms_resource_manager.create_plan('MY_PLAN', 'MY_PLAN');

PL/SQL procedure successfully completed.

SYS@dw23> exec dbms_resource_manager.create_plan_directive('MY_PLAN', 'other_groups', 'other_groups',  parallel_degree_limit_p1 => 4);

PL/SQL procedure successfully completed.

SYS@dw23> exec dbms_resource_manager.submit_pending_area();

PL/SQL procedure successfully completed.

SYS@dw23> -- enable resource plan
SYS@dw23> alter system set resource_manager_plan = 'MY_PLAN' sid='*' ;

System altered.

SYS@dw23> conn sh
Connected.
SH@dw23> alter session set "_px_trace"="high","compilation","high","execution","messaging","time";

Session altered.

SH@dw23> ALTER SESSION SET EVENTS '10053 trace name context forever, level 10';

Session altered.

SH@dw23> select /*+ PARALLEL(8) monitor*/ count(*) from sales;
    783327

SH@dw23> SET LONG 1000000
SH@dw23> SET LONGCHUNKSIZE 1000000
SH@dw23> SET LINESIZE 1000
SH@dw23> SET PAGESIZE 0
SH@dw23> SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(8) monitor*/ count(*) from sales

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SH (392:1425)
 SQL ID              :  0md7m3v2sh21s
 SQL Execution ID    :  16777217
 Execution Started   :  10/24/2013 14:46:49
 First Refresh Time  :  10/24/2013 14:46:49
 Last Refresh Time   :  10/24/2013 14:46:49
 Duration            :  .145228s
 Module/Action       :  SQL*Plus/-
 Service             :  dw23.uk.oracle.com
 Program             :  sqlplus.exe
 DOP Downgrade       :  50%
 Fetch Calls         :  1

Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|    0.33 |    0.23 |     0.01 |     0.09 |     1 |   1478 |   63 |  11MB |
===========================================================================

Parallel Execution Details (DOP=4 , Servers Requested=8 , Servers Allocated=4)
====================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |  Other   | Buffer | Read | Read  | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | (sample #)  |
====================================================================================================================
| PX Coordinator | QC    |         |    0.15 |    0.09 |          |     0.05 |     58 |      |     . |             |
| p000           | Set 1 |       1 |    0.04 |    0.02 |     0.00 |     0.02 |    361 |   17 |   3MB |             |
| p001           | Set 1 |       2 |    0.05 |    0.04 |     0.00 |     0.01 |    377 |   15 |   3MB |             |
| p002           | Set 1 |       3 |    0.05 |    0.05 |     0.00 |     0.00 |    383 |   17 |   3MB |             |
| p003           | Set 1 |       4 |    0.04 |    0.03 |     0.00 |     0.01 |    299 |   14 |   2MB |             |
====================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1763145153)
=================================================================================================================================================
| Id |        Operation         |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                          |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=================================================================================================================================================
|  0 | SELECT STATEMENT         |          |         |      |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  1 |   SORT AGGREGATE         |          |       1 |      |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  2 |    PX COORDINATOR        |          |         |      |         1 |     +0 |     5 |        4 |      |       |          |                 |
|  3 |     PX SEND QC (RANDOM)  | :TQ10000 |       1 |      |         1 |     +0 |     4 |        4 |      |       |          |                 |
|  4 |      SORT AGGREGATE      |          |       1 |      |         1 |     +0 |     4 |        4 |      |       |          |                 |
|  5 |       PX BLOCK ITERATOR  |          |    783K |   68 |         1 |     +0 |     4 |     783K |      |       |          |                 |
|  6 |        TABLE ACCESS FULL | SALES    |    783K |   68 |         1 |     +0 |    48 |     783K |   63 |  11MB |          |                 |
=================================================================================================================================================

PX_TRACE shows that resouce manager reduced dop to 4:kxfrAllocSlaves                                                [      80/     0]DOP trace -- call kxfpgsg to get 8 slaves
2013-10-24 14:46:49.903900 :PX_Messaging:kxfp.c@9813:kxfpgsg(begin): reqthreads=8 height=0 lsize=0 alloc_flg=0x234
2013-10-24 14:46:49.904013 :PX_Messaging:kxfp.c@9886:kxfpgsg(): reqthreads=8 KXFPTHIS/KXFPLDBL/KXFPADPT/ load balancing=on adaptive=on
2013-10-24 14:46:49.904055 :PX_Messaging:kxfp.c@18418:kxfpiinfo(): inst [cpus:mxslv:#pg]1    [4   :80   :1  ](default: 0) inst target is 40number of active slaves on the instance: 0,number of active slaves but available to use: 0
2013-10-24 14:46:49.905752 :PX_Messaging:kxfp.c@18742:kxfpclinfo(): inst(load   :user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff1   (0.00:0   :100:400 :40    :0     :0      :0      :0     :0     ) defDOP=8, tpc=2
2013-10-24 14:46:49.905849 :PX_Messaging:kxfp.c@17831:kxfpLoadAdDOP():  
     granted dop: 8
    load adapt num servers requested to = 8 (from kxfpAdaptDOP())
    Ressource Manager reduced num server to maxdop = 4
2013-10-24 14:46:49.905922 :PX_Messaging:kxfp.c@2149:kxfpqialo(): Allocate new q for dp=(nil) new q=0x951a5628 dp=(nil) pnum=65535 numa#=0 qser=0 done.
2013-10-24 14:46:49.906030 :PX_Messaging:kxfp.c@10255:kxfpgsg(): getting 1 sets of 4 threads, client parallel query execution flg=0x234threads reduced from 8 to 4Height=4, Affinity List Size=0, inst_total=1, coord=1Insts:      1Threads:    4getting 4 slaves (8 required) for q=0x951a5628 qser=5633 

这篇关于并行 parallel DOP 受 Resource Manager 限制的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---