Auto DOP and Queuing Parameter Settings

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.

Reference Configuration

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.

Assumptions

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

Settings

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).

settings_x22_table

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!

Comments:

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today