Can hardcoding parallelism in Oracle hints lead to sub-optimal performance
Yes, yes it can. If the number of running parallel threads exceeds the number of CPU cores on the host machine, each of these threads will compete for CPU resources. This can be extremely inefficient and harm performance.