PARALLEL Hint in 11.2.0.3
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 Jul 2014 16:56:13 +0800
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:
