Oracle Database - Shared Pool

> Database > Oracle Database

1 - About

The Shared pool is a shared memory area where cursors and PL/SQL objects are stored.

The shared pool is a shared memory that store:

  • Cached data dictionary information and
  • Shared Cursors and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

Portion of the SGA that contains shared memory constructs such as :

Advertising

3 - shared_pool_size in parallel execution

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)

4 - Management

4.1 - Flush

ALTER SYSTEM FLUSH SHARED_POOL;

4.2 - get advice

The view V$SHARED_POOL_ADVICE gives advices. It oredicts the impact of different values of shared_pool_size on elapsed parse time saved.

This view is gathered when the statistics_level parameter is set on TYPICAL.

Advertising

4.3 - package

You can manage it with the DBMS_SHARED_POOL package.

To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.

@?/rdbms/admin/dbmspool.sql

The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs. These scripts are not run by as part of standard database creation.

5 - Documentation / Reference

db/oracle/shared_pool.txt · Last modified: 2017/09/06 19:30 by gerardnico