After you determine the initial setting for the shared pool, you must calculate additional memory requirements for message buffers and determine how much additional space you need for cursors.
You must increase the value for the SHARED_POOL_SIZE parameter to accommodate message buffers. The message buffers allow query server processes to communicate with each other.
Oracle Database uses a fixed number of buffers for each virtual connection between producer query servers and consumer query servers. Connections increase as the square of the DOP increases. For this reason, the maximum amount of memory used by parallel execution is bound by the highest DOP allowed on your system. You can control this value by using either the PARALLEL_MAX_SERVERS parameter or by using policies and profiles.
To calculate the amount of memory required, use one of the following formulas:
For SMP systems:
mem in bytes = (3 x size x users x groups x connections)
For Oracle Real Application Clusters and MPP systems:
mem in bytes = ((3 x local) + (2 x remote)) x (size x users x groups) / instances
Each instance uses the memory computed by the formula.
The terms are:
SIZE = PARALLEL_EXECUTION_MESSAGE_SIZE
USERS = the number of concurrent parallel execution users that you expect to have running with the optimal DOP
GROUPS = the number of query server process groups used for each query
A simple SQL statement requires only one group. However, if your queries involve subqueries which are processed in parallel, then Oracle Database uses an additional group of query server processes.
CONNECTIONS = (DOP2 + 2 x DOP)
If your system is a cluster or MPP, then you should account for the number of instances because this increases the DOP. In other words, using a DOP of 4 on a two-instance cluster results in a DOP of 8. A value of PARALLEL_MAX_SERVERS times the number of instances divided by four is a conservative estimate to use as a starting point.
LOCAL = CONNECTIONS/INSTANCES
REMOTE = CONNECTIONS - LOCAL
Add this amount to your original setting for the shared pool. However, before setting a value for either of these memory structures, you must also consider additional memory for cursors, as explained in the following section.
Parallel execution plans consume more space in the SQL area than serial execution plans. You should regularly monitor shared pool resource use to ensure that the memory used by both messages and cursors can accommodate your system's processing requirements.