RE: PARALLEL Hint in 11.2.0.3
Date: Thu, 31 Jul 2014 13:55:32 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E0532D_at_exmbx05.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chitale, Hemant K [Hemant-K.Chitale_at_sc.com] 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 31 2014 - 15:55:32 CEST