No.of partitions of a table in this case

[himridul]

Hi,

Is there any relationship between
No. of CPU , DOP(Degree of Parallelism) and No.of partitions of a table ?

The problem is as follows :

I've a table , containing 30 GB of data. We have

no. of CPU = 20

parallel_threads_per_CPU = 2 (NOT SURE)

parallel_max_servers = 40

A.Now What should be the DOP here ?

Is it 20*2 = 40 ?

B.Which one is preferable from the following options to decide the no. of partition
of the table , to acheive best performance :

1. No. of partition = The power of 2's (>= 40)

2. No. of partition >= multiple of 40's

 

[sbix]

himridul,
theoretically speaking, # of CPUs hasn't too much to do on how you split in partition your table.
A typical partitioning is i.e. by year, by department ...etc.

 

[himridul]

That's true sbix . But I think DOP is related to these two things parallel_threads_per_CPU and parallel_max_servers .

Anyway I don't have solid idea regarding this , I went through some manuals . There I found regarding parallel servers .

 

[sbix]

By other hand ... i.e. you're running a big query on a DWH Db, where all the rows of a big table have to be scanned ...
You haven't any key in forcing a CPU to execute the scan on a specific partition .. but ... if you got 4 CPU's you colud split this table in 4 by a key field which yields to 4 almost equivalent partitions .. in this case Oracle should execute 4 queries (if you have settled up parallel degree to 4) ... but I am not really sure about