PARALLEL Hint in 11.2.0.3
Date: Thu, 31 Jul 2014 16:56:13 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB60213ADD9_at_HKJUMXMB103B.zone1.scb.net>
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 0SQL> 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 31 2014 - 10:56:13 CEST