Configuration Guide

10.4

Back Next

Limiting the Parallel Degree for Gathering Statistics in Oracle environments

The parallel degree for gathering statistics in Oracle environments should be limited to ensure Hub process performance is not negatively impacted.

You must be logged in as a user with database administrator privileges to set the parallel degree.

Perform the following steps to assign an appropriate parallel degree for gathering statistics:

Calculate the appropriate parallel degree by using the following formula:

APPROPRIATE PARALLEL DEGREE = CPU_COUNT * PARALLEL_THREADS_PER_CPU

CPU_COUNT

is the number of CPUs available for Oracle to use. The

PARALLEL_THREADS_PER_CPU

is usually

If the server has many CPUs, select a parallel degree value equal to or less than the number of CPU cores.

If other applications are running on the same server as the Hub, decide how many CPU resources can be allocated to the Hub and then use this number when setting the appropriate parallel degree.

Check the current parallel degree setting by running the following SQL*Plus command:

select DBMS_STATS.GET_PREFS( 'DEGREE' ) from dual;

If necessary, set the appropriate parallel degree by running one of the following SQL*Plus commands:

In Oracle 10g:

DBMS_STATS.SET_PARAM ('DEGREE',<appropriate parallel degree value>);

In Oracle 11g:

DBMS_STATS.SET_GLOBAL_PREFS ('DEGREE', <appropriate parallel degree value>);

Test performance using the new parallel degree value by running the following SQL command for a large table:

DBMS_STATS.GATHER_TABLE_STATS

Repeat steps 3 and 4, reducing the parallel degree each time, until wait events are eliminated and performance is acceptable.

Rename Saved Search

Table of Contents

Configuration Guide

Configuration Guide

Limiting the Parallel Degree for Gathering Statistics in Oracle environments

Limiting the Parallel Degree for Gathering Statistics in Oracle environments