Oracle Database - Shared Pool

Portion of the SGA that contains shared memory constructs such as shared SQL areas.

Parallel servers communicate among themselves and with the Query Coordinator by passing messages. The messages are passed via memory buffers that are allocated from the shared pool. When a parallel server is started it will allocate buffers in the shared pool so it can communicate, if there is not enough free space in the shared pool to allocate the buffers the parallel server will fail to start.

In order to size your shared pool appropriate you should use the following formulas to calculate the additional overhead parallel servers will put on the shared pool. If you are doing inter-node parallel operations

(((2 + (cpu_count X parallels_threads_per_cpu)) X 2) X (cpu_count X parallels_threads_per_cpu)) X
parallels_execution_message_size X # concurrent queries

or when you use cross instance parallel operation in a RAC environment.

(((2+ (cpu_count X 2)) X 4) X cpu_count X 2)) X parallel_execution_message_size X # concurrent queries

Note the results are returned in bytes.

Only the memory needed for the parallel_min_servers will be preallocated from the shard_pool at database startup. As additional parallel servers are needed, their memory buffers will be allocated “on the fly” from the shared pool. These rules apply irrespective of whether you use shared_pool_size directly, or sga_target (10g and higher) or memory_target (starting with 11g)

• Oracle SQL Parallel Execution - An Oracle White Paper - June 2008
• Memory Architecture - Shared Pool