That session query DOP is really just for query related operations. There is no benefits of using it
for model creation logic. In this case, it probably messed up some internal execution plans.
If you move the setSessionQueryDOP call after the model creation and before query execution,
do you see a performance gain? Also, I assume you have a balanced hardware setup with
8+ CPU cores and plenty of I/O bandwidth.
Effectivly, if I move the setSessionQueryDOP call after the model creation and before query execution, I see a performance gain. I also notice similar improvement when I use PREFIX ORACLE_SEM_FS_NS: http://oracle.com/semtech#DOP=8 , instead of setSessionQueryDOP.
We have a hardware setup with 64 CPU, 2TB of RAM and 5TB flash storage