Re: PARALLEL Hint in 11.2.0.3

What are the I/O calibration statistics?  Since you have Auto DOP (triggered by the hint) I suspect  i/o calibration has been run.  Please check dba_rsrc_io_calibrate and see what max_iops, max_mbps and latency values were calculated.


 
David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"



On Thursday, July 31, 2014 7:56 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
 


 


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@freelists.org] on behalf of Chitale, Hemant K [Hemant-K.Chitale@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 */ INTOTABLE_A SELECT /*+ PARALLEL */   * FROM TABLE_B
where both TABLE_A and TABLE_B have a defined DEGREE of 32,  at various times I see theINSERT ... SELECT running with different RequestedDoPs  (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-l