Home Page

Back to Papers


Introduction

Architecture

Instance Configuration

Instance Configuration II

Dictionary Views

Dictionary Views II

Monitoring Queries

Session Tracing and Wait Events

Conclusion

Other Resources

Instance Configuration (Continued)

parallel_max_servers and parallel_min_servers

Default Value - Derived from the values of CPU_COUNT, PARALLEL_AUTOMATIC_TUNING and PARALLEL_ADAPTIVE_MULTI_USER

Recommended Value - Dependant on number of CPUs. Maybe CPUs * 2

As Oracle uses PX for user requests, it needs to allocate PX slaves and it does this from a pool of slaves. These two parameters allow you to control the size of the pool and are very straightforward in use. The most difficult thing is to decide on the maximum number of slaves that you think is sensible for your server. Iíve seen people running dozens or hundreds of slaves on a four CPU server. Clearly that means that each CPU could be trying to cope with 10-20 or more processes and this probably isnít a good idea. Donít forget, though, that this is precisely what your home PC is doing all of the time! However if your disk subsystem is extremely slow, it may be that a number of slaves per CPU is beneficial because most of your processes are spending most of their time waiting on disk i/o rather than actually doing anything! However, that needs to be balanced against the extra work that the operating system is going to have to do managing the run queue.

A sensible range of values is perhaps 2-to-4 * the number of CPUs. The most important thing is to perform some initial stress testing and to monitor CPU and disk usage and the serverís run queue carefully!

parallel_execution_message_size

Default Value - 2Kb

Automatic Tuning Default - 4Kb

Recommended Value - 4-8Kb

This parameter controls the size of the buffers used to pass messages between the various slaves and the query coordinator. If a message is larger than this size, then it will be passed in multiple pieces, which may have a slight impact on performance. Tellingly, parallel_automatic_tuning increases the size from the default of 2Kb to 4Kb so this is probably a useful starting point, but it may be worth increasing to 8Kb or even larger. Bear in mind, though, that increasing this value will also increase the amount of memory in the Large or Shared Pool, so you should check the sizing calculations in the documentation and increase the relevant parameter appropriately

Other significant parameters

In addition to the parallel_ parameters, you should also think about the effect that all of the additional PX slaves will have on your server. For example, each is going to require a process and a session and each is going to be using a sub-task SQL statement that will need to exist in the Shared SQL area. Then we need to think about all of the additional work areas used for sorting for example. The documentation is very good in this area, though, so Iíll refer you to that.

Previous

 Next

Technical Papers Utilities and Scripts Book Reviews Links
My Resume Fun & Games Email Home