Auto DOP and Queuing Parameter Settings
By Jean-Pierre Dijcks-Oracle on Feb 14, 2011
In anticipation of some of the questions that will come out of this week's Rocky Mountain Oracle User Group Training Days, I decided to publish some of the parameter settings/best practices.
Just so I do not have to describe too much here, I've picked some internal homework we started to do. The reference configuration is an Exadata Database Machine X2-2. The data sheet for the machine is here.
The assumptions for the configuration settings below are:
- Conservative settings to get you going - you will change some of these as you see your tests and workloads run with the parameters
- Better safe than sorry, so nothing very daring
- Not aimed at delivering the latest drop of performance, instead aimed at reasonable DOPs and reasonable concurrency
- Aiming at staying well within the realms of capabilities in terms of parallel processes
- Data Warehouse only
The settings in the table below are per INSTANCE. For a full rack the aggregated cluster wide number is multiplied by instance count (8 in this case). In the table below you see a number of parameters. The gray ones are not directly relevant to Auto DOP and queuing controls, but are worthwhile keeping an eye on. Note that we leave parallel_max_servers default (on X2-2 it would come to 240).
For the full machine, just as an example, parallel_servers_target = 8 * 128 = 1024, or 1024 parallel processes in use for the entire cluster before we start queuing parallel statements.
Parallel_degree_limit is per query, so it is not aggregated across the cluster. The * is to indicate the changes that come into play when you use Database Resource Manager. If you do so, the system wide setting in this table should be raised as the settings in DBRM are now leading. In this case you should view (and thus raise) parallel_degree_limit as your safety net for queries that do not go into a resource plan or group.
For a full rack (again on a per instance level) setting parallel_degree_limit to 64 sounds very reasonable.
Explaining These Settings
Stay tuned for more... recordings describing the above data are coming soon!