Version : 184.108.40.206 on RHEL 5.4
I have been asked to tune a query which has a DOP of 4
select /*+ parallel 4 */
I was under the impression that when you give DOP as 4 like above , 4 threads will be spawned to run the SELECT query.
But my colleague says , if you give DOP as 4 , actual threads spawned will be 4 times the number of cores
ie. If you have 2 CPUs with 4 cores on each , oracle will be spawning 4 times 8 = 32 threads to process this SQL query. Is this true ?
Yes, so lets suppose you are in a cluster environment of a 8 node cluster and with each node having 8 CPU cores , default DOP (degree of parallelism) will be 2 * 8 * 8. So cpu_count means CPU cores. Oracle sees each core as a cpu.
No, it is not.
You have asked for 4 PQ slaves. You get 4. However, if there is an ORDER BY clause, the ORDER BY might take another 4 as well.
If you run an INSERT .. SELECT .. ORDER BY with Parallel Hints or table level degree and you have a join on multiple tables, the number of PQ slaves can start with the highest DEGREE of the list in the SELECT .. FROM but would allocate PQ slaves for the INSERT, SELECT and ORDER BY, all three separately.
Hemant K Chitale
Hemant has given the correct conditions, but if it is a basic select statement then you will get 4 if enough PX server processes are available. Basic select statement here would mean lets say without joining tables suppose. Suppose for table A you do Alter table A parallel 10 ; and for table B you do Alter table B parallel 4 ; then when you join them you would have been processed with a DOP of 10 and allocationg 20 parallel servers. So the higher will be taken into account. They will only be available as long as you have enough PX server processes, considering into account PARALLEL_DEGREE_LIMIT, PARALLEL_ADAPTIVE_MULTI_USER and so on...