RE: PARALLEL Hint in 11.2.0.3
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 Jul 2014 13:55:32 +0000
I haven't investigated, but I wouldn't be surprised if "recent load history"
came into play. If so it's possible that with a suitable window for averaging
you could find that running the same query twice in succession results in
Oracle using the averaged effect of the load from the first execution as a
reason for limiting the degree of the second query. You might try the
experiment, varying the time between consecutive executions to see if longer
delays result in higher degrees.
I also note that in your example you have parallel_servers_target = 32, so it's
not a complete surprise that Oracle should avoid running parallel 32 when two
sets of slaves are needed.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Chitale, Hemant K [Hemant-K.Chitale@xxxxxx]
Sent: 31 July 2014 09:56
To: ORACLE-L
Subject: PARALLEL Hint in 11.2.0.3
Anyone know how Oracle auto-computes the DoP for the PARALLEL Hint in 11.2.0.3
For a statement like
INSERT /*+ PARALLEL */ INTO TABLE_A SELECT /*+ PARALLEL */ * FROM TABLE_B
where both TABLE_A and TABLE_B have a defined DEGREE of 32, at various times I
see the INSERT ... SELECT running with different Requested DoPs (2, 7, 22, 24
!) even as no other session is attempting to use PQ slaves.
SQL>select table_name, degree from user_tables
2 where table_name in ('TABLE_S15','TABLE_T15');
TABLE_NAME DEGREE
------------------------------ ----------------------------------------
TABLE_S15 32
TABLE_T15 32
2 rows selected.
SQL>explain plan for
2
SQL>
SQL>alter session enable parallel dml;
Session altered.
SQL>explain plan for
2 insert /*+ PARALLEL */ into TABLE_S15
3 select * from TABLE_T15;
Explained.
SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4291751268
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1121K| 1140M| 1809
(1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | |
| | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1121K| 1140M| 1809
(1)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | TABLE_S15 | | | |
| Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1121K| 1140M| 1809
(1)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1121K| 1140M| 1809
(1)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | LOAD AS SELECT | TABLE_S15 | | | |
| Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1121K| 1140M| 1809
(1)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| TABLE_T15 | 1121K| 1140M| 1809 (1)|
00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 22
19 rows selected.
SQL>
SQL>show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ----------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean TRUE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 64
parallel_min_percent integer 0
parallel_min_servers integer 10
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 3
parallel_servers_target integer 32
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL>
Unfortunately, I have no access to SYS.AUX_STATS$
Hemant K Chitale
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html.
Other related posts:
