A Parallelization

The Oracle database provides numerous ways to configure parallelization and this section describes the exact settings that have been tested for the best performance. Parallel settings are closely related to proper statistics described Appendix B, "Statistics".

A.1 Degree of Parallelization

The installation scripts require a parameter named MAX_PARALLEL_DEGREE to be used for installing the database tier. This parameter relies directly on the number of CPUs that the database server has and must be used carefully to match the capabilities of the database server.

This parameter is used to set the degree of parallelization on the larger tables in CDM and ODB schemas. All other tables are created with the noparallel option. This allows the optimizer to make sure that parallel operations are used with SQL statements that access the larger tables. The value used should also be used in the initialization parameters of the database described below.

Specifying a value that is too large for the capabilities of the database server will cause many waits for parallel slaves. Alternately, specifying a value too small will leave the database with unutilized resources.

The recommendations specified here must be adapted to match the loads of other applications on the database as well as concurrent users. These specifications are described for systems that have an average number of sessions connected to the database that matches the capabilities of the database. If the expected sessions are more than average, smaller values for MAX_PARALLEL_DEGREE should be used.

You must configure the parallel parameters exactly as specified in this section. Any deviation from these instructions can result in unexpected performance.

Table A-1 CPU Sizes and Max Parallel Degree

Database Server Max Parallel Degree

Quad Core

2

Other Cores

<cpu count> / 4 (not to exceed 8)

Exadata

1/8 rack: 8, 1/4 or higher: 16

In addition to the MAX_PARALLEL_DEGREE, the installer requires initialization parameters for controlling parallel operations to be set as follows:

  • parallel_adaptive_multi_user=True

  • parallel_automatic_tuning=False

  • parallel_degree_limit=<MAX_PARALLEL_DEGREE>

  • parallel_degree_policy=LIMITED

  • parallel_execution_message_size=16384

  • parallel_force_local =TRUE

  • parallel_min_time_threshold=AUTO

Note:

  • The parallel_degree_limit should match the MAX_PARALLEL_DEGREE used to install the application.

  • Set the parallel_degree_policy to LIMITED. The LIMITED policy ensures that the tables used in each SQL statement determines the number of parallel slaves used to execute the SQL.

Oracle recommends that you configure the resource manager to make sure that the database never has run-away processes that may ignore the initialization parameters. The optimizer may have situations where it tries to use more parallel slaves than specified in the initialization parameters. The resource manager is a way to ensure that the SQL statements will never exceed certain thresholds.

The following SQL script is an example that will limit a server to use no more than 16 parallel slaves for Exadata and 8 for Non-Exadata systems. This can be modified to match the MAX_PARALLEL_DEGREE of any server.

Example for Exadata

exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.create_plan( plan => 'LIMIT_DOP', comment => 'Limit Degree of Parallelism');
exec dbms_resource_manager.create_plan_directive(plan=> 'LIMIT_DOP',  group_or_subplan =>'OTHER_GROUPS' ,  comment => 'limits the parallelism', parallel_degree_limit_p1=> 16)
exec dbms_resource_manager.validate_pending_area();
exec dbms_resource_manager.submit_pending_area();
alter system set resource_manager_plan = 'LIMIT_DOP' sid='*';

Example for Non-Exadata

exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.create_plan( plan => 'LIMIT_DOP', comment => 'Limit Degree of Parallelism');
exec dbms_resource_manager.create_plan_directive(plan=> 'LIMIT_DOP',  group_or_subplan =>'OTHER_GROUPS' ,  comment => 'limits the parallelism', parallel_degree_limit_p1=> 8)
exec dbms_resource_manager.validate_pending_area();
exec dbms_resource_manager.submit_pending_area();
alter system set resource_manager_plan = 'LIMIT_DOP' sid='*';

Another important use of parallel degree is for the OHO loaders. Most SQL loaders used to load files let the user pass a parameter for degree of parallelization. Most of these loaders will create intermediate tables and set the tables to match the passed parameter. This can be used to maximize how many server resources are used.

This value can be adjusted based on the number of other sessions that use the database and also how many concurrent loaders are running. This ensures that the database is used most effectively to load data. For example, performance tests have shown that a 1/4 rack exadata is only used at about 30% loading 8 concurrent sessions for gVCF files and using a degree parameter of 4. This can be used as a guideline to adjust this value for each specific database environment.