parallel query option

[manjunathk]

Hi friends,
How do I go about in having the parallel query option for our database?

We have parallel server installed(2 instances) and our main tables are partitioned on range and subpartitioned on hash.

Tables have been created with clause 'Parallel' but without any degree specified. As wwe have 4 cpus, will it take 4 as degree?

Thanks
manjunath

[gpsingh]

Degree: Depends on the number of CPUS or number ofdisks upon which the table or index lies , whichever is less.

[pando]

To use OPS your tables must be partitioned...? Or it´s recommended only

[wjramsey]

There are a number of things that must be in your init file in order to use the PQO such as parallel_max_servers. I would recommend reading, I believe, Chapter 20? in the Oracle performance tuning manual which goes over the basics of how to set up and tune the parallel query option. There is also a decent book by OReilly that covers OPS and the Parallel Query option.

The amount of PQ Slaves you allow is usually a multiple of the number of CPU's (for example I have parallel_max_servers set at 80 on an 8CPU system). There are other factors that affect what you should set the value to including the number of concurrent users, how much memory you have, how many disks are involved, etc... I don't have the book here but the formula is something like ( #CPU's * # expected concurrent users * 2). To tell you the honest truth, I've searched dillegently to some good information regarding how best to set these values but it seems like you just have to set them low, and just keep incrementing the value until you start getting declining performance returns for the amount of resources used. There are some statictics that oracle maintains in the library that are of help. Let me know if you need more assistance.

Joe