0

I have one insert into select query, like : 

INSERT /*+ append */ INTO  
select col1,col2...........  
from table1,table2(multiple table join)

When I compare plan between prod and UAT, I see UAT is running in 4 DOP but prod is not running in parallel.

Query is exactly same, and checked parallelism at table level, but degree=1 in prod and UAT.

Not sure what is the reason running in parallel?

I assume when we use /*+ append */ internally it will opt for parallel, but why it is ignored in prod?

DB : oracle11g

APC
  • 144,005
  • 19
  • 170
  • 281
Laks
  • 95
  • 11
  • 1
    The hint for parallel execution is [`/*+ parallel */`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#SQLRF-GUID-D25225CE-2DCE-4D9F-8E82-401839690A6E). – William Robertson Mar 29 '20 at 13:34
  • the database can 'downgrade' parallelism for queries as well based on available server resources – thatjeffsmith Mar 29 '20 at 14:16
  • 1
    which 'table' you checked for parallel? the one you insert into or all those in the join? And what is running in parallel: the select alone or the whole thing ? Last /*+ APPEND */ does not entail parallel execution. – gsalem Mar 29 '20 at 14:46
  • 2
    @Laks Look at my [answer here](https://stackoverflow.com/a/21132027/409172) for a list of 39 different factors that can influence the degree of parallelism. – Jon Heller Mar 29 '20 at 17:01
  • 1
    I had this problem yesterday. It turned out that the parameter optimizer_dynamic_sampling was set to 2 on one instance (where it ran in parallel) and 4 on the other (where it did not run in parallel). So I recommend also to compare your init parameters across the DB's to see if there are any optimizer related parameter mismatches. It is also often the case that stats are different in the two DB's which will cause different execution plans. – Roger Cornejo Apr 02 '20 at 15:00

1 Answers1

0

Just execute 

select * from table(dbms_xplan.display_cursor('<sql_id>', childnumber, format => '+NOTE ADVANCED ADAPTIVE');

And Oracle will tell you, at very bottom there is Note section:

Note
-----
   - Degree of Parallelism is 96 because of table property

There can be various reasons like TRIGGERS or Oracle Text index present.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • error while trying to run above : Error: format '+NOTE ADVANCED ADAPTIVE' not valid for DBMS_XPAN.DISPLAY_CURSOR( – Laks Mar 30 '20 at 08:57
  • maybe try to remove the ADAPTIVE part. The format depends on Oracle version. But at least since ver. 12.1 the NOTE section should tell you details about parallel processing – ibre5041 Mar 30 '20 at 10:16