并行 parallel DOP 受 Resource Manager 限制
监控
| Script | |
|---|---|
|
|
This article shows how you can limit the degree ot parallelism.
SOLUTION
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
In a database there is only 1 resource group.
You can see it when you query V$RSRC_CONSUMER_GROUP
SQL> select name from V$RSRC_CONSUMER_GROUP;
NAME
--------------------------------
<Group name>
We can limit the Degree of Parallelism for this group with the command
exec dbms_resource_manager.create_plan_directive(plan=> 'LIMIT_DOP', -
group_or_subplan =>'<Group name>' , -
comment => 'limits the parallelism', parallel_degree_limit_p1=> 4);
The value of parallel_degree_limit_p1 contains the maximum number of Parallelism.
The complete script is
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.create_plan( plan => 'LIMIT_DOP', comment => 'Limit Degree of Parallelism');
exec dbms_resource_manager.create_plan_directive(plan=> 'LIMIT_DOP', -
group_or_subplan =>'<Group name>' , -
comment => 'limits the parallelism', parallel_degree_limit_p1=> 4);
exec dbms_resource_manager.validate_pending_area();
exec dbms_resource_manager.submit_pending_area();
You activate the script with
alter system set resource_manager_plan = 'LIMIT_DOP' sid='*';
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