CODEWITHSUNDEEP
sqloracle-databaseperformanceparallel-query
Laks
Laks

Reputation: 85

Query running in parallel in one environment not in other

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

Upvotes: 0

Views: 630

Answers (1)

ibre5041
ibre5041

Reputation: 5288

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.

Upvotes: 0

Related Questions