|
Home
Page
Back to Papers |
Please note this page has moved to http://oracledoug.com/px4.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-) 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.
|